mysql 数据库中varchar的长度与字节，字符串的关系

Question

经常看到别人设置varchar的长度是255，它可以存储多少个汉字。在phpmyadmin中看到varchar的定义是变长（0-65535），它的最大长度到底是255还是65535，是不是可以设置成varchar（60000）？还有长度和字节的关系，如上设置，它的长度就是60000，可以存储60000个字节，这样理解对吗？

Answer 1

CREATE TABLE `test` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `content` varchar(5) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `test`(`content`) VALUES ('123456');
INSERT INTO `test`(`content`) VALUES ('中国人民银行');
SELECT * FROM `test`;
返回:
id  content
1   12345
2   中国人民银

It can be seen that varchar(5) can store 5 characters, whether they are numbers, letters, or Chinese characters.

CREATE TABLE `test2` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `content` varchar(21842) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

How do you get the maximum number of characters 21842 that can be accommodated by the varchar type field in this table?
21842 = (65535-1-2-4)/3
MySQL requires that the defined length of a row cannot exceed 65535 (including multiple fields), so there are 65535.
The maximum effective length of a varchar depends on the maximum row size.
The reason for subtracting 1 is that the actual row storage starts from the 2nd byte.
The reason for subtracting 2 is that the 2 bytes in the varchar header represent the length.
The reason for subtracting 4 is that the int type of field id occupies 4 bytes.
The reason for dividing by 3 is that one utf8 character takes up 3 bytes.

If you change varchar(21842) to varchar(21844) in the test2 table, the following error will be reported:
1118 - 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.

Selection of char, varchar and text types in MySQL:
If you know the fixed length, use char. For example, MD5 string is fixed to 32 bits.
Fields that change frequently use varchar.
For characters exceeding 255, you can only use varchar or text, not char.
Where varchar can be used, text is not used.

Answer 2

The character size of MySQL is related to the character set. If the character set is ascii, Chinese cannot be saved (it will be displayed as garbled characters). If it is UTF8, each character is 1-3 bytes.

Speak with facts

MySQL 5.6 for testing

Check the database character set first

sqlshow variables like '%char%';

Result: UTF8

Variable_name	Value
character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_database	utf8
character_set_filesystem	binary
character_set_results	utf8mb4
character_set_server	utf8
character_set_system	utf8
character_sets_dir	D:Program FilesMySQLMySQL Server 5.6sharecharsets

Create table (Note: varchar(10))

sqlCREATE TABLE `test_char` (
    `s` VARCHAR(10) NULL DEFAULT NULL
)

Insert data

Cross ASCII characters, executed correctly

sqlinsert into test_char
(s)
values
('0123456789')
;

Ten Chinese characters, executed correctly

sqlinsert into test_char
(s)
values
('一二三四五六七八九十')
;

But the eleven ASCII or Chinese characters all reported errors, saying the data was too long.

Conclusion

The

length is the character length of the current character set, not the byte length!

Answer 3

varchar(6000) can store 6000 bytes. If the character set uses UTF-8, since UTF-8 is a "variable length encoding", English letters are compiled into one byte, and Chinese characters are usually 3 bytes, only extremely rare words will be encoded into 4-6 bytes. Therefore, it can be considered that when the encoding is UTF-8, 2000 Chinese characters can be stored in varchar(6000)

Answer 4

The length of varchar and char does not refer to the byte length, but the maximum number of characters in the current character set. For example, varchar 100, which stores ascii characters, can store up to 100 characters. When storing Chinese, it can store up to 100 characters. Not 33.