Shulou(Shulou.com)05/31 Report--

How to find the maximum length of varchar in MySQL? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

1. Restriction rules

The restrictions of a field have the following rules when the field is defined:

Www.2cto.com

A) Storage restrictions

The varchar field stores the actual content separately from the clustered index, and the content begins with 1 to 2 bytes to represent the actual length (2 bytes are needed if the length exceeds 255), so the maximum length cannot exceed 65535.

B) Code length limit

If the character type is gbk, the maximum length of each character is 2 bytes and the maximum length cannot exceed 32766.

If the character type is utf8, each character is up to 3 bytes and the maximum length cannot exceed 21845.

If the definition exceeds the above limit, the varchar field will be forcibly converted to text and warning will be generated.

C) Row length limit

It is the length of a row definition that causes the varchar length limit in practical applications. MySQL requires that the defined length of a row cannot exceed 65535. If the defined table length exceeds this value, prompt

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. Calculation example

Give two examples to illustrate the calculation of the actual length.

A) if a table has only one varchar type, such as defined as

Create table T4 (c varchar (N)) charset=gbk

Then the maximum value of N here is (65535-1-2) / 2 = 32766.

The reason for minus 1 is that the actual row storage starts from the second byte.

The reason for minus 2 is that the 2 bytes of the varchar header represent the length

The reason for dividing 2 is that the character encoding is gbk. Www.2cto.com

B) if a table is defined as

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-303) / 3-21812.

Minus 1 and minus 2 are the same as the above example

The reason for minus 4 is that c of type int occupies 4 bytes.

The reason for minus 303 is that char (30) occupies 90 bytes and the code is utf8.

If the varchar exceeds the b rule above and is forcibly converted to the text type, then each field occupies a defined length of 11 bytes, which is no longer a "varchar".

The answer to the question about how to find the maximum length of varchar in MySQL is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.