What is the maximum varchar length in MySQL?

Source: Internet
Author: User
What is the maximum varchar length in MySQL? This is not a fixed number. Briefly describe the restriction rules. When defining a field, there are the following rules: a) Storage restrictions varchar? The field stores the actual content separately in the clustered index. The content starts with 1 to 2 bytes to indicate the actual length (2 words are required when the length exceeds 255 ).

What is the maximum varchar length in MySQL? This is not a fixed number. Briefly describe the restriction rules. When defining a field, there are the following rules: a) Storage restrictions varchar? The field stores the actual content separately in the clustered index. The content starts with 1 to 2 bytes to indicate the actual length (2 words are required when the length exceeds 255 ).

What is the maximum varchar length in MySQL? This is not a fixed number. Briefly describe the restriction rules.

Restriction rules

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

A) Storage restrictions

Varchar? The 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.

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;

Then 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.

Source: http://dinglin.iteye.com/blog/914276

What is the maximum varchar length in MySQL? This is not a fixed number. Briefly describe the restriction rules. When defining a field, there are the following rules: a) Storage restrictions varchar? The 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: if the character type is gbk, each character occupies up to 2 bytes, and the maximum length cannot exceed 32766. If the character type is utf8, each character occupies up to 3 bytes, 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) the length of a row is limited by the length defined by a row in varchar .? MySQL requires that the definition length of a row cannot exceed 65535. If the defined table length exceeds this value, ERROR 1118 (42000): Row size too large is displayed. the maximum row size for the used table type, not counting BLOBs, It is 65535. you have to change some columns to TEXT or BLOBs. Here are two examples to illustrate the calculation of the actual length. A )? If a table has only one varchar type, such as create table t4 (c varchar (N) charset = gbk, the maximum value of N here is (65535-1-2) /2 =? 32766. [...]

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.