collations默认排序规则的坑

问题现象

使用mysqldump从Mysql 5.7迁移到Mysql 8.0。

迁移前：5.7上字符集为utf8mb4，排序规则为utf8mb4_general_ci

迁移后： 8.0上字符集为utf8mb4，排序规则自动变成了utf8mb4_0900_ai_ci（期望general_ci）

不同排序规则关联会产生报错

原因说明：

https://dev.mysql.com/doc/refman/8.0/en/charset-mysql.html

1、每个字符集有一个默认的排序规则，在mysql5.7,utf8mb4的默认排序规则为utf8mb4_general_ci，在mysql8.0，utf8mb4的默认排序规则为utf8mb4_0900_ai_ci。

2、在建表的时候若没有显式指定排序规则，则自动使用字符集的默认排序规则。

建表测试

测试环境： mysql 8.0
### DEFAULT CHARSET=utf8mb4


DB级别字符集设置
CREATE DATABASE `bakdata` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */


执行语句：
CREATE TABLE `wjf_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`app_name` varchar(64) NOT NULL COMMENT '执行器AppName'
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;


实际生效语句
CREATE TABLE `wjf_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`app_name` varchar(64) NOT NULL COMMENT '执行器AppName'
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


执行语句：
CREATE TABLE `wjf_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`app_name` varchar(64) NOT NULL COMMENT '执行器AppName'
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB ;


实际生效语句：
CREATE TABLE `wjf_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`app_name` varchar(64) NOT NULL COMMENT '执行器AppName'
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

执行语句：
CREATE TABLE `wjf_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `app_name` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT '执行器AppName',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT COLLATE=utf8_bin;


实际生效语句：
CREATE TABLE `wjf_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `app_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '执行器AppName'
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin

测试结论：

表级别字符集生效规则：

CHARSET	COLLATE	实际生效	说明
无	无	库级别设置	库级别设置生效
有	无	COLLATE为COLLATE的默认排序规则	库级别不生效，特别注意，mysql 8 中utf8mb4的默认collate为utf8mb4_0900_ai_ci
无	有	CHARSET为COLLATE对应的字符集	库级别不生效，每个collate存在唯一对应的charset
有	有	表级别设置

字符集修改

alter table_name CONVERT TO CHARACTER set utf8mb4 collate utf8mb4_general_ci;

1、该语句会同时修改表级别的设置和已存在的数据。

2、该操作会锁表（排他MDL锁），阻塞DML。