不得不注意！那些容易被忽视的MySQL字符集问题？

2019-12-05

现象

在使用MySQL客户端书写SQL语句的时候，我们可以在字符串前边加_charset_name的符号，其中的charset_name对应着某个具体的字符集，废话不多说，先写两个例子看一下：

mysql> SELECT _utf8'我';
+-----+
| 我 |
+-----+
| 我 |
+-----+
1 row in set (0.04 sec)

mysql> SELECT _gbk'我';
+-----+
| 鎴 |
+-----+
| 鎴 |
+-----+
1 row in set, 1 warning (0.02 sec)

可以看到第一个查询结果正常，第二个查询出现了乱码。为什么呢？下边细细道来。

原因

我们知道MySQL是一个C/S架构的软件，可以有很多客户端连接到服务器进行交互。客户端发送给服务器的请求以及服务器发送给客户端的响应本质上都是一个二进制的字节串，每当我们从客户端发送一个请求到服务器，服务器处理完成之后再把响应返回给客户端的过程其实发生了很多字符集转换过程。

首先请求会被MySQL客户端编码为字节序列之后通过网络传输到服务器。对于MySQL自带的客户端来说，这个编码过程使用的字符集和我们使用的操作系统的默认字符集是一样的，类Unix系统的默认字符集就是utf8，windows系统的默认字符集就是gbk。
服务器收到字节序列请求之后，会认为该字节串是按照character_set_client系统变量编码的，之后将其从character_set_client转换到character_set_connection，之后进行更深入的处理。
最后再将响应发送到客户端的时候，又会按照character_set_results进行编码。
客户端收到响应字节串之后，按照本客户端规定的字符集进行解码。对于MySQL自带的客户端来说，这个解码过程使用的字符集和我们使用的操作系统的默认字符集是一样的，类Unix系统的默认字符集就是utf8，Windows系统的默认字符集就是gbk。

总结一下这几个涉及到的通信字符集系统变量：

系统变量描述character_set_client服务器解码请求时使用的字符集character_set_connection服务器处理请求时会把请求字符串从character_set_client转为character_set_connectioncharacter_set_results服务器向客户端返回数据时使用的字符集

现在我的系统中的这几个系统变量的值都是utf8：

mysql> SHOW VARIABLES LIKE 'character_set_client';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_client | utf8 |
+----------------------+-------+
1 row in set (0.24 sec)

mysql> SHOW VARIABLES LIKE 'character_set_connection';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| character_set_connection | utf8 |
+--------------------------+-------+
1 row in set (0.25 sec)

mysql> SHOW VARIABLES LIKE 'character_set_results';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| character_set_results | utf8 |
+-----------------------+-------+
1 row in set (0.30 sec)

如果我们使用了_charset_name前缀，意味着禁止服务器将后续字节从character_set_client转换到character_set_connection，而是默认使用_charset_name代表的字符集作为它后续字节的字符集。比方说：

mysql> SELECT _gbk'我';
+-----+
| 鎴 |
+-----+
| 鎴 |
+-----+
1 row in set, 1 warning (0.02 sec)

我现在使用的是macOS操作系统，所以

客户端发送请求时会将字符'我'按照utf8进行编码，也就是：0xE68891。
服务器收到请求后发现有前缀_gbk，则不会将其后边的字节0xE68891进行从character_set_client到character_set_connection的转换，而是直接把0xE68891认为是某个字符串由gbk编码后得到的字节序列。
然后再把上述0xE68891从gbk转换为character_set_results，也就是utf8。0xE688在gbk中代表汉字'鎴'，而0x91无法解码（我们可以看到上述查询结果中有1个warning）。我们紧接着上边的查询语句执行一下SHOW WARNINGS：

mysql> SHOW WARNINGSG
*************************** 1. row ***************************
 Level: Warning
 Code: 1300
Message: Invalid gbk character string: '91'
1 row in set (0.01 sec)

之后将汉字'鎴'再按照utf8进行编码，得到的结果就是E98EB4，把它发送到客户端。

客户端收到之后再解码到屏幕上，解码也使用utf8字符集，所以就出现了鎴。

扩展

如果在我的机器上我执行SELECT LENGTH(_gbk '我')会得到什么结果呢（LENGTH函数用来统计某个字符串共占用多少字节）？有很多小伙伴不经思考，脱口而出：2!哈哈，我们看一下结果验证一下：

mysql> SELECT LENGTH(_gbk '我');
+--------------------+
| LENGTH(_gbk '我') |
+--------------------+
| 3 |
+--------------------+
1 row in set, 1 warning (0.01 sec)

WTH?竟然是3？其实再回想一下我们上边所说的，因为'我'前边加了_gbk，所以不会经历从character_set_client到character_set_connection的转换过程，而是直接把0xE68891当作是一个采用gbk编码的字节串。这个字节串中有3个字节，当然结果就返回3了（虽然0x91这个字节在gbk字符集中是无效的，可以看到上边查询语句中也给出了Warning）。

思考

如果我现在不使用基于macOS操作系统的客户端，而采用基于Windows操作系统的客户端来发送请求，那么下边的语句的返回结果将会是什么呢：

SELECT LENGTH(_utf8 '我');