This is the 13th day of my participation in the August More text Challenge. For details, see: August More Text Challenge

Writing in the front

About the maximum value of MySQL Varchar field type calculation, maybe we have been wrong, this article starts from the problem, through the practice to verify some practical experience, hope to everyone’s development work some help ~

Background description

Recently, when designing a technical scheme, my colleague considered that a table would like to use vARCHAR type for storage instead of text, so it is necessary to determine the maximum length of VARCHAR to evaluate whether the storage length bottleneck will be encountered in the later stage.

What is the maximum size of a vARCHar field in a MySQL database?

Problem analysis

Everything is subject to the official document. 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 roughly means:

  • In MySQL 4.1, the length is always 1 byte.

  • After MySQL 5.0, the length can be 1 byte (maximum 255 bytes) or 2 bytes (256 to 65535).

According to the official website, the maximum value is 65535bytes. For utF8MB4 encoding, each character is 4 bytes. The maximum value should be 16383.75

65535/4 = 16383.75Copy the code

Practice verification

That might seem like a conclusion, but is that really the case?

Let’s try it out, shall we?

Mysql version: select version(); / / 5.7Copy the code

1, if a table has only one VARCHar type

The definition is as follows:

CREATE TABLE t1 (
  c varchar(N) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

What is the maximum length N of ‘c’ in table T1?

(65535-1-2) / 4 = 16383Copy the code

Remark:

The reason for subtracting 1 is that the actual row store starts at the second byte;

The reason for subtracting 2 is that the varchar header is 2 bytes long;

The reason for this is that the character encoding is UTF8MB4.

2) What if the table contains multiple other types of cases

The definition is as follows:

CREATE TABLE `t2` (
  `c1` int(10) DEFAULT NULL,
  `c2` char(32) DEFAULT NULL,
  `c3` varchar(N) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

What is the maximum length N of ‘c’ in table T2?

(65535-1-2-4-32 ∗ 4) / 4 = 16350Copy the code

Remark:

· The reasons for subtraction 1 and 2 are the same as above;

· Minus 4 because int takes 4 bytes;

· Minus 32*4 because utF8MB4 encoding char type takes 4 bytes (length 32)

Let’s verify that it is as stated in the above inference calculation:

1) Change the length of field C3 in table T2 to 16350

alter table `t2` modify column `c3` varchar(16350);
Copy the code

The execution succeeds.

2) Change the length of field C3 in table T2 to 16351

alter table `t2` modify column `c3` varchar(16351);
Copy the code

Failed to execute, and the following error message is displayed:

Column length too big for column ‘name’ (max = 16383); use BLOB or TEXT instead.

To summarize

Q: How many characters can a varchar store?

This depends on the character set used in the table. Latin1, GBK, UTF8, and UTF8MB4 encodings need 1, 2, 3, and 4 bytes to store a character, respectively, while taking into account the impact of removing other fields.

Real knowledge comes from practice. You can try it briefly before you come to a conclusion.

– END –

Author: Architecture improvement road, ten years of research and development wind and rain road, dacang architect, CSDN blog expert, focus on architecture technology precipitation learning and sharing, career and cognitive upgrading, adhere to the sharing of grounding gas dry articles, look forward to growing with you. Follow and private message I reply “01”, send you a programmer growth advance gift package, welcome to hook up.

The maximum value of the MySQL Varchar type is not the same as that of the MySQL Varchar type

Thanks for reading!