Length of the varchar field of mysql _ MySQL

The length of the mysql varchar field bitsCN.com

Length of the varchar field of mysql

When designing system fields today, I found myself not familiar with varchar. I have set a field. the type is VARCHER, and I tested it. it is found that not much is full. I think it is strange that MYSQL 5.5 or above does not have a length of 65535. why is it only a little bit? Later, we found that the default VARCHER length is still 255. if you want it to be longer, you have to specify it. also, you cannot specify it as 65535 or 65534. This will make a mistake. the reason is as follows:

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.

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.

BitsCN.com