It has always been thought that there are two bytes to record the length (a small length can also be recorded in a byte), so the problem was very boring at that time, but then some people in the group to explain, suddenly found that the original thing is not so simple
The MYSQL compact format, each record has a byte to represent the null field distribution, if the table has a field allowed to be empty, the maximum can be set to 65532, if no field allows null, then that byte can be saved, the maximum can be defined to 65533, do not know is not this reason
So the Internet to read some data, and in the local did some experiments, the original Vachar the maximum length is really uncertain (according to whether there is a non-empty field to decide)
We did a local experiment, innodb+latin the environment
Copy Code code 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
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
Copy Code code as follows:
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.
The following is a supplementary note from other netizens:
This is not a fixed number. This article briefly describes the restriction rules.
Strlen calculates string length, one Chinese when 2 characters
Mb_strlen according to its character encoding mode, the statistic character quot
Count the number of elements in an array or the number of attributes in an object
Copy Code code as follows:
<?php
Header (' Content-type:text/html;charset=utf-8 ');
$string 1 = "Chechun industry";//define character variable in
$string 2= "Xcy";//define English character variable
Direct output look at the length of their
Echo strlen ($string 1);
echo "</br>";
Echo strlen ($string 2);
echo "</br>";
Try Mb_strlen with PHP multibyte extension functions
Echo Mb_strlen ($string 1, ' UTF8 ');
echo "</br>";
Echo Mb_strlen ($string 2, ' UTF8 ');
echo "</br>";
?>
The output results are:
9
3
3
3
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.
For a more English forum, use GBK to occupy 2 bytes per character, while using UTF-8 English is only one byte.
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, of course this is not "varchar" anymore.
