深入了解MySQL字符集乱码

字符集一直是MySQL让人蛋疼的问题,MySQL8.0将默认字符集定义为utf8mb4,如果一个DBA没有碰到过字符集乱码的问题,那肯定不是一个合格的厨子。

Unicode与UTF-8

Unicode 是一个很大的集合,现在的规模可以容纳100多万个符号。每个符号的编码都不一样,比如,U+0639表示阿拉伯字母Ain,U+0041表示英语的大写字母A,U+4E25表示汉字严.

Unicode 只是一个符号集,它只规定了符号的二进制代码,却没有规定这个二进制代码应该如何存储。

比如,汉字严的 Unicode 是十六进制数4E25,转换成二进制数足足有15位(100111000100101),也就是说,这个符号的表示至少需要2个字节。表示其他更大的符号,可能需要3个字节或者4个字节,甚至更多。

unicode编码表

utf-8编码表

MySQL字符集参数意义

mysql> show variables like '%char%';
+----------------------------------------+------------------------------------------------------+
| Variable_name                          | Value                                                |
+----------------------------------------+------------------------------------------------------+
| character_set_client                   | utf8                                                 |
| character_set_connection               | utf8                                                 |
| character_set_database                 | latin1                                               |
| character_set_filesystem               | binary                                               |
| character_set_results                  | utf8                                                 |
| character_set_server                   | latin1                                               |
| character_set_system                   | utf8                                                 |
| character_sets_dir                     | /home/shuail/src/sgrdb/debug/install/share/charsets/ |
| simple_password_check_other_characters | 1                                                    |
+----------------------------------------+------------------------------------------------------+

服务器端设置的参数

  1. character_set_server: create database不指定字符集时,使用此参数的字符集。
  2. character_set_database: 不可设置参数,用户表示当前数据库的字符集。USE到不同库时,会变成不同库的字符集。
  3. character_set_filesystem: 文件名解析时使用的字符集,比如load data, select into outfile等命令指定文件时会用到。
  4. character_set_system: server层存储标识符等用到的字符集,固定utf8.

客户端设置的参数

  1. character_set_client: 用户指定客户端的字符集
  2. character_set_connection: 用户指定客户端的字符集
  3. character_set_results: 服务器返回给客户端消息之前,会把对应的字段转换成此值所规定的字符集

列的字符集

列的字符集可以使用create table选项进行统一定义,也可以单独对某个列进行定义:

GreatOpenSource> create table t1(c1 varchar(20) charset latin1, c2 varchar(20)) charset utf8;
Query OK, 0 rows affected (0.02 sec)

GreatOpenSource> show full columns from t1;
+-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type        | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| c1    | varchar(20) | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| c2    | varchar(20) | utf8_general_ci   | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.01 sec)

terminal字符集

除了MySQL的字符集,还有一个字符集比较容易被忽略,那就是终端的字符集,比如terminal、iterm2等终端,都可以设置不同的字符集,用于终端显示的判断。

插入过程字符集转化流程

charset

举例分析

中国编码

汉字 Unicode UTF-8
4E2D E4B8AD
56FD E59BBD

插入流程

表定义见列的字符集一节

GreatOpenSource> set names latin1;
Query OK, 0 rows affected (0.00 sec)

GreatOpenSource> insert into t1 values("中国", "中国");
Query OK, 1 row affected (0.02 sec)

GreatOpenSource> select * from t1;
+--------+--------+
| c1     | c2     |
+--------+--------+
| 中国   | 中国   |
+--------+--------+
1 row in set (0.00 sec)
GreatOpenSource> select hex(c1), hex(c2) from t1;
+--------------+----------------------------+
| hex(c1)      | hex(c2)                    |
+--------------+----------------------------+
| E4B8ADE59BBD | C3A4C2B8C2ADC3A5E280BAC2BD |
+--------------+----------------------------+
1 row in set (0.00 sec)
  1. 终端数据同样的汉字,编码相同,均为utf-8编码.
  2. server将字符按照character_set_client=latin1解析,转化为character_set_connection=latin1, 由于两个字符集一样,故不做任何转化。
  3. server将字符按照character_set_connection=latin1解析,转化为character_set_system=utf8,字符集不同,需要逐字转换
    1. latin1转为unicode, 中国的latin1=E4B8ADE59BBD, 转为unicode=E4B8ADE59BBD
    2. unicode转为utf-8, unicode转为utf-8为C3A4C2B8C2ADC3A5E280BAC2BD
  4. server按照列定义字符集进行转化
    1. c1由utf-8转为latin1,即是上面的逆向转化, 最后存储E4B8ADE59BBD
    2. c2定义为utf-8,不需要转化,故直接存储C3A4C2B8C2ADC3A5E280BAC2BD

虽然最后终端两个字符都能正常显示,但是在内部存储其实是两种存储格式。

深入源码

character_set_client => character_set_connection

sql_yacc.yy

text_literal:
          TEXT_STRING
          {
            LEX_STRING tmp;
            CHARSET_INFO *cs_con= thd->variables.collation_connection;
            CHARSET_INFO *cs_cli= thd->variables.character_set_client;
            uint repertoire= thd->lex->text_string_is_7bit &&
                             my_charset_is_ascii_based(cs_cli) ?
                             MY_REPERTOIRE_ASCII : MY_REPERTOIRE_UNICODE30;
            if (thd->charset_is_collation_connection ||
                (repertoire == MY_REPERTOIRE_ASCII &&
                 my_charset_is_ascii_based(cs_con)))
              tmp= $1;
            else
            {
              if (thd->convert_string(&tmp, cs_con, $1.str, $1.length, cs_cli))
                MYSQL_YYABORT;
            }
            $$= new (thd->mem_root) Item_string(thd, tmp.str, tmp.length,
                                                cs_con,
                                                DERIVATION_COERCIBLE,
                                                repertoire);
            if ($$ == NULL)
              MYSQL_YYABORT;
          }

character_set_connection => character_set_system

| > my_convert_using_func
| | > my_convert
| | | > copy_and_convert
| | | | > sql_strmake_with_convert
| | | | | > Item::set_name
| | | | | | > Item_string::fix_and_set_name_from_value
| | | | | | | > Item_string::Item_string

character_set_system => field charset

| > my_convert_fix
| | > String_copier::well_formed_copy
| | | > Field_varstring::store
| | | | > Item::save_str_value_in_field
| | | | | > Item_string::save_in_field
| | | | | | > fill_record
| | | | | | | > fill_record_n_invoke_before_triggers
| | | | | | | | > mysql_insert

参考文献

  1. 字符编码笔记:ASCII,Unicode 和 UTF-8
  2. unicode编码表
  3. utf-8编码表