MySQL VARCHAR

Varchar in MySQL is a data type used for storing text whose length can have a maximum of 65535 characters. The varchar columns in the table are of variable length string that can hold either numeric or character or both. This data type is capable of storing only 255 characters before version 5.0.3, but with this version and later, it can hold up to 65535 characters. It can be stored in MySQL as a 1-byte or 2-byte length prefix plus actual size.

The length prefix specifies the byte length of a string value, not the maximum size that we have set. If values do not require more than 255 bytes, a column uses length prefix plus one byte. If values require more than 255 bytes, a column uses length prefix plus two bytes.

The maximum length of a VARCHAR in MySQL is subject to the maximum row size of 65,535 bytes, which is shared among all columns except TEXT/BLOB columns and the character set used. It means the total column should not be more than 65535 bytes.

Let us understand it with the help of an example.

We are going to create two tables and named it Test1 and Test2. Both tables contain two columns named T1 and T2. Execute the following statement to create a table "Test1":

The above statement created a table successfully because the column length T1 = 32765 plus 2 bytes and T2 = 32766 plus 2 bytes equal to 65535 (32765+2+32766+2). So, the column length satisfies the maximum row size of varchar, which is 65535.

Now, we are going to see what happens if the column size exceeds the maximum size of varchar 65535. Execute the below statement where we have increases the size of column T1 to create a table Test2:

After executing the above statement, MySQL produces the error. It means the maximum row size cannot exceed 65,535 bytes. In any case, if it increases, the statement failed, and MySQL will generate an error.

In another case, suppose we have created a table named Test3 using the below statement:

Next, insert values into the table using the below statement:

Now, execute the above statement. MySQL gives the error message: Data is too long for column 'Name' at row 1. The output explains it more clearly.

MySQL VARCHAR and Spaces

MySQL does not padded space for varchar values when they are stored. MySQL also retained the trailing spaces when they stored or retrieved varchar values. We can understand it through the following example where an insert statement adds values in the Name column of table Test3:

Next, execute the SELECT statement to retrieve the values.

It will give the following output where MySQL included the trailing space in the counting of length because it does not increase the column length.

However, when we try to insert a varchar value with trailing spaces which exceeded the column length, the MySQL will truncate the trailing spaces. Also, MySQL issues a warning. The following example explains it more clearly:

The above statement inserts a value whose length is six into the name column. The value still inserted into the column, but MySQL truncates the trailing space before adding the value. We can verify using the below query where we can see the insert statement added successfully but with a warning that gives: Data truncated for column 'name' at row 1:

Difference between Char and Varchar Data Type

The char and varchar data types are both follow ASCII character. They are almost the same but differ in storing and retrieving the data from the database. The following table summarises the essential differences between char and varchar data type: