The maximum length of MySQL varchar is always regarded as two bytes to record the length (the length can also be recorded in one byte ), this problem was boring at the time, but some people in the group explained it and suddenly found that it was not that simple.
In mysql compact format, each record has a byte to indicate the distribution of NULL fields. if any field in the table is allowed to be NULL, the maximum value is 65532. If no field is allowed to be NULL, that byte can be saved, and the maximum value can be defined as 65533. I wonder if this is the reason.
So 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.
The code is as follows:
-- 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
The code is as follows:
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.
The following is a supplement provided by other netizens:
This is not a fixed number. This article briefly describes the restrictions.
Strlen calculates the string length. a Chinese character must be 2 characters long.
Mb_strlen uses its character encoding mode to calculate the character"
Count: calculates the number of elements in the array or the number of attributes in the object.
The code is as follows:
Header ('content-Type: text/html; charset = UTF-8 ');
$ String1 = "Xie Chunye"; // defines Chinese character variables
$ String2 = "xcy"; // defines English character variables
// Output them directly to check their length
Echo strlen ($ string1 );
Echo"
";
Echo strlen ($ string2 );
Echo"
";
// Use the php multi-byte extension function mb_strlen to try it out.
Echo mb_strlen ($ string1, 'utf8 ');
Echo"
";
Echo mb_strlen ($ string2, 'utf8 ');
Echo"
";
?>
The output result is:
9
3
3
3
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.
For forums with a lot of English, using GBK occupies 2 bytes for each character, while using UTF-8 English occupies only one byte.
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.
Source: http://www.bitsCN.com/article/31589.htm
