MySQL-解析客户端SQL执行字符集参数设置

前言:

在使用SQL进行查询时，有时会遇到客户端查询返回乱码或者查询结果异常的问题，造成这些问题的主要原因为字符集的参数character_set_client，character_set_connection，character_set_results，collation_connection设置不合理导致，只有保证客户端环境字符集与数据库参数设置字符集一致，才能避免问题的发生。

接下来文章的主要内容是关于解析SQL查询过程中涉及到的相关字符集参数的作用以及如何正确设置字符集参数。

MySQL字符集转化流程

从客户端发起SQL查询到查询结果返回这个过程中需要经过多个流程的编码以及解码，当其中一个流程的字符集设置不正确，就可能导致查询结果返回乱码或者查询结果异常的情况。

字符集转化流程如下：

解析字符集转化流程

        默认环境字符集设置:

        客户端编码:UTF8

        character_set_client:UTF8MB4

        character_set_connection:UTF8MB4

        表编码:UTF8MB4

        character_set_results:UTF8MB4

        collation_connection:utf8mb4_0900_ai_ci

1 解析客户端环境的字符集作用，客户端会客户端环境的字符集将发送字符编码为字节值发送给MySQL

使用默认字符集从客户端发起一个SQL查询。

select id from test.test where name='牛';

通过tcpdump对3306端口进行抓包，注意不要对连接进行加密，不然会无法抓取到包信息。

tcpdump -S -nn -tttt -i lo host 1XX.1XX.1XX.XX and port 3306 and tcp -c 100 -w /tmp/tcpdump.cap

 对抓包内容进行分析，可以确认对于中文"牛"编码为e7899b字节值。

那么这个e7899b字节值是使用什么字符集编码的呢？

我们可以通过MySQL hex函数进行编码确认e7899b字节值就是通过utf8的编码，与当前客户端环境的字符集一致。

test@3306 22:24:  [test]> SELECT hex(CONVERT('牛' USING utf8 ));
+---------------------------------+
| hex(CONVERT('牛' USING utf8 ))  |
+---------------------------------+
| E7899B                          |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)
​
test@3306 22:24:  [test]>

2 接下来解析character_set_client参数，MySQL根据character_set_client设置的字符集将客户端发送的字节值转化为字符

将character_set_client参数设置为gbk。

set character_set_client=gbk;

再次执行SQL查询select id from test.test where name='牛';查询不到结果，说明这里字符发生了转化。

使用当前字符集配置插入数据（2，'牛')。

insert into test values(2,'牛');

再将character_set_client调整为utf8mb4,执行查询。

select id from test.test where name='牛';

可以看到通过gbk插入的'牛'，在utf8里面显示为'鐗'。

而'鐗'是UTF8的字节值'E7 89 9B'通过character_set_client字符集GBK解码的结果，这说明MySQL是按character_set_client的字符集对客户端的字节值进行解码。

3 接下来解析character_set_connection以及collation_connection参数

MySQL根据character_set_connection设置的字符集将上一步的字符转化为当前字符集编码，这个参数不同的字符集设置其实不影响结果的查询返回，因为下一步还是会转化为表字段的字符集再进行比较,这一步主要的作用还是collation_connection参数对字符串的比较规则

接下来我们测试character_set_connection的设置对查询的影响

使用默认字符集从客户端发起一个SQL查询。

select id from test.test where name='牛';

结果返回一条数据。

将character_set_connection设置为gbk。

set character_set_connection=gbk;

从客户端再次执行相同的SQL查询​。

select id from test.test where name='牛';

结果还是返回一条数据，说明character_set_connection参数的字符集设置不影响查询。

分析collation_connection参数的作用

使用默认字符集查询以下SQL。

select 'a'='A'

修改collation_connection为gbk_bin。

set character_set_connection=gbk;
set collation_connection=gbk_bin;

再次查询以下SQL。

select 'a'='A'

需要注意的是，如果需要到表里面查询数据，表列的比较规则还是优先级最高的，不会使用collation_connection

执行以下SQL查询。

select * from test where name='A';

还是返回结果a,因为表的比较规则utf8mb4_0900_ai_ci是不区分大小写的。

4 接下来解析character_set_results参数

MySQL根据参数character_set_results设置的字符集将返回结果编码发送给客户端

使用默认字符集从客户端发起一个SQL查询。

select name from test.test where name='牛';

查询结果返回'牛'。

使用tcpdump进行抓包分析，可以发现'牛'返回的字节值为e7899b为utf8的编码。

将character_set_results设置为gbk。

set character_set_results=gbk;

使用tcpdump进行抓包分析，可以发现'牛'返回的字节值变为ca53为gbk的编码,这说明character_set_results影响着查询结果的返回。

总结

1 四个参数character_set_client，character_set_connection，character_set_results，collation_connection只有character_set_client，character_set_results，collation_connection对查询可能造成影响。

2 避免客户端查询返回乱码或者查询结果异常的方法就是保证客户端环境字符集与数据库参数character_set_client，character_set_connection，character_set_results，collation_connection字符集设置一致。