Restrictions on MySQL database varchar _ MySQL

Description of MySQL database varchar restriction rules bitsCN.com

MySQLDatabaseVarcharWhat is the maximum length? In fact, this is not a fixed number. the length of varchar isRestriction rules. In this article, we will introduce the varchar restriction rules in the MySQL database, and describe the restriction rules with an actual example. Next, let's take a look at this part.

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, the system prompts ERROR1118 (42000): rowsiz1_large. Themaximumrowsizefortheusedtabletype, notcountingBLOBs, is65535.YouhavetochangesomecolumnstoTEXTorBLOBs.

2. computing example

Here are two examples to illustrate the calculation of the actual length.

A) If a table has only one varchar type, such as createtablet4 (cvarchar (N) charset = gbk, the maximum value of N here 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 as createtablet4 (cint, c2char (30), c3varchar (N) charset = utf8; the maximum value of N is (65535-1-2-4-30*3)/3 = 21812.

The reasons for reducing 1 and 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 forcibly converted to the text type, each field occupies 11 bytes. of course, this is no longer a "varchar.

The following is an introduction to the varchar restriction rules in the MySQL database. I hope this introduction will be helpful to you!

BitsCN.com