Introduction to MySQL VARCHAR

In MySQL, the data type VARCHAR a variable-length string type, and it is different from the data type CHAR is a data type. If the type of a column is defined as VARCHAR type, the length of the content in the column is the length (number of bytes) of the content itself.

MySQL VARCHAR Syntax

You need to specify a maximum length for VARCHAR. Here is the syntax of VARCHAR:

VARCHAR(max_length)

The parameter max_length is a numeric value indicating the maximum number of bytes for this column. If we don’t specify this value, the default value is 255. That is to say, VARCHAR is equivalent to VARCHAR(255).

When MySQL stores a VARCHAR value , MySQL uses the first 1 or 2 bytes to a store the length of the actual string content. If the value of the column is less than 255 bytes, MySQL uses ​​1 byte, otherwise uses 2 bytes.

The maximum allowed length of VARCHAR is 65535 bytes, which is also the row size limit in MySQL.

In MySQL, except for TEXT and BLOB columns, the row size is limited to 65535 bytes. and the VARCHAR column’s maximum length is 65535 bytes.

In addition, how many characters can be stored in a VARCHAR column is related to the character set used by the database.

MySQL VARCHAR Examples

Let’s look at a simple example.

We will try to create a new table with the maximum length as follows:

CREATE TABLE test_varchar_latin1 (v VARCHAR(65535))
CHARACTER SET 'latin1';

Note here that we are using a character set latin1. At this point, MySQL will issue an error message:

Error Code: 1118. Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

Here, since there are 2 bytes used for the VARCHAR length prefix, we can only set it to max VARCHAR(65532).

Now let’s look at another example using utf8 encoding :

CREATE TABLE test_varchar_utf8 (v VARCHAR(65535))
CHARACTER SET 'utf8';

MySQL will issue an error message like the following:

Error Code: 1074. Column length too big for column 'v' (max = 21845); use BLOB or TEXT instead

This is because each utf8 character takes up to 3 bytes, so: 65535 / 3 = 21845.

VARCHAR truncate content

When the inserted content exceeds the length defined by the VARCHAR column , MySQL adopts the following strategy:

• If the excess contains only spaces, the extra spaces are truncated.
• If the excess part is not just a space, an error message is given.

First, let’s create a test table with only one field v defined as VARCHAR(2):

CREATE TABLE test_varchar (v VARCHAR(2))
CHARACTER SET 'latin1';

Let’s insert a value 'A ' that contains 2 spaces and has a total length of 3.

INSERT INTO test_varchar
VALUES ('A  ')

1 row(s) affected, 1 warning(s): 1265 Data truncated for column 'v' at row 1

The insertion is successful here, but MySQL gives a truncation prompt.

Let’s take one more value 'ABC', it’s length 3.

INSERT INTO test_varchar
VALUES ('ABC')

Error Code: 1406. Data too long for column 'v' at row 1

Here, inserting data fails.

Conclusion

In this article, we discussed MySQL data type VARCHAR and the length limitations of VARCHAR.