Understanding MySQL VARCHAR data type

The MySQL 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 VARCHAR columns:

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 25 characters -display_name column of VARCHAR type with a max length of 50 characters

The total of both columns size is 75 characters.

MySQL stores 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 1.

When your column is more than 255 bytes, then the length prefix will be 2.

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:

• ASCII type - 1 byte per character
• latin1 type - 1 byte per character
• utf8 type - 3 bytes per character
• utf8mb4 type - 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.

The 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 16,383 bytes.

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.

Creating one 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.

An 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 username and 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! 👍