Some things to note about varchar type in Mysql

Storage rules for varchar

In versions below 4.0, varchar(20) means 20 bytes. If you store UTF8 Chinese characters, you can only store 6 (3 bytes for each Chinese character).
In versions 5.0 and above, varchar(20) means 20 characters. No matter whether they are numbers, letters or UTF8 Chinese characters (each Chinese character is 3 bytes), 20 characters can be stored, and the maximum size is 65532 bytes.
The varchar field stores the actual content separately outside the clustered index, and the beginning of the content uses 1 to 2 bytes to indicate the actual length.
This is what the official statement says:

Values ​​in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.
In contrast to CHAR, VARCHAR values ​​are stored as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value.
A column uses one length byte if values ​​require no more than 255 bytes, two length bytes if values ​​may require more than 255 bytes.

Difference between varchar and char

Difference 1: Fixed length and variable length

char means fixed length, and varchar means variable length. When the inserted string exceeds their length, it is handled according to the situation. If it is strict mode, the insertion will be rejected and an error message will be prompted. If it is loose mode, it will be intercepted and then inserted. If the length of the inserted string is less than the defined length, it will be handled differently, such as char(10), which means that 10 characters are stored. No matter how many characters you insert, it will be 10. If it is less than 10, it will be filled with spaces. If varchar(10) is less than 10, then as many characters as inserted will be stored.
How does varchar know the length of the stored string? In fact, for a varchar field, one (if the string length is less than 255) or two bytes (if the length is greater than 255) are needed to store the length of the string. But because he needs a prefix to indicate the specific number of bytes (because varchar is variable length, without this length value he does not know how to read the data).

The second difference is the storage capacity.

For char, the maximum number of characters that can be stored is 255, regardless of the encoding.
And varchar can store up to 65532 characters. The maximum effective length of a VARCHAR is determined by the maximum row size and the character set used. The overall maximum length is 65,532 bytes

VARCHAR encoding length limit

If the character type is gbk, each character occupies at most 2 bytes and the maximum length cannot exceed 32766; if the character type is utf8, each character occupies at most 3 bytes and the maximum length cannot exceed 21845. If the above limit is exceeded during definition, the varchar field will be forcibly converted to text type and a warning will be generated.

Line length limit

What causes the varchar length limit in practical applications is the length of a row definition. MySQL requires that the definition length of a row cannot exceed 65535. If the defined table length exceeds this value, 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.
That is to say, for example, if you create a table with two varhcar type fields in the table structure, the total length of these two fields cannot exceed 65535.
The official description is as follows:

Every table has a maximum row size of 65,535 bytes.
This maximum applies to all storage engines, but a given engine might have additional constraints that result in a lower effective maximum row size.

Control bit of varchar

The Varchar character type in MySQL also reserves 1 byte for other control information.

Example

Example 1: If a table has only one field of VARCHAR(N) type and utf8 encoding, what is the maximum value of N?

For example: create table tb_name1(a varchar(N)) default charset=utf8, then the maximum value of N = (65535-1-2)/3=21844.
The reason for the minus 1 is that the actual row storage starts at the second byte.
The reason for subtracting 2 is that the 2 bytes in the varchar header represent the length.
The reason for the division by 3 is that the character encoding is utf8.
SQL test:

create table tb_name1(a varchar(21844)) default charset=utf8;
Query OK, 0 rows affected (0.38 sec)

drop table tb_name1;
Query OK, 0 rows affected (0.00 sec)

create table tb_name1(a varchar(21845)) default charset=utf8;
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

Example 2: If a table has a field of type VARCHAR(N) and other field types, encoded in utf8, what is the maximum value of N?

For example: create table tb_name2(a int, b char(20), c varchar(N)) default charset=utf8;
Then: Maximum value of N = (65535-1-2-4-203)/3 = 21822
The reason for the minus 1 is that the actual row storage starts at the second byte.
The reason for subtracting 2 is that the 2 bytes in the varchar header represent the length.
The reason for subtracting 4 is that the int type of the a field occupies 4 bytes.
The reason for subtracting 203 is that char(20) takes up 60 bytes and the encoding is utf8.

SQL test:

create table tb_name2(a int, b char(20), c varchar(21822)) default charset=utf8;
Query OK, 0 rows affected (0.28 sec)

drop table tb_name2;
Query OK, 0 rows affected (0.20 sec)

create table tb_name2(a int, b char(20), c varchar(21823)) default charset=utf8;
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

Example 3: If a table has multiple fields of VARCHAR(N) type and other field types, gbk encoding, what is the maximum value of N?

For example: create table tb_name3(a int, b char(20), c varchar(50), d varchar(N)) default charset=gbk;
Then: Maximum value of N = (65535-1-1-2-4-202-502)/2 = 32693
The reason for the first minus 1 is that actual row storage starts at the second byte.
The second minus 1 means that the second varchar(50) has a 1-byte header indicating the length (less than 255).
The reason for subtracting 2 is that the 2 bytes in the varchar header represent the length.
The reason for subtracting 202 is that char(20) takes up 40 bytes and the encoding is gbk.
The reason for subtracting 502 is that varchar(50) takes up 100 bytes and the encoding is gbk.

SQL Test:

create table tb_name3(a int, b char(20), c varchar(50), d varchar(32694)) default charset=gbk;
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

create table tb_name3(a int, b char(20), c varchar(50), d varchar(32693)) default charset=gbk;
Query OK, 0 rows affected (0.18 sec)

The above are the details of some points that need to be paid attention to in the varchar type in Mysql. For more information about the Mysql varchar type, please pay attention to other related articles on 123WORDPRESS.COM!