VARCHAR length in mysql

This article introduces the character length and related parameters of the varchar data type in mysql. For more information, see this article.

This article introduces the character length and related parameters of the varchar data type in mysql. For more information, see this article.

In MySQL 65535 or later versions, the varchar data type can be up to 65532 characters in length, that is, bytes of data can be stored, and the start and second bits occupy three bytes. That is to say, in versions 4.1 or earlier, you can use varchar to store data in fixed TEXT or BLOB format. This effectively reduces the file size.

I. VARCHAR storage and row length restrictions

1. in VARCHAR (N), N refers to the length of characters. The VARCHAR type supports a maximum of 65535 characters and 65535 bytes, but does not support varchar with a length of 65535, 65535 should contain the length of all fields, the length of the Variable Length Field identifier, and the accumulation of NULL identifier. The content starts with 1 to 2 bytes to indicate the actual length (2 bytes if the length exceeds 255 ). So there are other overhead. The actual storage length is 65532.

(The maximum length of the MySQL database varchar type in versions earlier than 5.0.3 is 255, and its data range can be 0 ~ 255)

2. Because the null flag occupies one byte, you can remove the not null restriction.

3. MySQL requires that the definition length of a row cannot exceed 65535, which means that the total length of all columns cannot exceed 65535. If the total length of a column exceeds this length, it cannot be created.

Ii. VARCHAR Length Encoding restrictions

1. Overview

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.

2. Example

(1) 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.

(2) 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.

Iii. SQL Mode

In MySQL, the SQL mode is often used to solve the following problems:

1. By setting the SQL Mode, you can complete data verification with different strict degrees to effectively ensure data accuracy.

2. you can set the SQL Mode to ANSI to ensure that most SQL statements comply with the standard SQL syntax. Therefore, you do not need to modify the business SQL statements when migrating between different databases.

3. Before migrating data between different databases, you can set the SQL Mode to make it easier for MySQL to migrate data to the target database.

View when SQL Mode

Mysql> select @ SQL _mode;

STRICT_TRANS_TABLES (strict mode) implements strict data validation, so that error data cannot be inserted into the table.

If you set SQL _mode to '', a table can be created, but a warning message is displayed,

Mysql> set SQL _mode = '';

Mysql> create table test4 (a varchar (25000) charset = utf8;

Mysql> show warnings;

The warning message indicates that the VARCHAR can be created because MySQL automatically converts the VARCHAR to the Text type.

Mysql> show create table test4;

1. Restrictions

The following rules apply to field definitions:

A) Storage restrictions

Varchar field is the actual content is stored in the Cluster Index, the content of the first with 1 to 2 bytes to show the actual length (length across 255 need 2 bytes ), the maximum length cannot exceed 65535.

B) encoding length limit

If the character type is gbk, each character occupies a maximum of 2 bytes, 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.

For forums with a lot of English effort, the application of GBK occupies 2 bytes for each character, while the application of UTF-8 English occupies only one byte.

If the defined time spans the preceding limits, 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. The definition length of a MySQL request line cannot exceed 65535. If the defined table length spans this value, a prompt is displayed.

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not ing BLOBs, is 65535. You have to change some columns to TEXT or BLOBs.

If one item is set to varchar (50)
Of course, the English version is 50.
What about Chinese?
UTF-8 Chinese occupies 3 bytes
So, can this varchar (50) only store 16 Chinese characters?
Mysql varchar (50) Stores 50 files in both Chinese and English.

In MySQL5, The varchar field type is described as follows: varchar (m) variable-length string. M indicates the maximum column length. M ranges from 0 to 65,535. (The maximum actual length of a VARCHAR is determined by the maximum row size and the character set used. The maximum valid length is 65,532 bytes ).
Why is this change? I really feel that the MySQL manual is too unfriendly, because you need to read it carefully before you can find this description: MySQL 5.1 complies with standard SQL specifications, the trailing space of the VARCHAR value is not deleted. When VARCHAR is saved, it uses a prefix of one or two bytes plus data. If the length declared by the VARCHAR column is greater than 255, the length prefix is two bytes.
Well, it seems I understand a little bit. However, when the length is greater than 255, the prefix of two bytes is used. The primary subtraction question is: 65535-2 = 65533. I don't know how these Daniel calculate it. Do you still have questions?
Note: I tested UTF8 encoding. The maximum length of varchar is 21854 bytes.
In mysql 5.0.45, the database code utf8 is tested: varchar is defined as 21785 at most. That is to say, only 21785 letters, numbers, and Chinese characters are allowed.
Suppose: varchar has a maximum byte value of 65535, and utf8 is encoded into three characters: 65535/3 = 21785.