MySQL varchar Length

 

Http://dinglin.iteye.com/blog/914276

Http://www.cnblogs.com/fakis/archive/2011/03/07/1976532.html

If varchar (50) is set in one item, then of course 50 for English, then for Chinese, the UTF-8 Chinese occupies 3 bytes, then the varchar (50) can I only store 16 Chinese characters? This is not the case. MySQL earlier than MySQL, but later than MySQL 5.0 MySQL varchar (50) Stores 50 varchar fields in both Chinese and English, but the total length of all varchar fields in a table is related to encoding. If it is UTF-8, it is about 65535/3. If it is GBK, about 65535/2.
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.

What is the maximum varchar length in MySQL? This is not a fixed number. This article briefly describes the restrictions.

1. Restrictions

Field restrictions include the following rules when a field is defined:

A)
Storage restrictions

The varchar field stores the actual content separately outside the clustered index. The actual storage starts from the second byte, next, we need to use 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 up to 2 bytes

If the character type is utf8, each character occupies up to 3 bytes

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.