MySQLDUMP：关于导出数据表结构collate属性的问题

作者简介：高鹏，笔名八怪。《深入理解MySQL主从原理》图书作者，同时运营个人公众号“MySQL学习”，持续分享遇到的有趣case以及代码解析！

关注这个问题主要是怕使用mysqldump升级的时候，因为做跨版本升级我一般任何业务库的数据导出导入是最稳当的。如果表的字符集遇到问题，这是一个比较严重的问题，容易导致上线后join报错或者效率降低（隐式转换），因此我稍微测试了一下，并且找了一下逻辑。如果这些结论有误请反馈给我，一起再探讨一下。

一、MySQLDUMP的建表语句来源

实际上MySQLDUMP的建表语句和show create table 来源一致，都来自如下：

#9  0x0000000004b57806 in dd::get_implicit_tablespace_options (thd=0xad2eb70, table=0xb365c18, autoextend_size=0x7fffe47ca360) at /newdata/mysql-8.0.23/sql/dd/dd_table.cc:3100
#10 0x000000000388fdaa in store_create_info (thd=0xad2eb70, table_list=0xb353340, packet=0x7fffe47cb0d0, create_info_arg=0x0, show_database=false)
    at /newdata/mysql-8.0.23/sql/sql_show.cc:2245
#11 0x000000000388b69a in mysqld_show_create (thd=0xad2eb70, table_list=0xb353340) at /newdata/mysql-8.0.23/sql/sql_show.cc:1182
#12 0x000000000388923f in Sql_cmd_show_create_table::execute_inner (this=0xb3526e0, thd=0xad2eb70) at /newdata/mysql-8.0.23/sql/sql_show.cc:405
#13 0x0000000003899a5a in Sql_cmd_show_noplan::execute (this=0xb3526e0, thd=0xad2eb70) at /newdata/mysql-8.0.23/sql/sql_show.h:186
#14 0x00000000037fa060 in mysql_execute_command (thd=0xad2eb70, first_level=true) at /newdata/mysql-8.0.23/sql/sql_parse.cc:4407
#15 0x00000000037fbf41 in dispatch_sql_command (thd=0xad2eb70, parser_state=0x7fffe47cda50) at /newdata/mysql-8.0.23/sql/sql_parse.cc:4988

二、collate在5.7和8.0的不同

我们通常导出建表语句的时候（或者show create table）的时候，如果仔细观察一般都只是如下：

5.7：
CREATE TABLE `logdata_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `snap_shot` int(11) DEFAULT NULL,  
  `EVENT_NAME` varchar(128) NOT NULL,  
  `COUNT_STAR` bigint(20) unsigned NOT NULL,  
  `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL,  
  `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL,  
  `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL,  
  `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL,  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2735 DEFAULT CHARSET=utf8;

注意最后的 CHARSET=utf8，但是有些情况却会在后面加上collate 。实际上这是因为utf8的默认字符集为utf8_general_ci，因此不用加，如果设置为utf8_bin，则会在输出中加上 CHARSET=utf8 collate=utf8_bin如下：

因此我们得到一个结论：

• 如果mysqldump或者show create table 如果collate不是默认的那么就会加上collate设置

但是在8.0 中我们知道默认的字符集为utf8mb4 默认的collate为utf8mb4_0900_ai_ci，这是一个特例，我想因为collate改变了需要考虑到兼容性。因此在8.0中我们默认的show create就能看到collate如下：

因此我们增加一个结论：

• 如果是8.0 默认的字符集utf8mb4 ，即便是默认的collate utf8mb4_0900_ai_ci，输出show create依旧会增加collate。

三、代码验证

实际上代码就在store_create_info中可以简单看看

5.7：

8.0：

当然如果想研究下show create table到底怎么打印的这也是一个入口函数。

四、导入库不设置collation_server的情况

这里我们只考虑，关于导入库设置了collation_server的情况，我们最后测试。

• 默认建表方式，不会对表和字段设置字符集等。
• 只设置character_set_server，不显示设置collation_server。

我们通常用mysqldump导出数据然后导入到新版本，那么我们就需要注意一下建表语句的字符集是否正确，总结如下：

• 如果使用默认字符集5.7为utf8，8.0为默认utfmb4，这种情况老的导出数据因为只带了charset=utf8所以collate也是gernal_ci（utf8默认字符集）。那么新建的表是utf8mb4，如果遇到join则会遇到问题。
• 如果5.7设置字符集为utf8mb4，8.0为默认utfmb4，这种情况，因为5.7 utfmb4导出数据的时候只带了charset=uf8mb4，那么导入的时候新建的表collate就是uf8mb4_0900_ai_ci。新建的表也是这样的，因此不会遇到问题，但是实际上collate已经从uf8mb4_general_ci变为了uf8mb4_0900_ai_ci。
• 如果5.7使用默认字符集utf8，8.0字符集设置为utf8，这个不会有任何问题。因为utf8的默认collate哪个版本都是utf8_general_ci。

当然情况还有很多不一一列举，我们可以到只要升级的库和原库字符集保持一致升级则不会遇到这种问题。同时show create database也是如此。而show create database的入口为mysqld_show_create_db函数，里面有类似的逻辑。

测试如下，稍微建立2个表，测试上面的两种情况如下：

5.7:

mysql> show create table testexp \G;
*************************** 1. row ***************************
       Table: testexpCreate Table: 
CREATE TABLE `testexp` (
  `id` int(11) NOT NULL,  
  `name` varchar(20) DEFAULT NULL,  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show create table testexp1 \G;
*************************** 1. row ***************************
       Table: testexp1
Create Table: CREATE TABLE `testexp1` (
  `id` int(11) NOT NULL,  
  `name` varchar(20) DEFAULT NULL,  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

随便做下mysqldump 导出后到 8.0 导入

8.0:
mysql> show create table testexp \G;
*************************** 1. row ***************************
       Table: testexpCreate Table: 
CREATE TABLE `testexp` (
  `id` int NOT NULL,  
  `name` varchar(20) DEFAULT NULL,  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

ERROR:
 No query specified
 
 mysql> show create table testexp1 \G;
 *************************** 1. row ***************************
        Table: testexp1Create Table: 
 CREATE TABLE `testexp1` (
   `id` int NOT NULL,  
   `name` varchar(20) DEFAULT NULL,  
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 1 row in set (0.00 sec)
 
 ERROR:
  No query specified

五、导入库设置了collation_server的情况

下面的测试是如果导入库设置了collation_server，建表的时候如果指定了charset且没有指定collate那么还是默认字符集的默认collate而不会是你设置的collation_server，那么如果是mysqldump导出的数据，因为mysqldump导出的表必定会指定charset，如果指定了charset那么建表的时候就是指定charset的默认collate，因此出现这种现象。如果建表语句不指定charset则根据collation_server和character_set_server进行。

mysql> show variables like '%coll%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| collation_connection | utf8_bin |
| collation_database   | utf8_bin |
| collation_server     | utf8_bin |
+----------------------+----------+
3 rows in set (0.01 sec)

mysql> show variables like '%char%';
+---------------------------+----------------------------------------------+
| Variable_name             | Value                                        |
+---------------------------+----------------------------------------------+
| character_set_client      | utf8                                         |
| character_set_connection  | utf8                                         |
| character_set_database    | utf8                                         |
| character_set_filesystem  | binary                                       |
| character_set_results     | utf8                                         |
| character_set_server      | utf8                                         |
| character_set_system      | utf8                                         |
| character_sets_dir        | /opt/mysql/mysql3306/install/share/charsets/ |
| ft_query_extra_word_chars | OFF                                          |
+---------------------------+----------------------------------------------+
9 rows in set (0.06 sec)
mysql> create table t3(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t3;
+-------+-------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table t4(id int)  CHARSET=utf8 ; （这里不会用到utf8_bin）
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t4;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| t4    | CREATE TABLE `t4` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如果导入到数据库设置collation_server参数的情况，比如设置为utf8mb4_general_ci，就可能导致，导入的数据和你设置的collation_server参数不符合。因为导出一定带有charset选项，是需要注意的。而新建的表可能是utf8mb4_general_ci，但是导入的表是utf8mb4_0900_ai_ci，这样join就会报错。为了避免这种问题最好不要设置collation_server参数，这也是我实际遇到过一个案例。现在测试如下：

mysql> show variables like '%coll%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

mysql> use ooo
Database changed

再次导入刚才的数据

mysql> show create table testexp1;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------+
| testexp1 | CREATE TABLE `testexp1` (
  `id` int NOT NULL,  
  `name` varchar(20) DEFAULT NULL,  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table testexp;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                             |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| testexp | CREATE TABLE `testexp` (
  `id` int NOT NULL,  
  `name` varchar(20) DEFAULT NULL,  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |（这里发现还是utf8mb4_0900_ai_ci，而不是我们设置的 utf8mb4_general_ci）
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

最后提醒大家导入数据后最好看看字段的collate是否符合预期，这点尤为重要。当然关于建表的时候charset和collate的决定，我没有去代码验证，完全来源于测试的结论。后面验证一下。