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

1. Restriction rules

The limits of a field have the following rules when the field is defined:

A) storage limits

The varchar field is to store the actual content separately from the clustered index, where the content begins with 1 to 2 bytes representing the actual length (2 bytes in length over 255), so the maximum length cannot exceed 65535.

b Coding Length Limit

If the character type is GBK, each character is up to 2 bytes and the maximum length cannot exceed 32766;

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

If the definition exceeds the above limit, the varchar field is forcibly converted to the text type and produces warning.

c) The length of the limit

The length of a row definition is the limit of the varchar length in the actual application. MySQL requires a row to have a defined length of not more than 65535. If the defined table length exceeds this value, the prompt

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

2. Calculation example

Give two examples to illustrate the actual length of the calculation.

A If a table has only one varchar type, as defined as

CREATE table t4 (c varchar (N)) CHARSET=GBK;

Then the maximum value for n here is (65535-1-2)/2= 32766.

The reason for minus 1 is that the actual row is stored starting with the second byte ';

The reason for minus 2 is that the length of the varchar head is 2 bytes;

The reason for the addition of 2 is that the character encoding is GBK.

b If a table is defined as

CREATE table t4 (c int, C2 char (), C3 varchar (N)) Charset=utf8;

Then the maximum value for n here is (65535-1-2-4-30*3)/3=21812

Minus 1 and minus 2 are the same as the previous example;

The reason for minus 4 is that C of type int is 4 bytes;

The reason for reducing the 30*3 is that char (30) occupies 90 bytes and the encoding is UTF8.

If the varchar exceeds the above B rule and is strongly converted to the text type, then each field occupies a defined length of 11 bytes, which is not "varchar" anymore.

Read some information on the internet, and did some local experiments, the original Vachar the maximum length is really uncertain (based on whether there is a non-empty field to decide)
We did a local experiment, innodb+latin the 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
For the second case, the empty field can not be added to the length of 65533, the maximum can only to 65532, in the end should be the quotation of that kind of argument.

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

Name varchar NOT NULL would be 1 byte (length) + chars (latin1)
Name varchar NOT NULL is 2 bytes (length) + to chars (latin1)
Name varchar (65533) not NULL is 2 bytes (length) + Up to 65533 chars (latin1)
Name varchar (65532) is 2 bytes (length) + Up to 65532 chars (latin1) + 1 null byte

To sum up, the original MySQL vachar field type Although the maximum length is 65535, but it is not able to save so much data, the maximum can to 65533 (not allow non-null fields), when the Non-empty field can only to 65532.

2.CHAR (M), VARCHAR (m) different
The length of the column defined by CHAR (m) is fixed, and M can be between 0~255, and when the char value is saved, a space is padded to the right of the value to reach the specified length. When a char value is retrieved, the trailing space is removed. No case conversion is made during storage or retrieval. Char stores fixed-length data is convenient, char field index efficiency level, such as the definition of char (10), then whether you store data to reach 10 bytes, will take up 10 bytes of space, insufficient automatically filled with space.

The length of the column defined by VARCHAR (m) is a variable-length string, and the M value can be between 0~65535, (the maximum valid length of the VARCHAR is determined by the maximum row size and the character set used). The overall maximum length is 65,532 bytes). The varchar value saves only the number of characters required, plus a single byte to record the length (two bytes if the length of the column declaration exceeds 255). The varchar value is saved without padding. The trailing blanks are still retained when the value is saved and retrieved, conforming to standard SQL. VARCHAR store variable length data, but storage efficiency is not high char. If the possible value of a field is an unfixed length, we only know that it cannot exceed 10 characters, and it is most cost-effective to define it as VARCHAR (10). The actual length of the varchar type is the actual length of its value +1. Why "+1"? This byte is used to save how much length is actually used. Consider from the space, use varchar suitable, consider from the efficiency, use char suitable, the key is according to the actual situation to find the tradeoff point.

The biggest difference between char and varchar is that one is fixed length and one is variable length. Because it is a variable length, the actual string is actually stored, plus a byte of the length of the record string (two bytes if more than 255 is required). If the value assigned to a char or varchar column exceeds the maximum length of the column, the value is cropped to fit. If the character being trimmed is not a space, a warning is generated. If you crop a non-space character, it causes an error (not a warning) and disables the insertion of the value by using strict SQL mode.

3. Differences between varchar and text, BLOB types
The Varchar,blob and text types are variable-length types, and their storage requirements depend on the actual length of the column value (expressed in the previous table with L), not on the maximum possible size of the type. For example, a varchar (10) column can hold a string with a maximum length of 10 characters, and the actual storage need is the length of the string, plus 1 bytes to record the length of the string. For the string ' ABCD ', L is 4 and the storage requirement is 5 bytes.

The blob and text types require 1,2,3 or 4 bytes to record the length of the column value, depending on the maximum possible length of the type. varchar need to define size, with a maximum limit of 65535 bytes, and text is not required. If you assign a value that exceeds the maximum length of a column type to a BLOB or text column, the value is truncated to fit it.

A blob is a large binary object that can hold a variable number of data. 4 blob types Tinyblob, blobs, Mediumblob, and Longblob differ only in terms of the maximum length at which they can hold the value.

BLOBs can store pictures, text is no good, text can only store plain text files. 4 text types Tinytext, text, Mediumtext, and Longtext correspond to 4 blob types and have the same maximum length and storage requirements. The only difference between a blob and a text type is that the sort and comparison of BLOB values is done in a case-sensitive manner, while the text value is not case sensitive. In other words, a text is a case insensitive blob.

4. Summarizing Char,varchar,text Differences
The difference in length, char range is 0~255,varchar maximum is 64k, but note that the 64k here is the length of the entire row, to consider the other column, and if there is not NULL will occupy a bit, for different character sets, effective length is not the same, For example, the UTF8, up to 21845, but also to remove the other column, but varchar in general the storage is enough. If you encounter a large text, consider using text, maximum to 4G.

Efficiency is basically char>varchar>text, but if you're using a InnoDB engine, it's recommended to use varchar instead of char.

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

It is necessary for the database to choose the appropriate data type storage, which has a certain impact on performance. Here in the fragmentary record two pens, for type int, if you do not need to access negative values, it is best to add unsigned; For fields that often appear in the Where statement, consider indexing, which is especially appropriate for indexing.