A detailed introduction to the varchar length of MySQL database

The varchar type of MySQL database tutorial has a maximum length limit of 255 in the version below 5.0.3, and its data range can be 0~255.

In the MySQL5.0.3 and above versions, the length of the varchar data type is supported to 65535, which means that 65,532 bytes of data can be stored, and the starting and ending bits take up 3 bytes, that is, data stored in a fixed text or BLOB format in the following versions of 5.0.3 can be used in high The version uses variable-length varchar to store, which effectively reduces the size of the database file.
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, it prompts ERROR1118 (42000): rowsizetoolarge.themaximumrowsizefortheusedtabletype,notcountingblobs,is65535 . Youhavetochangesomecolumnstotextorblobs.

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 Createtablet4 (Cvarchar (N)) CHARSET=GBK, then the maximum value for N 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 CREATETABLET4 (Cint,c2char (), C3varchar (N)) Charset=utf8, then the maximum value for N is (65535-1-2-4-30*3)/3=21812.

The reason for minus 1 and minus 2 is 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 coerced to the text type, each field occupies a defined length of 11 bytes, which is not "varchar" anymore.

About the MySQL database varchar restrictions on the knowledge of the rules are introduced here, I hope this introduction can be harvested for you