Basic MySQL Tutorial: About varchar (N)

Basic MySQL Tutorial: About varchar (N)

Preface
What does varchar (N) N mean and how many Chinese characters can it store? This is a common question. Today I was asked by another developer to write an article about this question.

2. Theoretical knowledge
First, describe the definition of varchar in MySQL:
For versions earlier than 4.0, varchar (50) refers to 50 bytes. If UTF8 Chinese characters are stored, only 16 characters can be saved (3 bytes for each Chinese character)
Varchar (50) is a 50-character version. It can contain 50 characters, regardless of the number, letter, or UTF8 Chinese character (3 bytes in each Chinese character ).
Storage restrictions
The length of characters that need to be occupied by additional Bytes: less than 255 is 1 byte, and more than 255 is 2 bytes
Encoding restrictions
Gbk: each character occupies a maximum of 2 bytes.
Utf8: each character occupies a maximum of three bytes.
Utf8mb4 each character occupies up to 4 bytes, Chinese occupies 3 bytes, and emoji occupies 4 bytes
Length limit
The length of a MySQL-defined row cannot exceed 65535. This value limits the number of columns. For example, the char (128) utf8 character set can contain a maximum of 65535/(128*3) = 170 Chinese characters.

Test 3
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 ('abcdefeghij ');
Query OK, 1 row affected (0.00 sec)
Mysql> insert into t1 (name) values ('abcdefeghijk ');
Query OK, 1 row affected, 1 warning (0.00 sec)
Mysql> insert into t1 (name) values ('february 80 or 90 ');
Query OK, 1 row affected (0.00 sec)
Mysql> insert into t1 (name) values ('August 5, 80 or 90, 1 ');
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 ('20140901 ');
Query OK, 1 row affected (0.00 sec)
Mysql> insert into t1 (name) values ('20140901 ');
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 | February 5, 1234, 80 or 90 | 30 | 10 |
| 5 | February 5, 1234, 80 or 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 ('abcdefeghij ');
Query OK, 1 row affected (0.00 sec)
Mysql> insert into t2 (name) values ('abcdefeghijk ');
Query OK, 1 row affected, 1 warning (0.00 sec)
Mysql> insert into t2 (name) values ('february 80 or 90 ');
Query OK, 1 row affected (0.00 sec)
Mysql> insert into t2 (name) values ('August 5, 80 or 90, 1 ');
Query OK, 1 row affected, 1 warning (0.00 sec)
Mysql> insert into t2 (name) values ('20140901 ');
Query OK, 1 row affected (0.00 sec)
Mysql> insert into t2 (name) values ('20140901 ');
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 | February 5, 1234, 80 or 90 | 30 | 10 |
| 5 | February 5, 1234, 80 or 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 ('abcdefeghij ');
Query OK, 1 row affected (0.00 sec)
Mysql> insert into t3 (name) values ('abcdefeghijk ');
Query OK, 1 row affected, 1 warning (0.00 sec)
Mysql> insert into t3 (name) values ('february 80 or 90 ');
Query OK, 1 row affected (0.01 sec)
Mysql> insert into t3 (name) values ('August 5, 80 or 90, 1 ');
Query OK, 1 row affected, 1 warning (0.00 sec)
Mysql> insert into t3 (name) values ('20140901 ');
Query OK, 1 row affected (0.00 sec)
Mysql> insert into t3 (name) values ('20140901 ');
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 | February 5, 1234, 80 or 90 | 20 | 10 |
| 5 | February 5, 1234, 80 or 90 | 20 | 10 |
| 6 | February 5, 1234, 80 or 90 | 20 | 10 |
| 7 | 0123456789 | 10 | 10 |
| 8 | 0123456789 | 10 | 10 |
+ ---- + -------------------------------- + -------------- + ------------------- +
8 rows in set (0.00 sec)
From the test above, we can see that in the current version, the unit of length defined by varchar (N) is character, length (str) indicates the number of bytes occupied by str, char_length (str) the number of characters occupied by str.
Regardless of the character set, numbers and English letters only occupy one character, and also occupy one byte. Chinese characters vary with character sets.

Iv. Summary
Let's look back at the question at the beginning of the article. How many Chinese characters can varchar (N) store. In Versions later than 5.0, varchar (N) can store N Chinese characters.
Aside from the character set, if a row of data is of the varchar type, the 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, one additional bit is required to record whether the value is NULL. For InnoDB, there is no difference
For row_format as fixed, delete_flag is 1; for row_format = dynamic, delete_flag is 0

Based on this formula, we can answer the maximum value of N at the beginning: (65535-1-2)/3
1 is because the actual storage starts from 2nd bytes.
2, because the actual character length needs to be stored in the list Length
Except 3 because utf8 encoding is restricted
Next:
Create table t4 (c int, c2 char (30), c3 varchar (N) charset = utf8;
Maximum Value of N: (65535-1-2-4-30*3)/3
The maximum value of N here is (65535-1-2-4-30*3)/3 = 21812
Subtraction 1 and subtraction 2 are the same as those in the previous example;
The reason for 4 reduction is that int Type c occupies 4 bytes;
The reason for the decrease of 30*3 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 ".