字符编码 - 关于 MySQL 的 varchar 长度计算的疑问

Question

先创建一个表

CREATE TABLE `test` (
  `a` varchar(3) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

字段a类型为varchar，长度3为三个字节，也就是说字段a里面最多可以存三个字节长度的数据。
但是我只想下面的sql语句竟然能成功。SQL语句为:

insert into test values ('汉ab');

“汉”unicode的字节长度为3，'ab'两个字符的字节长度为2，总共有的字节长度为5了，为什么能插入成功呢，我的sql_mode设置的为严格模式。

MySQL编码信息为：

> mysql> show variables like "%char%";                            
+--------------------------+---------------------------------------------+
| Variable_name            | Value                                       |
+--------------------------+---------------------------------------------+
| character_set_client     | utf8                                        |
| character_set_connection | utf8                                        |
| character_set_database   | latin1                                      |
| character_set_filesystem | binary                                      |
| character_set_results    | utf8                                        |
| character_set_server     | latin1                                      |
| character_set_system     | utf8                                        |
| character_sets_dir       | D:\lamp\mysql-5.6.23-winx64\share\charsets\ |
+--------------------------+---------------------------------------------+

Answer 1

The length of mysql varchar type refers to the number of characters, not the number of bytes, and has nothing to do with encoding.

SELECT char_length('中文');

2

SELECT length('中文');

6 (Each Chinese character occupies 3 bytes under utf8 encoding)

Answer 2

In mysql, varchar(3) does not represent a length of 3 bytes, but represents 3 characters. For varchar(3), "I am not" and "abc" are the same length~

Answer 3

I am not a DBA. I briefly read the MySQL documentation. It should be that MySQL uses utf8 in CHARSET. The stored data is not directly calculated in bytes. For Western European characters, Chinese characters and the like, multibyte charcter is used. It can be considered that It stores words rather than bits.

Reference:

http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8.html
http://dev.mysql.com/doc/refman/5.0/en/string -type-overview.html

Answer 4

After mysql 5.5, it will no longer be calculated by bytes

Answer 5

Mysql varchar number calculation method - number calculation? 2 bytes? 3 bytes? 4 bytes? Encoding format determines everything!

Answer 6

UTF-8, you must first understand what format it is, a variable-length character set, which uses 2-3 bytes to represent a character.
Therefore, the number of words can only be counted in terms of characters, and both full-width and half-width characters are treated as one character.
But there is a situation where you need to be careful about the limit length of VARCHAR, that is, if the character type is gbk, each character occupies a maximum of 2 bytes, and the maximum length cannot exceed 32766; if the character type is utf8, each character occupies a maximum of 3 bytes , the maximum length cannot exceed 21845. For forums with more English, using GBK each character occupies 2 bytes, while using UTF-8 English only occupies one byte. If the definition exceeds the above limit, the varchar field will be forcibly converted to text type and a warning

will be generated.

Answer 7

I have never understood the field length of mysql;
varchar(1)
varchar(20)
char(20)
int(1)
int(20)
Are the storage data lengths different?
Is the hard drive space occupied different?