问题现象
使用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。