MySQL字符集相关变量介绍及binlog中字符集相关缺陷分析

MySQL支持多种字符集（character set）提供用户存储数据，同时允许用不同排序规则（collation）做比较。

本文基于MySQL5.7介绍了字符集相关变量的使用，通过例子描述了这些变量具体意义。分析了MySQL binlog中字符集相关处理的缺陷，这些缺陷会导致复制中断或者主备不一致。最后给出了修复上述缺陷的建议。

MySQL字符集相关基础知识介绍

character_set_system

character_set_system为元数据的字符集，即所有的元数据都使用同一个字符集。试想如果元数据采用不同字符集，INFORMATION_SCHEMA中的相关信息在不同行之间就很难展示。同时该字符集要能够支持多种语言，方便不同语言人群使用自己的语言命名database、table、column。MySQL选择UTF-8作为元数据编码，用源码固定。

  1. sql/mysqld.cc
  2. int mysqld_main(int argc, char **argv)
  3. {
  4.   ...
  5.   system_charset_info= &my_charset_utf8_general_ci;
  6. }
  1. > select @@global.character_set_system;
  2. +-------------------------------+
  3. | @@global.character_set_system |
  4. +-------------------------------+
  5. | utf8                          |
  6. +-------------------------------+

MySQL会将identifier转换为system_charset_info(utf8)。

  1. sql/sql_lex.cc
  2. static int lex_one_token(YYSTYPE *yylval, THD *thd)
  3. {
  4.  case MY_LEX_IDENT:
  5.    ...
  6.  lip->body_utf8_append_literal
  7.    ...
  8. }
  10. void Lex_input_stream::body_utf8_append_literal(THD *thd,
  11.                                                 const LEX_STRING *txt,
  12.                                                 const CHARSET_INFO *txt_cs,
  13.                                                 const char *end_ptr)
  14. {
  15.   ...
  16.   if (!my_charset_same(txt_cs, &my_charset_utf8_general_ci))
  17.   {
  18.     thd->convert_string(&utf_txt,
  19.                        &my_charset_utf8_general_ci,
  20.                        txt->str, txt->length,
  21.                        txt_cs);
  22.   }
  23.   else
  24.   {
  25.     utf_txt.str= txt->str;
  26.     utf_txt.length= txt->length;
  27.   }
  28.   ...
  29. }
  31. sql/sql_yacc.yy
  33. IDENT_sys:
  34. IDENT { $$= $1; }
  35. | IDENT_QUOTED
  36. {
  37.   THD *thd= YYTHD;
  39.   if (thd->charset_is_system_charset)
  40.   {
  41.     ...
  42.   }
  43.   else
  44.   {
  45.     if (thd->convert_string(&$$, system_charset_info,
  46.                         $1.str, $1.length, thd->charset()))
  47.       MYSQL_YYABORT;
  48.   }
  49. }
  50. ;

character_set_server/collation_server

当create database没有指定charset/collation就会用character_set_server/collation_server，这两个变量可以动态设置，有session/global级别。

在源码中character_set_server/collation_server实际对应一个变量，因为一个collation对应着一个charset，所以源码中只记录CHARSET_INFO结构的collation_server即可。当修改character_set_server，会选择对应charset的默认collation。对于其他同时有charset和collation的变量，源码记录也都是记录collation。

  1. static Sys_var_struct Sys_character_set_server(
  2.        "character_set_server", "The default character set",
  3.        SESSION_VAR(collation_server), NO_CMD_LINE,
  4.        offsetof(CHARSET_INFO, csname), DEFAULT(&default_charset_info),
  5.        NO_MUTEX_GUARD, IN_BINLOG, ON_CHECK(check_charset_not_null));
  7. static Sys_var_struct Sys_collation_server(
  8.        "collation_server", "The server default collation",
  9.        SESSION_VAR(collation_server), NO_CMD_LINE,
  10.        offsetof(CHARSET_INFO, name), DEFAULT(&default_charset_info),
  11.        NO_MUTEX_GUARD, IN_BINLOG, ON_CHECK(check_collation_not_null));

通过下面case可以看到通过设置session中不同的character_set_server使创建database的默认charset和collation不同。

  1. > set character_set_server='utf8';
  3. > create database cs_test1;
  5. > select * from SCHEMATA where SCHEMA_NAME='cs_test1';
  6. +--------------+-------------+----------------------------+------------------------+----------+
  7. | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
  8. +--------------+-------------+----------------------------+------------------------+----------+
  9. | def          | cs_test1    | utf8                       | utf8_general_ci        | NULL     |
  10. +--------------+-------------+----------------------------+------------------------+----------+
  12. > set character_set_server='latin1';
  14. > create database cs_test2;
  16. > select * from SCHEMATA where SCHEMA_NAME='cs_test2';
  17. +--------------+-------------+----------------------------+------------------------+----------+
  18. | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
  19. +--------------+-------------+----------------------------+------------------------+----------+
  20. | def          | cs_test2    | latin1                     | latin1_swedish_ci      | NULL     |
  21. +--------------+-------------+----------------------------+------------------------+----------+

character_set_database/collation_database

该变量值session级别表示当前database的charset/collation，在后面的源码版本中该变量可能修正为只读，不建议修改该值。其global级别变量后面也会移除。

  1. > use cs_test1;
  3. > select @@character_set_database;
  4. +--------------------------+
  5. | @@character_set_database |
  6. +--------------------------+
  7. | utf8                     |
  8. +--------------------------+
  10. > use cs_test2;
  12. > select @@character_set_database;
  13. +--------------------------+
  14. | @@character_set_database |
  15. +--------------------------+
  16. | latin1                   |
  17. +--------------------------+

character_set_client

客户端发送到server的字符串使用的字符集，server会按照该变量值来解析客户端发来的语句。如果指定值和语句实际编码字符集不符就会解析出错，报语法错误或者得到非预期结果，例如下面的两个case。

  1. case1:实际使用utf8编码且包含中文字符，但设置character_set_client为latin1。
  3. > set character_set_client='latin1';
  5. > create table 字符集(c1 varchar(10));
  6. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '­—符集(c1 varchar(10))' at line 1
  8. > set character_set_client='utf8';
  10. > create table 字符集(c1 varchar(10));
  11. Query OK, 0 rows affected (0.14 sec)
  14. case2:实际使用utf8编码且包含中文字符，但设置character_set_client为gbk。
  16. > create database cs_test;
  18. > use cs_test;
  20. > set character_set_client='gbk';
  22. > create table 收费(c1 varchar(10));
  24. > show tables;
  25. +-------------------+
  26. | Tables_in_cs_test |
  27. +-------------------+
  28. | 鏀惰垂            |
  29. +-------------------+
  31. > set character_set_client='utf8';
  33. > create table 收费(c1 varchar(10));
  35. > show tables;
  36. +-------------------+
  37. | Tables_in_cs_test |
  38. +-------------------+
  39. | 收费              |
  40. | 鏀惰垂            |
  41. +-------------------+
  42. 2 rows in set (0.00 sec)

character_set_connection/collation_connection

没有指定字符集的常量字符串使用时的字符集，例如下面两个case。

case1中当设置为utf8_general_ci比较时候忽略大小写，导致’a’=’A’结果为1，如果设置为utf8_bin不忽略大小写，’a’ = ‘A’的结果就是0。

case2中当设置character_set_connection为’latin1’的时候，’你好’ = ‘我好’返回结果为1，如果设置为’utf8’，返回结果就是0。设置为’latin1’返回结果为1的原因是utf8编码的中文字符是无法转换为latin1字符的。这里MySQL就把’你好’和’我好’都转换成了’??’。

case3中character_set_connection的不同导致create table语句中column的实际default value不同。

  1. case1:设置collation_connection是否忽略大小写导致结果不一致。
  3. > set collation_connection=utf8_general_ci;
  5. > select 'a' = 'A';
  6. +-----------+
  7. | 'a' = 'A' |
  8. +-----------+
  9. |         1 |
  10. +-----------+
  12. > set collation_connection=utf8_bin;
  14. > select 'a' = 'A';
  15. +-----------+
  16. | 'a' = 'A' |
  17. +-----------+
  18. |         0 |
  19. +-----------+
  21. case2:设置character_set_connection不同导致结果不一致。
  23. > set character_set_connection='latin1';
  24. Query OK, 0 rows affected (0.00 sec)
  26. > select '你好' = '我好';
  27. +---------------------+
  28. | '你好' = '我好'     |
  29. +---------------------+
  30. |                   1 |
  31. +---------------------+
  32. 1 row in set, 2 warnings (0.00 sec)
  34. > set character_set_connection='utf8';
  35. Query OK, 0 rows affected (0.00 sec)
  37. > select '你好' = '我好';
  38. +---------------------+
  39. | '你好' = '我好'     |
  40. +---------------------+
  41. |                   0 |
  42. +---------------------+
  45. > set character_set_connection='latin1';
  48. > select '你好';
  49. +----+
  50. | ?? |
  51. +----+
  52. | ?? |
  53. +----+
  55. case3:设置character_set_connection导致实际default value不同。
  57. > set character_set_connection='utf8';
  59. > create table cs_t(c1 varchar(10) default '你好')charset=utf8;
  61. > insert into cs_t values();
  63. > select * from cs_t;
  64. +--------+
  65. | c1     |
  66. +--------+
  67. | 你好   |
  68. +--------+
  70. > set character_set_connection='latin1';
  72. > create table cs_t1(c1 varchar(10) default '你好')charset=utf8;
  74. > insert into cs_t1 values();
  76. > select * from cs_t1;
  77. +------+
  78. | c1   |
  79. +------+
  80. | ??   |
  81. +------+

character_set_results

查询结果和错误信息的字符集，server会把返回给客户端的结果转换为对应字符集。例如下面case，当设置character_set_results为’latin1’的时候，会导致返回的中文变成’?’。

  1. > set character_set_results='utf8';
  3. > select '你好';
  4. +--------+
  5. | 你好   |
  6. +--------+
  7. | 你好   |
  8. +--------+
  10. > set character_set_results='latin1';
  12. > select '你好';
  13. +----+
  14. | ?? |
  15. +----+
  16. | ?? |
  17. +----+
  19. > create table cs_test(c1 varchar(10)) charset=utf8;
  21. > insert into cs_test values('你好'),('我好');
  23. > select * from cs_test;
  24. +------+
  25. | c1   |
  26. +------+
  27. | ??   |
  28. | ??   |
  29. +------+
  32. > set character_set_results='utf8';
  35. > select * from cs_test;
  36. +--------+
  37. | c1     |
  38. +--------+
  39. | 你好   |
  40. | 我好   |
  41. +--------+

binlog 中字符集相关缺陷

binlog当前字符集相关实现

对于很多DDL语句，binlog都是直接记录客户端发来的字符串，对于这些语句只要记录语句执行时候的环境变量就可以在备库正确执行。binlog中Query_log_event记录了character_set_client、collation_connection和collation_server，代码如下。记录这三个变量的原因读者可以参考前面各个变量的介绍和case。

  1. int THD::binlog_query(THD::enum_binlog_query_type qtype, const char *query_arg,
  2.                       size_t query_len, bool is_trans, bool direct,
  3.                       bool suppress_use, int errcode)
  4. {
  5.   ...
  6.   case THD::STMT_QUERY_TYPE:
  7.   /*
  8.     The MYSQL_BIN_LOG::write() function will set the STMT_END_F flag and
  9.     flush the pending rows event if necessary.
  10.   */
  11.   {
  12.     Query_log_event qinfo(this, query_arg, query_len, is_trans, direct,
  13.                           suppress_use, errcode);
  14.     /*
  15.       Binlog table maps will be irrelevant after a Query_log_event
  16.       (they are just removed on the slave side) so after the query
  17.       log event is written to the binary log, we pretend that no
  18.       table maps were written.
  19.      */
  20.     int error= mysql_bin_log.write_event(&qinfo);
  21.     binlog_table_maps= 0;
  22.     DBUG_RETURN(error);
  23.   }
  24.   ...
  25. }
  27. Query_log_event::Query_log_event(THD* thd_arg, const char* query_arg,
  28.          size_t query_length, bool using_trans,
  29.          bool immediate, bool suppress_use,
  30.                                  int errcode, bool ignore_cmd_internals)
  31. {
  32.   ...
  33.   int2store(charset, thd_arg->variables.character_set_client->number);
  34.   int2store(charset+2, thd_arg->variables.collation_connection->number);
  35.   int2store(charset+4, thd_arg->variables.collation_server->number);
  36.   ...
  37. }

例如前面创建表cs_t1的case我们可以看到binlog如下。

  1. > set character_set_connection='latin1';
  2. > create table cs_t1(c1 varchar(10) default '你好')charset=utf8;
  4. SET TIMESTAMP=1516089074/*!*/;
  5. /*!\C utf8 *//*!*/;
  6. SET @@session.character_set_client=33,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
  7. create table cs_t1(c1 varchar(10) default '你好')charset=utf8

binlog字符集相关缺陷

对于Query_log_event如果记录的query仅仅是客户端的输入，上面记录字符集变量的方法没有问题。但如果query是server内部生成或者拼接成的，上面直接从thread中获取变量值得方法就可能导致错误。

例如下面的testcase，这里为便于观察和理解case没有使用mysql-test方式，后面有mysql-test。这里主库执行成功，成功创建了表t和视图’收费明细表’，但备库在创建视图的时候却报语法错误。

  1. 用gbk编码写如下sql文本
  2. cs_test.sql
  4. use test;
  5. set @@session.character_set_client=gbk;
  6. set @@session.collation_connection=gbk_chinese_ci;
  7. create table t(c1 int);
  8. create view `收费明细表` as select * from t;
  10. 在主库执行
  11. > source path/cs_test.sql;
  13. > set character_set_results='gbk';
  15. > use test;
  17. > show tables;
  18. +----------------+
  19. | Tables_in_test |
  20. +----------------+
  21. | 收费明细表             |
  22. | t              |
  23. +----------------+
  25. 备库
  27. > show slave status\G
  28. ...
  29. Last_SQL_Errno: 1064
  30. Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`閺鎯板瀭閺勫海绮忕悰鈺? AS select * from t' at line 1' on query. Default database: 'test'. Query: 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `鏀惰垂鏄庣粏琛╜ AS select * from t'
  31. ...

缺陷分析，MySQL记录create view的binlog代码如下。由前面基础知识可以知道对于db、table这些元数据MySQL会先转换为system_charset_info(utf8)。因此在下面代码中append_identifier添加的table name为utf8编码的’收费明细表’，但是views->source.str又是client端原始的gbk编码方式，binlog_query记录的是thd中的character_set_client。即binlog中的query可能是由system_charset_info和character_set_client两种编码方式组成的字符串，记录的是当前character_set_client的值。

  1. sql/sql_view.cc
  2. bool mysql_create_view(THD *thd, TABLE_LIST *views,
  3.                        enum_view_create_mode mode)
  4. {
  5.   ...
  6.   if (views->db && views->db[0] &&
  7.     (thd->db().str == NULL || strcmp(views->db, thd->db().str)))
  8.   {
  9.     append_identifier(thd, &buff, views->db,
  10.                       views->db_length);
  11.                       buff.append('.');
  12.   }
  13.   append_identifier(thd, &buff, views->table_name,
  14.                     views->table_name_length);
  15.   if (lex->view_list.elements)
  16.   {
  17.     List_iterator_fast<LEX_STRING> names(lex->view_list);
  18.     LEX_STRING *name;
  19.     int i;
  21.     for (i= 0; (name= names++); i++)
  22.     {
  23.       buff.append(i ? ", " : "(");
  24.       append_identifier(thd, &buff, name->str, name->length);
  25.     }
  26.     buff.append(')');
  27.   }
  28.   buff.append(STRING_WITH_LEN(" AS "));
  29.   buff.append(views->source.str, views->source.length);
  31.   int errcode= query_error_code(thd, TRUE);
  32.   thd->add_to_binlog_accessed_dbs(views->db);
  33.   if (thd->binlog_query(THD::STMT_QUERY_TYPE,
  34.                         buff.ptr(), buff.length(), FALSE, FALSE, FALSE, errcode))
  35.     res= TRUE;
  36.   ...
  37. }

在MySQL源码中搜索binlog_query还可以找到多处类似的bug，可参考下面的testcase。

  1. --disable_warnings
  2. --source include/master-slave.inc
  3. --enable_warnings
  5. # case1:创建gbk编码中文名视图
  7. create table t(c1 int);
  8. SET @@session.character_set_client=gbk;
  9. set @@session.collation_connection=gbk_chinese_ci;
  10. set @@session.collation_server=utf8_general_ci;
  11. create view `收费明细` as select * from t;
  12. drop view `收费明细`;
  13. show tables;
  15. --sync_slave_with_master
  17. connection slave;
  18. show tables;
  21. connection master;
  22. drop table t;
  24. # case2:创建gbk编码中文名视图，且view body中包含中文
  25. connection master;
  26. SET @@session.character_set_client=gbk;
  27. create table 视图(c1 int);
  28. create view 视图信息 as select * from 视图;
  29. drop view 视图信息;
  31. # case3: drop table 语句会是generated by server.
  32. drop table 视图;
  33. --sync_slave_with_master
  35. # case4:内存表，重启后再次访问时会生成delete from tableName语句.
  36. connection master;
  37. SET @@session.character_set_client=utf8;
  38. set @@session.collation_connection=utf8_general_ci;
  39. set @@session.collation_server=utf8_general_ci;
  40. create table `收费明细表`(c1 int) engine=memory;
  41. create view tv as select * from `收费明细表`;
  42. --connection slave
  43. -- source include/stop_slave.inc
  45. --let $rpl_server_number= 1
  46. --source include/rpl_restart_server.inc
  47. # access memory table after restarting server cause binlog 'delete from tableName'
  48. connection master;
  49. SET @@session.character_set_client=gbk;
  50. set @@session.collation_connection=gbk_chinese_ci;
  51. set @@session.collation_server=utf8_general_ci;
  52. select * from tv;
  56. --connection slave
  57. -- source include/start_slave.inc
  58. connection master;
  59. --sync_slave_with_master
  60. connection slave;
  62. # case5:character_set_client为gbk时中文名的procedure
  64. connection master;
  65. delimiter $$;
  66. create procedure 收费明细()
  67. begin
  68.   select 'hello world';
  69. end $$
  70. delimiter ;$$
  71. drop procedure `收费明细`;
  73. connection master;
  74. SET @@session.character_set_client=utf8;
  75. set @@session.collation_connection=utf8_general_ci;
  76. set @@session.collation_server=utf8_general_ci;
  77. drop view tv;
  78. drop table `收费明细表`;
  79. --sync_slave_with_master
  81. connection slave;
  82. show tables;
  85. # case6: 不同环境变量下create table like/as 表中有中文default value的
  87. set character_set_client = utf8;
  88. set character_set_connection = utf8;
  89. set character_set_database = utf8;
  90. set character_set_results = utf8;
  91. set character_set_server = utf8;
  93. CREATE TABLE `t1` (
  94.   `id` int(11) NOT NULL,
  95.   `orderType` char(6) NOT NULL DEFAULT '已创建',
  96.   PRIMARY KEY (`id`)
  97. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  99. create temporary table `tm` (c1 varchar(10) default '你好');
  101. show create table t1;
  103. ## switch client charset
  104. set character_set_client = latin1;
  105. set character_set_connection = latin1;
  106. set collation_server = utf8_bin;
  107. CREATE TABLE t2 SELECT * FROM t1;
  108. create table t3 like tm;
  109. show create table t2;
  110. show create table t3;
  112. --sync_slave_with_master
  114. connection slave;
  115. show tables;
  116. set character_set_client = utf8;
  117. set character_set_connection = utf8;
  118. set character_set_database = utf8;
  119. set character_set_results = utf8;
  120. set character_set_server = utf8;
  121. show create table t1;
  122. show create table t2;
  123. show create table t3;
  125. connection master;
  126. drop table t1;
  127. drop table t2;
  128. drop table t3;
  130. --sync_slave_with_master
  132. --source include/rpl_end.inc

修复方法

对于create view/create procedure等一个query包含两种编码的可以将system_charset_info的部分转换为thread中的character_set_client。这里的转换需要考虑当前thread中character_set_client不支持utf8字符的问题，当转换失败需要报错，否则主备会不一致。

对于完全由server生成的query，例如delete from和drop table语句，其query实际可以理解为system_charset_info，这种语句可以直接使binlog中character_set_client部分记录system_charset_info，而不是thread中的变量值。

该bug在MariaDB中也存在，可以见MDEV-14249，参考链接中的fix diff或者MariaDB的修复。