背景

最近有同事问,set names 时会同时设置了3个session变量

  1. SET character_set_client = charset_name;
  2. SET character_set_results = charset_name;
  3. SET character_set_connection = charset_name;

就从变量名字来看,character_set_client 是设置客户端相关的字符集,character_set_results 是设置返回结果相关的字符集,character_set_connection 这个就有点不太明白了,这个有啥用呢?

概念说明

通过官方文档来看:

  1. character_set_client 是指客户端发送过来的语句的编码;
  2. character_set_connection 是指mysqld收到客户端的语句后,要转换到的编码;
  3. 而 character_set_results 是指server执行语句后,返回给客户端的数据的编码。

对人来说,能够理解的是各种各样的符号,而对计算机来说,只能理解二进制,二进制和符号之间的对应关系就是编码。不同地域国家都有自己的一套符号集合,每个都各自用一组二进制数字表示,从而形成了不同的编码,字符集就可以看作是编码和符号的对应关系集合。同一个二进制数在不同的字符集下可能对应完全不一样的字符,如在GBK字符集中,C4E3 对应的是,而在big5字符集中对应的是,而 在unicode中的编码是4F60,在Collation-Charts 这个网站有字符集和编码对应关系图,可以非常直观地看到不同编码下二进制数和符号的对应关系。

set names 设置的3个变量就是设置mysqld和客户端通信时,mysqld应该如何解读client发来的字符,以及返回给客户端什么样的编码。

实验测试

环境如下:

  1. mysql> show variables like 'character%';
  2. +--------------------------+-------------------------------------+
  3. | Variable_name | Value |
  4. +--------------------------+-------------------------------------+
  5. | character_set_client | utf8 |
  6. | character_set_connection | utf8 |
  7. | character_set_database | utf8 |
  8. | character_set_filesystem | binary |
  9. | character_set_results | utf8 |
  10. | character_set_server | utf8 |
  11. | character_set_system | utf8 |

server端的3个编码设置都是utf8。 另外,客户端是标准 mysql client,使用的编码是utf8,和sever端编码是一致的。

建一张表作为测试

  1. CREATE TABLE t1(id INT, name VARCHAR(200) CHARSET utf8) engine=InnoDB;
  2. INSERT INTO t1 VALUES(0, '你好');
  3. mysql> SELECT id, name, hex(name) FROM t1;
  4. +------+--------+--------------+
  5. | id | name | hex(name) |
  6. +------+--------+--------------+
  7. | 0 | 你好 | E4BDA0E5A5BD |
  8. +------+--------+--------------+

下面我们分别改变这3个值,来看下结果会有什么变化

Case 1 只改变 character_set_client

  1. SET character_set_client=gbk;
  2. INSERT INTO t1 VALUES(1, '你好');
  3. mysql> SELECT id, name, hex(name) FROM t1;
  4. +------+-----------+--------------------+
  5. | id | name | hex(name) |
  6. +------+-----------+--------------------+
  7. | 0 | 你好 | E4BDA0E5A5BD |
  8. | 1 | 浣犲ソ | E6B5A3E78AB2E382BD |
  9. +------+-----------+--------------------+
  10. 2 rows in set (0.00 sec)

可以看到返回的数据已经乱码了,并且数据库里存的确实和第一条记录不一样。

case 2 只改变 character_set_connection

  1. SET names utf8;
  2. SET character_set_connection = gbk;
  3. INSERT INTO t1 VALUES(2, '你好');
  4. mysql> SELECT id, name, hex(name) FROM t1;
  5. +------+-----------+--------------------+
  6. | id | name | hex(name) |
  7. +------+-----------+--------------------+
  8. | 0 | 你好 | E4BDA0E5A5BD |
  9. | 1 | 浣犲ソ | E6B5A3E78AB2E382BD |
  10. | 2 | 你好 | E4BDA0E5A5BD |
  11. +------+-----------+--------------------+
  12. 3 rows in set (0.00 sec)

case 3 只改变 character_set_results

  1. SET names utf8;
  2. SET character_set_results = gbk;
  3. INSERT INTO t1 VALUES(3, '你好');
  4. mysql> select id, name, hex(name) from t1;
  5. +------+--------+--------------------+
  6. | id | name | hex(name) |
  7. +------+--------+--------------------+
  8. | 0 | | E4BDA0E5A5BD |
  9. | 1 | 你好 | E6B5A3E78AB2E382BD |
  10. | 2 | | E4BDA0E5A5BD |
  11. | 3 | | E4BDA0E5A5BD |
  12. +------+--------+--------------------+
  13. 4 rows in set (0.00 sec)

再改回原样,看下结果

  1. SET names utf8;
  2. mysql> SELECT id, name, hex(name) FROM t1;
  3. +------+-----------+--------------------+
  4. | id | name | hex(name) |
  5. +------+-----------+--------------------+
  6. | 0 | 你好 | E4BDA0E5A5BD |
  7. | 1 | 浣犲ソ | E6B5A3E78AB2E382BD |
  8. | 2 | 你好 | E4BDA0E5A5BD |
  9. | 3 | 你好 | E4BDA0E5A5BD |
  10. +------+-----------+--------------------+
  11. 4 rows in set (0.00 sec)

分析

我们先理下字符集在整个过程中是怎样变化的,然后再分析上面的case

客户发送请求时:

  1. A1 客户端发送出语句(总是以utf8)------> A2 sever收到语句解析(按character_set_client指定编码)
  2. |
  3. v
  4. A4 数据进入mysqld内部存储<--------- A3 sever判断是否需要转换编码(以character_set_connection 目标编码)

server返回结果时:

  1. B1 server返回结果(按character_set_results 指定编码) ----->B2客户端解析编码显示(总是以utf8)

A3步是否需要转换编码,代码中的逻辑是这样的,在sql_yacc.yy文件中:

  1. LEX_STRING tmp;
  2. THD *thd= YYTHD;
  3. const CHARSET_INFO *cs_con= thd->variables.collation_connection;
  4. const CHARSET_INFO *cs_cli= thd->variables.character_set_client;
  5. uint repertoire= thd->lex->text_string_is_7bit &&
  6. my_charset_is_ascii_based(cs_cli) ?
  7. MY_REPERTOIRE_ASCII : MY_REPERTOIRE_UNICODE30;
  8. if (thd->charset_is_collation_connection ||
  9. (repertoire == MY_REPERTOIRE_ASCII &&
  10. my_charset_is_ascii_based(cs_con)))
  11. tmp= $1;
  12. else
  13. {
  14. if (thd->convert_string(&tmp, cs_con, $1.str, $1.length, cs_cli))
  15. MYSQL_YYABORT;
  16. }
  17. $$= new (thd->mem_root) Item_string(tmp.str, tmp.length, cs_con,
  18. DERIVATION_COERCIBLE,
  19. repertoire);
  20. if ($$ == NULL)
  21. MYSQL_YYABORT;

如果 character_set_clientcharacter_set_connection 一样,或者当前的字符编码是和ASCII兼容,并且都是ASCII范围内的,就不转换,其它情况就转。

对于case1 实际上客户端发过来是UTF8的,但A2步骤server认为客户端的编码是GBK的,就按GBK来解析,同时满足A3步骤的转换条件,所以就误将UTF8编码认为是GBK,然后又给转成了UTF8。 你好的UTF8编码是 E4BDA0E5A5BD 6个字节,每个字符3个字节,按GBK来解析的话,因为GBK是固定2个字节,就认为有3个字符,然后转成UTF8,虽然UTF8是变长的,但是这里的3个GBK字符按值都是要占3个字节的,转出来一共9个字节。所以case1看到的实际存储的值一共9个字节,比原来的大。 在返回时,是按UTF8返回的,因为存了3个UTF8字符,所以客户端看到的就是3个。

对于case2 A2步骤没问题,问题是出在A3,按照转换逻辑,此时需要把UTF8转成GBK,这里因为character_set_client是正确的,所以转换的源不会识别错,转换成GBK自然也不会错,后面存储成UTF8时,再从GBK转成UTF8,也没错,因为UTF8和GBK字符集里都包含 ‘你’和’好’,所以相互转换也不会出错,只是多了2次转换。

对于case3 错在返回字符集设置的和客户端不匹配,在返回时,server将所有字符转成GBK的,结果客户端一根筋的认为是UTF8,就解析错了。 比较有意思的是第二条记录,即case1错误插进去的,显示出来是对的。 为什么呢,因为在case1中存的时候,是按 UTF8->强制解析为GBK->然后转为UTF8 这个逻辑存下去的,而返回的时候,因为server会将存的UTF8又给转回GBK,然后客户端又拿着这个GBK误以为是UTF8解析,实际上是case1的逆向过程,虽然2个方向都是错的,最终显示是好的,所谓的负负得正吧,哈哈。

对于case2 ,数据从客户端进入server的时候,多做了2次转换,最终显示还是对的,但不是所有场景都是这样,如下面这种

  1. set names utf8;
  2. set character_set_connection = latin1;
  3. INSERT INTO t1 VALUES(4, '你好');
  4. set names utf8;
  5. mysql> SELECT id, name, hex(name) FROM t1;
  6. +------+-----------+--------------------+
  7. | id | name | hex(name) |
  8. +------+-----------+--------------------+
  9. | 0 | 你好 | E4BDA0E5A5BD |
  10. | 1 | 浣犲ソ | E6B5A3E78AB2E382BD |
  11. | 2 | 你好 | E4BDA0E5A5BD |
  12. | 3 | 你好 | E4BDA0E5A5BD |
  13. | 4 | ?? | 3F3F |
  14. +------+-----------+--------------------+
  15. 5 rows in set (0.00 sec)

为什么呢,因为在 UTF8转latin1时,信息丢失了,latin1字符编码所能表达的字符集是远小于utf8的,就不在其中,这2个字符在转换中被转成了 ??,之后存储转换成UTF8时,?只有一个字节3F,还原回去还是 3F

总结

character_set_clientcharacter_set_results 是一定要和客户端一致,不要依赖于负负得正,character_set_connection 设置和character_set_client 不一致,有丢失数据的风险,所以尽量也一致,总之这3个值就是要一样,还要和客户端一致,所以才有了 set names 这个快捷命令。关于为啥要有 character_set_connection 这一步转换,笔者目前还没看出来,以后理解了再更新,如果读者朋友知道的话,请不吝赐教。