关注这个问题主要是怕使用mysqldump升级字符集遇到问题，这是一个比较严重的问题，因此我稍微测试和看了带入口函数。

一、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
#16 0x00000000037f2543 in dispatch_command (thd=0xad2eb70, com_data=0x7fffe47ceb00, command=COM_QUERY) at /newdata/mysql-8.0.23/sql/sql_parse.cc:1836
#17 0x00000000037f095e in do_command (thd=0xad2eb70) at /newdata/mysql-8.0.23/sql/sql_parse.cc:1320
#18 0x00000000039c5c91 in handle_connection (arg=0xab58540) at /newdata/mysql-8.0.23/sql/conn_handler/connection_handler_per_thread.cc:301
#19 0x000000000562cd84 in pfs_spawn_thread (arg=0xaa3a100) at /newdata/mysql-8.0.23/storage/perfschema/pfs.cc:2900
#20 0x00007ffff7bc6ea5 in start_thread () from /lib64/libpthread.so.0
#21 0x00007ffff5e388dd in clone () from /lib64/libc.so.6

二、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如下：

image.png

因此我们得到一个结论：

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

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

image.png

因此我们增加一个结论：

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

三、代码验证

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

5.7：

image.png

8.0：

image.png

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

四、为什么注意这个地方

这里我们只考虑

• 默认建表方式，不会对表和字段设置字符集等。
• 只设置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: testexp
Create 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: testexp
Create 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: testexp1
Create 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，建表的时候如果指定了charset且没有指定collate那么还是默认字符集的默认collate而不会是你设置的collation_server，这是应该mysqldump导出的表必定会指定charset，如果指定了charset那么建表的时候就是指定charset的默认collate，因此出现这种现象。如果不指定charset则根据collation_server和character_set_server进行。

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)

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)

这种导入数据库设置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）
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>