Length of the varchar field of mysql

Source: Internet
Author: User

The length of the varchar field of mysql is found to be insufficient in terms of varchar when designing system fields. I have set a field. the type is VARCHER, And I tested it. it is found that not much is full. I think it is strange that MYSQL 5.5 or above does not have a length of 65535. Why is it only a little bit? Later, we found that the default VARCHER length is still 255. If you want it to be longer, you have to specify it. also, you cannot specify it as 65535 or 65534. This will make a mistake. the reason is as follows: 1. When defining a field, the following rules apply to the restriction rule field: a) the storage restriction varchar field stores the actual content separately in the clustered index, the content must start 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 line is limited by the length defined by a line in practice. 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. 2. 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 the decrease of 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 minus 1 and minus 2 is the same as that in the previous example; the reason for 4 reduction is that c of the int type occupies 4 bytes; the reason for 30*3 reduction 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.

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.