What is the maximum length of the varchar type in MySQL?

In MySQL, what is the maximum length of the varchar type? We will find that the varchar length is 65535 for the varchar data type, that is, 65532 bytes of data can be stored, is that true? Let's take a look.

1. Restrictions

Field restrictions include the following rules when a field is defined:

 

A) Storage restrictions

The varchar field stores the actual content separately in the clustered index. The content starts with 1 to 2 bytes to indicate the actual length (2 bytes if the length exceeds 255 ), therefore, the maximum length cannot exceed 65535.

B) encoding length limit

If the character type is gbk, each character occupies a maximum of 2 bytes, and the maximum length cannot exceed 32766;

If the character type is utf8, each character occupies up to 3 bytes, and the maximum length cannot exceed 21845.

If the preceding limits are exceeded during definition, the varchar field is forcibly converted to the text type and generates a warning.

 

C) row length limit

In practice, the length of a varchar is limited by the length defined by a row. MySQL requires that the definition length of a row cannot exceed 65535. If the defined table length exceeds this value, a prompt is displayed.

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs.

 

2. computing example

Here are two examples to illustrate the calculation of the actual length.

 

A) if a table has only one varchar type, for example

Create table t4 (c varchar (N) charset = gbk;

The maximum value of N is (65535-1-2)/2 = 32766.

The reason for the decrease of 1 is that the actual Row Storage starts from the second byte ';

The reason for the decrease of 2 is that the two bytes in the varchar header indicate the length;

The reason for Division 2 is that the character encoding is gbk.

 

B) if a table is defined

Create table t4 (c int, c2 char (30), c3 varchar (N) charset = utf8;

The maximum value of N here is (65535-1-2-4-30*3)/3 = 21812

Subtraction 1 and subtraction 2 are the same as those in the previous example;

The reason for 4 reduction is that int Type c occupies 4 bytes;

The reason for the decrease of 30*3 is that char (30) occupies 90 bytes and the encoding is utf8.

 

If varchar exceeds the preceding B rule and is forced to be of the text type, each field occupies 11 bytes. Of course, this is no longer a "varchar ".

I read some documents online and did some experiments locally. the maximum length of the original vachar is really variable (determined by whether there are non-empty fields)
I conducted a local experiment in the innodb + latin environment.

-- Success
Drop table if exists test;
Create table test (name varchar (65533) not null) engine = innodb default charset = latin1
-- Too large
Drop table if exists test;

Create table test (name varchar (65533) engine = innodb default charset = latin1
In the second case, when a blank field is allowed, it cannot be added to the length of 65533. The maximum value is 65532. It should be the quote.

Some people have done similar experiments on the internet, refer to http://stackoverflow.com/questions/8295131/best-practise-for-sql-varchar-column-length

Name varchar (100) not null will be 1 byte (length) + up to 100 chars (latin1)
Name varchar (500) not null will be 2 bytes (length) + up to 500 chars (latin1)
Name varchar (65533) not null will be 2 bytes (length) + up to 65533 chars (latin1)
Name varchar (65532) will be 2 bytes (length) + up to 65532 chars (latin1) + 1 null byte

To sum up, although the original mysql vachar field type has a maximum length of 65535, it does not store so much data and can reach a maximum of 65533 (when non-empty fields are not allowed ), when a non-empty field is allowed, it can only be 65532.

2. Differences between CHAR (M) and VARCHAR (M)
The length of the column defined by CHAR (M) is fixed, and the M value can be 0 ~ 255. When the CHAR values are saved, fill in spaces on the right of them to reach the specified length. When the CHAR value is retrieved, the trailing space is deleted. Case-insensitive conversion is not performed during storage or retrieval. It is very convenient for CHAR to store fixed-length data, and the indexing efficiency of CHAR fields is high. For example, if char (10) is defined, no matter whether the data you store reaches 10 bytes, it takes up 10 bytes of space. If not, it is automatically filled with spaces.

The length of the column defined by VARCHAR (M) is a variable-length string, and the value of M can be 0 ~ 65535 (the maximum valid length of VARCHAR is determined by the maximum size of the row and the character set used. The total length is 65,532 bytes ). When the VARCHAR value is saved, only the number of characters is saved, and a byte is added to record length (if the length of the column declaration exceeds 255, two bytes are used ). The VARCHAR value is not filled when it is saved. When the value is saved and retrieved, the space at the end is retained, which complies with the standard SQL. Varchar stores variable-length data, but the storage efficiency is not as high as CHAR. If the possible value of a field is not fixed, we only know that it cannot exceed 10 characters. It is the most cost-effective to define it as VARCHAR (10. The actual length of the VARCHAR type is the actual length of its value plus 1. Why "+ 1? This byte is used to save the actual length. From the perspective of space, it is appropriate to use varchar; from the perspective of efficiency, char is suitable, and the key is to find a trade-off point based on the actual situation.

The biggest difference between CHAR and VARCHAR is the fixed length and variable length. Because it is a variable length, the actual storage is the actual string plus a record String Length byte (if it exceeds 255, it requires two bytes ). If the value assigned to a CHAR or VARCHAR column exceeds the maximum length of the column, crop the value to make it suitable. If the character to be dropped is not a space, a warning is generated. If you crop non-space characters, it will cause an error (rather than warning) and disable value insertion by using strict SQL mode.

3. Differences between VARCHAR, TEXT, and BlOB types
The VARCHAR, BLOB, and TEXT types are variable-length. The storage requirement depends on the actual length of the column value (represented by L in the previous table), rather than the maximum possible size of the type. For example, a VARCHAR (10) column can store a string with a maximum length of 10 characters. The actual storage needs to be the length of the string, plus one byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is 5 bytes.

BLOB and TEXT types require 1, 2, 3, or 4 bytes to record the length of column values, depending on the maximum possible length of the type. VARCHAR must be defined with a maximum size of 65535 bytes. TEXT is not required. If you assign a value that exceeds the maximum length of the column type to a BLOB or TEXT column, the value is truncated to suit it.

A blob is a large binary object that can save a variable amount of data. Four BLOB types, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB, differ only in the maximum length of values they can save.

BLOB can store images, but TEXT cannot. TEXT can only store plain TEXT files. Four TEXT types, TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT, correspond to four BLOB types, and have the same maximum length and storage requirements. The only difference between the BLOB and TEXT types is that the sorting and comparison of BLOB values are executed in a case-sensitive manner, while the TEXT values are case-insensitive. In other words, a TEXT is a case-insensitive BLOB.

4. Summary differences between char, varchar, and text
Difference in length, char range is 0 ~ 255, varchar can be up to 64 k, but note that 64 k here is the length of the entire row. Consider other columns, and if not null exists, it will also occupy one space, for different character sets, the effective length is also different, such as utf8, a maximum of 21845, but also to remove other columns, but varchar is generally enough to store. If you encounter large text, consider using text, up to 4 GB.

Efficiency is basically char> varchar> text. However, if Innodb engine is used, we recommend that you use varchar instead of char.

Char and varchar can have default values, and text cannot specify default values

It is necessary to select an appropriate data type for the database, which has a certain impact on the performance. Here there are two pieces of records. For int type, if you do not need to access negative values, you 'd better add unsigned. For fields that often appear in where statements, consider adding indexes, shaping is especially suitable for indexing.