Description of restriction rules for MySQL database varchar

  • 2020-05-12 06:17:17
  • OfStack

What is the maximum length of varchar in the MySQL database? Actually, this is not a fixed number. The length of varchar is limited. In this paper, we will introduce the restriction rules of varchar in MySQL database, and explain the restriction rules with a practical example. Next, let's learn about this part from 1.

1. Restrictive rules

The restrictions on fields are defined with the following rules:

a) storage limits

The varchar field stores the actual content separately from the clustered index, and the content begins with 1 to 2 bytes representing the actual length (2 bytes if the length exceeds 255), so the maximum length cannot exceed 65535.

b) code length limitation

If the character type is gbk, each character takes up to 2 bytes, and the maximum length cannot exceed 32766.

If the character type is utf8, each character takes up to 3 bytes and the maximum length cannot exceed 21845.

If the above limit is exceeded, the varchar field is forced to be of type text and warning is generated.

c) length limit

What causes the varchar length limit in practice is the length defined by one line. MySQL requires that a line be defined no longer than 65535. If tables defined length more than this value, the prompt ERROR1118 (42000) : Rowsizetoolarge. Themaximumrowsizefortheusedtabletype, notcountingBLOBs, is65535. YouhavetochangesomecolumnstoTEXTorBLOBs.

2. Calculation examples

Take two examples to illustrate the calculation of actual length 1.

a) if there is only one varchar type in a table, createtablet4(cvarchar(N))charset=gbk; Then the maximum value of N is (65535-1-2)/2=32766.

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

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

The reason for dividing by 2 is that the character encoding is gbk.

b) if 1 table is defined as createtablet4(cint,c2char(30),c3varchar(N))charset=utf8; The maximum value of N here is (65535-1-2-4-30*3)/3=21812.

Minus 1 and minus 2 for the same reason;

The reason for minus 4 is that c of type int takes 4 bytes;

The reason for minus 30*3 is that char(30) takes 90 bytes and the encoding is utf8.

If varchar exceeds the b rule above and is forced to be of type text, then each field occupies a defined length of 11 bytes, which is no longer "varchar".

This is the end of the introduction of MySQL database varchar limit rules knowledge, I hope this introduction can be useful to you!


Related articles: