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

This article is about how to understand varchar (N) in MySQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

A preface

What does varchar (N) N mean and how many Chinese characters can be stored? It's a clich é question. Today, I was asked by a developer colleague about this question, so I might as well write an article to introduce it in detail.

Second, theoretical knowledge

Let's first explain the definition of varchar in the historical version of MySQL:

Below version 4.0, varchar (50) refers to 50 bytes. If you store UTF8 characters, you can only store 16 characters (3 bytes each).

Version 5.0 and above, varchar (50), refers to 50 characters, which can be stored in either numeric, alphabetic or UTF8 Chinese (3 bytes each).

Storage limit

Extra bytes are required to store the length of characters: 1 byte for less than 255 and 2 bytes for greater than 255

Coding limit

Gbk: up to 2 bytes per character

Utf8: up to 3 bytes per character

Utf8mb4 takes up to 4 bytes per character, Chinese 3 bytes, and emoji emoticons 4 bytes.

Length limit

MySQL defines that the length of rows cannot exceed 65535, which limits the number of columns, such as the char (128) utf8 character set, with a maximum of 65535 / (12832) = 170Chinese characters.

Three tests

Environment Server version: 5.6.26-74.0-log Percona Server

Mysql > create table T1

-> (id int NOT NULL AUTO_INCREMENT primary key

-> name varchar (10)

->) engine=innodb default charset=utf8mb4

Query OK, 0 rows affected (0.01 sec)

Mysql > create table T2

-> (id int NOT NULL AUTO_INCREMENT primary key

-> name varchar (10)

->) engine=innodb default charset=utf8

Query OK, 0 rows affected (0.01 sec)

Mysql > create table T3

-> (id int NOT NULL AUTO_INCREMENT primary key

-> name varchar (10)

->) engine=innodb default charset=gbk

Query OK, 0 rows affected (0.01 sec)

Utf8mb4 character set

Mysql > insert into T1 (name) values ('abcdfeghi')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into T1 (name) values ('abcdfeghij')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into T1 (name) values ('abcdfeghijk')

Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql > insert into T1 (name) values ('one two three four five six seven eight ninety')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into T1 (name) values ('one two three four five six seven eight 91')

Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql > show warnings

+-- +

| | Level | Code | Message | |

+-- +

| | Warning | 1265 | Data truncated for column 'name' at row 1 | |

+-- +

1 row in set (0.00 sec)

Mysql > insert into T1 (name) values ('0123456789')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into T1 (name) values ('01234567890')

Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql > select id,name,length (name), char_length (name) from T1

+-+-

| | id | name | length (name) | char_length (name) | |

+-+-

| | 1 | abcdfeghi | 9 | 9 |

| | 2 | abcdfeghij | 10 | 10 | |

| | 3 | abcdfeghij | 10 | 10 | |

| | 4 | 12, 3, 4, 5, 6, 7, 890 | 30 | 10 | |

| | 5 | one two three four five six seven eight 90 | 30 | 10 |

| | 6 | 0123456789 | 10 | 10 |

| | 7 | 0123456789 | 10 | 10 | |

+-+-

7 rows in set (0.00 sec)

Utf8 character set

Mysql > insert into T2 (name) values ('abcdfeghi')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into T2 (name) values ('abcdfeghij')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into T2 (name) values ('abcdfeghijk')

Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql > insert into T2 (name) values ('one two three four five six seven eight ninety')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into T2 (name) values ('one two three four five six seven eight 91')

Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql > insert into T2 (name) values ('0123456789')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into T2 (name) values ('01234567890')

Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql > select id,name,length (name), char_length (name) from T2

+-+-

| | id | name | length (name) | char_length (name) | |

+-+-

| | 1 | abcdfeghi | 9 | 9 |

| | 2 | abcdfeghij | 10 | 10 | |

| | 3 | abcdfeghij | 10 | 10 | |

| | 4 | 12, 3, 4, 5, 6, 7, 890 | 30 | 10 | |

| | 5 | one two three four five six seven eight 90 | 30 | 10 |

| | 6 | 0123456789 | 10 | 10 |

| | 7 | 0123456789 | 10 | 10 | |

+-+-

7 rows in set (0.00 sec)

Gbk character set

Mysql > insert into T3 (name) values ('abcdfeghi')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into T3 (name) values ('abcdfeghij')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into T3 (name) values ('abcdfeghijk')

Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql > insert into T3 (name) values ('one two three four five six seven eight ninety')

Query OK, 1 row affected (0.01sec)

Mysql > insert into T3 (name) values ('one two three four five six seven eight 91')

Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql > insert into T3 (name) values ('0123456789')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into T3 (name) values ('01234567890')

Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql > select id,name,length (name), char_length (name) from T3

+-+-

| | id | name | length (name) | char_length (name) | |

+-+-

| | 1 | abcdfeghi | 9 | 9 |

| | 2 | abcdfeghij | 10 | 10 | |

| | 3 | abcdfeghij | 10 | 10 | |

| | 4 | 12, 3, 4, 5, 6, 7, 890 | 20 | 10 | |

| | 5 | one two three four five six seven eight 90 | 20 | 10 |

| | 6 | one two three four five six seven eight 90 | 20 | 10 |

| | 7 | 0123456789 | 10 | 10 | |

| | 8 | 0123456789 | 10 | 10 | |

+-+-

8 rows in set (0.00 sec)

As you can see from the above tests, the units of length defined by varchar (N) in the current version are characters, length (str) represents the number of bytes consumed by str, and char_length (str) represents the number of characters occupied by str.

No matter what character set it is, it takes up only one character and one byte for numbers and letters. Chinese characters vary from character set to character set.

Four summaries

Go back and answer the question at the beginning of the article how many Chinese characters can be stored in varchar (N). The answer is that varchar (N) can store N Chinese characters in versions later than 5. 0.

Character set aside, if a line of data is all of type varchar, its maximum length is 65535 bytes.

The formula for calculating the row length is as follows:

Row length = 1

+ (sum of column lengths)

+ (number of NULL columns + delete_flag + 7) / 8

+ (number of variable-length columns)

For MyISAM, you need an extra bit to record whether the value is NULL;. For InnoDB, there is no difference.

Fixed,delete_flag is 1 for row_format and 0 for row_format=dynamic,delete_flag

According to this formula, we can solve the maximum value of the beginning N: (65535-1-2) / 3.

Minus 1 because the actual storage starts at the second byte

Minus 2 because you want to store the actual character length in the list length

Divide 3 because of utf8 coding restrictions

Let's do it again:

Create table T4 (c int, c2 char (30), c3 varchar (N)) charset=utf8

Maximum value of N: (65535-1-2-4-303) / 3

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 above is how to understand varchar (N) in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.