Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "what is the maximum value of MySQL varchar type". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Question: what is the maximum storage length of the varchar field type of the MySQL database?

Analysis of problems

Everything is subject to the official documents, and the official description is as follows:

In MySQL 4.1 the length is always 1 byte. In MySQL 5.0 the length may be either 1 byte (for up to 255) or 2 bytes (for 256 to 65535).

It probably means:

Before MySQL 4.1, the length was always 1 byte (varchar (20), which means 20 bytes)

After MySQL 5. 0, the length can be 1 byte (up to 255 bytes) or 2 bytes (256 to 65535)

According to the official website, the maximum value is 65535bytesfeuf8mb4. In the case of coding, each character accounts for 4 bytes, and the maximum value should be 16383.75.

65535Accord 4" 16383 ".75 practical verification

It seems that a conclusion has been reached by now, but is this really the case?

Shall we give it a try?

Mysql version: select version (); / / 5.71.If a table has only one varchar type

The definition is as follows:

CREATETABLE`t1` (`c`varchar (N) DEFAULTNULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

What is the maximum length N of the `c` field in table T1?

(65535 − 1 − 2) / 4 "16383"

Note:

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

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

The reason for dividing 4 is that the character encoding is utf8mb4.

2) what if there are many other types of situations in the table

The definition is as follows:

CREATETABLE`t2` (`c1`int (10) DEFAULTNULL, `c2`char (32) DEFAULTNULL, `c3`varchar (N) DEFAULTNULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

What is the maximum length N of the `c` field in Table T2?

(65535 − 1 − 2 − 4 − 32) / 4 16350

Note:

The reasons for minus 1 and 2 are the same as above.

The reason for minus 4 is that the int type occupies 4 bytes

The reason for the minus 32'4 is that the char type encoded by utf8mb4 occupies 4 bytes (length 32)

Let's verify that it is as described in the above inference calculation:

1) modify the length of the c3 field in the T2 table to 16350alter table `t2` modify column `c3` varchar (16350)

The execution was successful.

2) modify the length of c3 field in T2 table to 16351

Alter table `t2` modify column `c3` varchar (16351)

The execution failed. The error message is as follows:

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs.

This is the end of the content of "what is the maximum value of MySQL varchar type". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.