The Complete Guide To MySQL VARCHAR Datatype

In this tutorial, we will learn about the VARCHAR datatype in MySQL. We will see what is a VARCHAR datatype, its minimum and maximum limit and what characters are valid as a VARCHAR datatype. So, let’s get started!

Also read: MySQL INT Datatype

Introduction to MySQL VARCHAR

The first and most important point before getting started is that CHAR and VARCHAR are two different data types. Therefore, do not get confused between these two datatypes. We will see the detailed information about the VARCHAR and at the end, we will compare it with the CHAR datatype.

The VARCHAR type stores a variable-length string having a length of 0 to 65535 bytes.

Therefore, the maximum row size is 65,535 bytes, which determines the effective maximum length of a VARCHAR column.

Note that, MySQL stores the varchar data as a one-byte or two-byte length prefix plus the data.

Here, the length prefix is the number of bytes in the value. If the value that is going to be stored in the varchar column is less than 255 bytes then the length prefix will use one byte, otherwise two bytes.

Remember that, the length prefix is only present for the varchar and not the char data type because the varchar stores a variable-length string.

Varchar Storage Illustration

The following table shows how much storage is required for the given strings.

As you can see here, the length of the value is less than 255 bytes, therefore, the extra one byte will be taken as a length prefix. Hence, the final storage required will be the length of a value plus that one byte.

Also, the last value in the table is trimmed because the column size we have set is 4. Therefore, MySQL will trim the value to the given length and store it. Note that, this is only when the strict SQL mode is not enabled.

If the strict SQL mode is enabled, the value will not be stored which exceeds the column length and the error will be shown.

MySQL VARCHAR Example

Here, we will create a table which is having two varchar columns. Note that the maximum row size is 65,535. Therefore, the combined size of all columns must be less than or equal to 65,535.

CREATE TABLE varcharTut(
col1 VARCHAR(32765) NOT NULL,
col2 VARCHAR(32766) NOT NULL
)CHARACTER SET 'latin1' COLLATE LATIN1_DANISH_CI;Code language: SQL (Structured Query Language) (sql)

As you can see, 32765+32766 makes 65531 bytes. But these two columns also take 2 bytes each as a length prefix. So the total becomes 32765+32666+2+2=65535.

If you try to increase the size of a column by a single byte, you will get an error.

Let’s try.

CREATE TABLE varcharTut(
col1 VARCHAR(32766) NOT NULL,
col2 VARCHAR(32766) NOT NULL
)CHARACTER SET 'latin1' COLLATE LATIN1_DANISH_CI;Code language: SQL (Structured Query Language) (sql)

As you can see, we get an error because the row size exceeds the maximum limit.

Now, let’s try to insert a value that exceeds the column length.

Here, we will use the following table which has the column of type varchar and size 6.

INSERT INTO emps(name,age)
VALUES("Iron Man",21);Code language: SQL (Structured Query Language) (sql)

Here, the value we are inserting has a length of 8 bytes. Therefore, MySQL will abort the insertion and throw an error because the strict SQL mode is enabled.

As you can see, we get an error that the data is too long for the given column.

If you want to disable the strict mode, you can use the following command. However, it is best to leave the strict mode enabled.

$ mysql -u root -p -e "SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';" Code language: SQL (Structured Query Language) (sql)

Difference Between CHAR and VARCHAR

The first and basic difference between the char and varchar is the limit. You can store up to 255 bytes in the char datatype column whereas, for varchar, the limit is up to 65535.

The main difference is, that the char is fixed in length. Whereas, the varchar is variable length.

This indicates that there are diverse storage needs.

A CHAR always requires the same amount of storage space, regardless of what you save, but a VARCHAR has different storage needs based on the individual string being stored.

For example, if the column size of a char datatype is 200 and the string you are storing has a length of 100 characters, then it will take 200 bytes anyhow as the remaining size will be filled with 100 spaces. In the case of varchar, it will take 100 bytes for the string and 1 byte for the length prefix.

Summary

In this tutorial, we have learned about the varchar data types in brief. I hope you have learned about it as well as the difference between the varchar and the char in an easy way.

References

MySQL official documentation on char and varchar types.