技术分享 | MySQL 字符集再探

作者：傅同学

爱可生研发部成员，主要负责中间件产品开发，热衷技术原理。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。作者：傅同学

本公众号之前发表了一些关于MySQL字符集的文章: 从utf8转换成utf8mb4 、 字符集相关概念 、 有关SQL 语句 、 字符集注意事项 、 乱码问题.

近日, 在为 爱可生开源数据传输工具dtle 增加UTF-32字符集支持过程中，笔者又了解了一下 MySQL 字符集机制。下面补充说明一些内容：

1 字符集

• ASCII 是最基础的字符集，每个 ASCII 字符占用1字节，ASCII 仅利用了8位编码能力的一半，最高位恒为 0 。

  • Latin1 字符集利用剩下的一半编码了西欧常用字母。
• UCS-2 使用2字节编码，容量为65536 。

  • UTF-16 在这基础上, 增加了临时使用2个2字节，编码特殊字符的能力。
• UTF-32 使用定长4字节编码。
• GB 系列、UTF-8 等字符集，0~127编码和 ASCII 一样，使用单字节。在最高位不为0时，额外使用1~3字节编码。

  • 即它们是变长编码，每个字符占用1~4字节

只表达 ASCII 0~127字符的话，Latin1、GB系列、UTF8 等编码是完全相同的. 而 UTF16 和 UTF32 则会有额外的空白。

mysql> select
         hex(convert("sql" using latin1)) as a,
         hex(convert("sql" using gbk)) as b,
         hex(convert("sql" using utf8mb4)) as c,
         hex(convert("sql" using utf16)) as d,
         hex(convert("sql" using utf32)) as e;

| a      | b      | c      | d            | e                        |
+--------+--------+--------+--------------+--------------------------+
| 73716C | 73716C | 73716C | 00730071006C | 00000073000000710000006C |

使用如下 SQL 语句可以看到当前 MySQL 实例支持的字符集(和collation)。

mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME, ID FROM INFORMATION_SCHEMA.COLLATIONS
         ORDER BY CHARACTER_SET_NAME, ID;
...
| gb18030            | gb18030_chinese_ci       | 248 |
| gb18030            | gb18030_bin              | 249 |
| gb18030            | gb18030_unicode_520_ci   | 250 |
...
| utf8mb4            | utf8mb4_general_ci       |  45 |
| utf8mb4            | utf8mb4_bin              |  46 |
| utf8mb4            | utf8mb4_unicode_ci       | 224 |
...

题外：什么是 collation

A collation is a set of rules that defines how to compare and sort character strings.

MySQL文档: collation是进行字符串比较或排序时使用的规则. 例如:

• 大小写是否敏感(case sensitive / insensitive)
• 特殊的相等规则: ij = ij

2 MySQL 系列参数

执行 SQL show variables like 'character%' 可以获得MySQL中关于字符集的几个参数。

character_set_client
character_set_connection
character_set_results
character_set_server
-- 注: 下面3个参数一般无需关心
character_set_filesystem
character_set_database
character_set_system

第06期：梳理 MySQL 字符集的相关概念 中“ 六、字符集系统参数”一节进行了解释，但稍有误差。

2.1 参数 character_set_client

• character_set_client 并不是客户端使用的字符集.
• 用户输入什么数据, 客户端就发送什么数据. 字符集由用户输入决定.
• character_set_client 是服务端的参数.
• 服务端认为客户端发来的数据是以 character_set_client 编码的.

character_set_client不能被设置为以下几个字符集 (参考链接):

ucs2
utf16 / utf16le
utf32

不难发现其共同点为: 都是多字节编码. 至于限制的原因本文不作展开。

2.2 参数 character_set_connection

根据MySQL文档

(after receiving them) The server converts statements sent by the client from character_set_client to character_set_connection. Exception: For string literals that have an introducer such as _utf8mb4 or _latin2…

即收到客户端发来的语句后，服务端(mysqld)会将语句从character_set_client转换为character_set_connection。(除了指明字符集的字符串字面量, 如 _utf8mb4"中文字符串".)

2.3 SET NAMES 语句

mysql> SET NAMES gb2312;

MySQL文档: 该语句设定3个关于 character set 的变量：character_set_client, character_set_connection 和 character_set_results. 该语句不设置 character_set_server 。

注： MySQL客户端的--default-character-set参数效果和SET NAMES语句一样。

$ mysql -h ... --default-character-set=gb2312

3 一个乱码原因的勘误

之前发表的 第09期：有关 MySQL 字符集的乱码问题 “一、转码失败”一节中有如下案例

-- 我的终端字符集是 utf8
...

-- 新建立一个连接，客户端这边字符集为 gb2312
root@ytt-pc:/home/ytt# mysql --default-character-set=gb2312 ...

...
-- 表的字符集为 utf8
mysql> create database ytt_new10;
mysql> create table ytt_new10.t1(a1 varchar(100)) charset utf8mb4;

-- 插入一条数据，有两条警告信息
mysql> insert into ytt_new10.t1 values ("病毒滚吧！");
Query OK, 1 row affected, 2 warnings (0.01 sec)
...

-- 那检索出来看到，数据已经不可逆的乱码了。
mysql> select * from ytt_new10.t1;
| a1        |
+-----------+
| ???▒??▒ |

该文认为乱码原因是“客户端编码设置成和表编码不一致”。

但可以发现：即使客户端和表的编码都是 gb2312 ，仍然会产生乱码。

-- 在上述gb2312 MySQL session中
mysql> create table ytt_new10.t2(a1 varchar(100)) charset gb2312;
mysql> insert into ytt_new10.t2 values ("病毒滚吧！");
Query OK, 1 row affected, 2 warnings (0.02 sec)

mysql> select * from ytt_new10.t2;
+------+
| a1   |
+------+
|      |

事实上，MySQL 知道character_set_client = gb2312，也知道t1表编码为utf8。写入过程中，MySQL 会进行转换，不应当发生乱码。

发生乱码的真正原因是: 我们发送给 MySQL 的数据 ，并不是以 gb2312 编码的。是我们欺骗了 MySQL 。

insert ... "("病毒滚吧！")这个语句, 是以终端字符集 utf8 编码的. MySQL 把 utf8 数据当作 gb2312 数据, 转换成 utf8 数据, 自然产生了乱码.

让我们以 gb2312 发送数据:

# 终端编码为UTF-8. 使用iconv转换文件编码.
$ echo 'insert into ytt_new10.t1 val("病毒滚吧！");' | iconv -f utf8 -t gb2312 > insert-gb.txt
$ cat insert-gb.txt # 终端无法正常显示GB2312字符
insert into t1 val("???????ɣ?");
$ mysql -h ... --default-character-set=gb2312 < insert-gbk.txt

SELECT 时, MySQL 会把数据转换成character_set_results再返回给客户端. 终端可能不支持非 UTF8 字符的显示, 需要转换.

$ mysql ... --default-character-set=utf8 -e 'select * from ytt_new10.t1'
病毒滚吧！
$ mysql ... --default-character-set=gb2312 -e 'select...' | iconv -f gb2312 -t utf8
病毒滚吧！

4 字符集 client 到 connection转换的意义

收到客户端发来的语句后, 服务端会将语句从 character_set_client 转换为 character_set_connection

这个步骤乍一看多此一举

• 直接以client字符集执行, MySQL也会正常转换到表字符集.
• 也可以让客户端直接以connection字符集发送语句.

那么，为什么不将两个参数合并呢? 搜索发现, 使用转换层的潜在原因如下:

不同字符集的某些行为是完全不同的

mysql> set character_set_connection = 'utf8';
mysql> select length("hello");
|               5 |

mysql> set character_set_connection = 'utf32';
mysql> select length("hello");
|              20 |

由于character_set_client的限制，客户端不能直接以 UTF32 等字符集发送语句。如果我们就是想要 UTF32 下的行为（函数结果、排序规则等），就需要由 MySQL 进行一层转换。