Mysql varchar length problem

If the setting in an item is varchar (50) Then of course the English is 50 then the Chinese is utf-8 3 bytes So, is this varchar (50) only able to save 16 characters? No, this is the case with MySQL, but not after 5.0. MySQL varchar (50) Both Chinese and English are stored 50, but the total length of all varchar fields in a table is related to the encoding, if it is utf-8, then about 65535/3, if it is GBK, then probably 65535/2.
MySQL5 document, where the varchar field type is described as: varchar (m) variable length string. M represents the maximum column length. The range of M is 0 to 65,535. (The maximum actual length of varchar is determined by the size of the longest row and the character set used, and the maximum effective length is 65,532 bytes). Why is it so transformed? I really feel that the MySQL handbook is too unfriendly, because you have to read it carefully to see this description: MySQL 5.1 complies with the standard SQL specification and does not remove trailing spaces for varchar values. VarChar is saved with a byte or two bytes long prefix + data. If the varchar column declaration is longer than 255, the length prefix is two bytes.

What is the maximum length of varchar in MySQL? This is not a fixed number. This article briefly describes the restriction rules.

1. Restriction rules

The restriction of a field has 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, the actual storage starts at the second byte, and then the actual length is 1 to 2 bytes (2 bytes longer than 255), so the maximum length cannot exceed 65535.

b) Encoding length limit

The character type is GBK, with a maximum of 2 bytes per character

The character type is UTF8, with a maximum of 3 bytes per character

If the limit above is defined, the varchar field is forcibly converted to the text type and generates warning.

c) Limit of the length of the president

The length of a row definition is the limit of the varchar length in the actual application. MySQL requires that a row's definition length cannot exceed 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 have the 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;

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

The reason for minus 1 is that the actual row storage starts with the second byte ';

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

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;

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

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

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

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

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

Mysql varchar length problem