本文授权转载自原作者Shawn, 未经授权请勿转载
“在MySQL Server部署安装、数据库、表、连接等各种场景都涉及到字符集的设置,这些场景中字符集之间的关系是怎样的?不同的字符集设置会有什么影响?
本文的数据和测试均基于MySQL 5.7。
字符集简介
- 字符集charset:字符的编码规则
- 排序规则collation:相同字符集的数据的排序规则
在MySQL5.7中存在41 charset和222种 collation。不同的字符集可以保存不同的字符编码范围,MySQL存储相应编码的字符所占用的字节长度也不相同。如utf8占用3个字节,gbk占用2个字节。MySQL每种字符集都有一个默认的排序规则,可以根据不同的业务场景选择合适的排序规则。
通过SHOW CHARACTER SET;
和SHOW COLLATION;
命令可以查看当前服务器支持的字符集和排序规则列表。
“示例仅截取了部分内容
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
mysql> show collation;
+--------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
| utf8_german2_ci | utf8 | 212 | | Yes | 8 |
| utf8_croatian_ci | utf8 | 213 | | Yes | 8 |
| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |
| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
从上表中可以看出每种编码的默认排序规则,比如utf8编码的默认排序规则为utf8_general_ci
。
MySQL默认字符集配置
MySQL中有8个字符集相关的设置,可通过show variables
命令查看当前配置。
mysql> show variables like '%char%';
+--------------------------+-----------------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /mysql-lin/mysql/share/charsets/ |
+--------------------------+-----------------------------------------------+
默认的编码设置和生效范围如下
变量 | 影响范围 | 默认编码 | 说明 |
---|---|---|---|
character_set_client | Global, Session | utf8 | 客户端请求的数据使用的字符集 |
character_set_connection | Global, Session | utf8 | 连接传输过程中使用的字符集 |
character_set_database | Global, Session | latin1 | 数据库的默认编码格式 |
character_set_filesystem | Global, Session | binary | 访问文件系统上的文件名时使用的编码格式 |
character_set_results | Global, Session | utf8 | 给客户端返回的数据的编码格式 |
character_set_server | Global, Session | latin1 | Mysql Server的默认编码格式 |
character_set_system | Global | utf8 | 数据库服务器存储使用的编码格式,不可动态变更 |
character_sets_dir | Global | dirname | 字符集安装的目录,不可动态变更 |
影响范围Global表示是全局配置,Session表示会影响会话连接传输的数据。character_set_system
和character_sets_dir
是MySQL Server自身使用的编码格式,不会影响客户端会话。
以上设置为MySQL的默认设置,在使用MySQL的过程中,可以为每一个连接会话指定默认值以外的编码格式,如:
- 在连接参数中使用
--default-character-set=xxx
或命令行执行set names xxxx
,会同时修改当前会话的character_set_client
、character_set_connection
和character_set_results
三个字符集配置。 - 创建数据库时指定编码:
CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_general_ci;
,除了指定数据库的编码,MySQL还可以为数据表、数据列都指定不同的编码。 - 指定访问文件系统文件名编码: 在连接参数中使用
--character-set-filesystem=name
字符集编码转换
在日常使用客户端连接使用MySQL Sevrer的过程中,字符集的设置会直接影响客户端的数据写入和读取,设置不当会出现数据丢失、乱码等问题。
编码转换过程
- 客户端终端在命令行输入命令,命令将会被使用终端配置的文本字符集进行编码,向数据库发送编码后的命令
- MySQL Server使用
character_set_client
配置的字符集解析客户端的请求命令 - MySQL Server将请求命令转换为
character_set_connection
字符集 - MySQL Server将上一步结果转换为数据表格字段相应的字符集,并进行数据查询:
- 转换为每个数据字段设置的字符集
- 若数据字段未指定字符集,使用对应数据表指定的字符集
- 若数据表未指定字符集,使用对应数据库指定的字符集
- 若数据库未指定字符集,使用character_set_server的字符集
- 若请求涉及文件系统操作,如LOAD_DATA, LOAD_FILE等
- 将请求处理结果转换为
character_set_results
字符集 - 客户端使用终端文本字符集显示返回的结果
2. 乱码问题
从上述流程可以看出,以下两种场景有可能会产生乱码问题。
- 本地终端显示的文本字符集和
character_set_client
,character_set_results
不一致。 - 内部字符集的存储范围小于外部的字符集,无法存储外部传入的数据,如业务中比较常见的MySQL utf8占用三个字节,而utf8mb4占用四个字节。由于标准的 UTF-8 编码是占用1~4个字节,MySQL 的 utf8 编码只分配三个字节,因此,存储四字节的UTF8字符时,比如 emoji 表情,应该设置 MySQL 的字符集为 utf8mb4 ,以防数据存入不完整。
3. 案例说明
- 读取数据乱码
假设有两个客户端终端,本地的编码分别为GBK和UTF8,分别向数据库中写入一条数据:
客户端1:insert into test value(1, '中文');
客户端2:insert into test value(2, '中文');
此时执行 select * from test
会出现什么结果? 执行结果如下:
客户端1:
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | 中文 |
| 2 | ?? |
+----+------+
客户端2:
mysql> select * from test;
+----+----------+
| id | name |
+----+----------+
| 1 | ÖÐÎÄ |
| 2 | 中文 |
+----+----------+
客户端1本地终端显示编码为GBK,即发送给MySQL Server的字符其实是按照GBK编码的。客户端2本地终端显示编码为utf8,即发送给MySQL Server的字符是按照utf8编码的。由于服务器端的 character_set_client
设置为utf8,即服务器会用utf8来解码客户端发送过来的数据并进行后续处理。在写入数据时,由于MySQL中utf8字符集占用3个字节,gbk只占用两个字节,因此,客户端1和客户端2的数据都可以无损的存储在数据库中。在读取数据时,服务器端将结果根据 character_set_results
的utf8编码后返回给客户端1和客户端2。此时,id1和id2两条数据的真实编码是gbk和utf8。客户端1的终端文本显示编码为GBK,因此,可以正确显示gbk编码的数据1,utf8编码的数据2在终端显示为乱码。客户端2的终端文本显示编码为utf8,因此,可以正确显示utf8编码的数据2,gbk编码的数据1在终端显示为乱码。
- gbk终端写入的数据如何才能不乱码呢?
在连接参数设置--default-character-set=gbk
或在命令行执行set names gbk
, 设置character_set为gbk即可。
mysql> set names gbk;
mysql> insert into test value(3, '中文');
此时,服务器端可以正确写入的数据编码和解码为内部字符集,读取数据时,也可以正确的将内部字符集转换为客户端需要的字符集。
此时再读取数据,客户端1和客户端2都可以正确显示id3的数据。
客户端1:
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | 中文 |
| 2 | ?? |
| 3 | 中文 |
+----+------+
客户端2:
mysql> select * from test;
+----+----------+
| id | name |
+----+----------+
| 1 | ÖÐÎÄ |
| 2 | 中文 |
| 3 | 中文 |
+----+----------+
简易指南
- 在使用MySQL的过程中,需要根据存储的数据类型为数据库和表选择合适的字符集。
- 使用客户端时,使用
set names xxx
命令设置字符集为当前文本显示终端的字符集。
参考资料
MySQL 5.7 Reference Manual[1]
参考资料
[1]MySQL 5.7 Reference Manual: https://dev.mysql.com/doc/refman/5.7/en/charset.html