VARCHAR data type is used for storing alpha-numeric texts in your column for up to 65,535 characters.
When you create a table, you can specify the column type as
VARCHAR and specify the length of the characters the column can store.
The following is a valid
CREATE TABLE statement with
CREATE TABLE users ( username VARCHAR(25) NOT NULL, display_name VARCHAR(50) NOT NULL );
The above statement will create the
users table with two columns:
username column of
VARCHAR type with a max length of
display_name column of
VARCHAR type with a max length of
The total of both columns size is 75 characters.
VARCHAR data type in a dynamic memory allocation mode, which means that if you only store a data of
5 characters in length, then you will only use 5 bytes instead of the maximum length.
VARCHAR type storage calculation
VARCHAR data types are stored as a 1 byte or 2 byte length prefix plus the actual data size.
If your column is
255 bytes or less, then the length prefix will be
When your column is more than
255 bytes, then the length prefix will be
The actual data size itself will vary depending on the
CHARACTER SET you defined for the table.
Here’s the most common
CHARSET type size:
ASCIItype - 1 byte per character
latin1type - 1 byte per character
utf8type - 3 bytes per character
utf8mb4type - 4 bytes per character
If you’re using the latest MySQL database version 8 or above, then the default
CHARACTER SET should be
utf8mb4 which also supports emojis and special characters.
Let’s see an example of how to calculate the actual data size.
First, you should know that MySQL table has a limit size of 65,535 bytes that are distributed equally among all columns.
VARCHAR column(s) in one table must not exceed this limit or MySQL will throw an error.
When you’re creating a table with the
utf8mb4 character set, then the maximum length of one
VARCHAR column is
CREATE TABLE users ( username VARCHAR(16383) NOT NULL -- Already at limit );
This is because
16,383 * 4 + 2 equals
65,534 which is only 1 byte less than the MySQL limit.
VARCHAR with the size of
16,384 will cause MySQL to throw
Column length too big error.
When you create a table with two
VARCHAR columns, then you need to further reduce the size of each column:
CREATE TABLE users ( username VARCHAR(8191) NOT NULL, display_name VARCHAR(8191) NOT NULL );
Each column will have the size of
8191 * 4 + 2 in
utf8mb4 character set, so the total size is
32766 for each column.
32766 * 2 = 65532
Increasing the size of one of the columns to 8192 will cause
Row size too large error:
CREATE TABLE users ( username VARCHAR(8191) NOT NULL, display_name VARCHAR(8192) NOT NULL -- Row size too large );
If you’re also including an
INT type column, then you need to further reduce one of the column sizes to make space for it.
INT type column is 4 bytes in size, so reducing your
VARCHAR length by 1 would be enough:
CREATE TABLE users ( username VARCHAR(8191) NOT NULL, display_name VARCHAR(8190) NOT NULL, age INT NOT NULL );
And that’s how you count the maximum length of
VARCHAR columns in a table.
In real software projects,
VARCHAR data type is commonly used for storing short alpha-numeric text that you want to add as a table index for quick searching (like
display_name for websites like Twitter or Facebook)
When you need to store multiple paragraphs of text such as comments on a website, it’s better to use
TEXT types because they can store a large amount of text that is not calculated toward the row size limit.
You’ve just learned about MySQL
VARCHAR data type. Great job! 👍