【MySQL篇】使用mysqldump导入报错Unknown collation: ‘utf8mb4_0900_ai

💫《博主介绍》：✨又是一天没白过，我是奈斯，从事IT领域✨

💫《擅长领域》：✌️擅长阿里云AnalyticDB for MySQL(分布式数据仓库)、Oracle、MySQL、Linux、prometheus监控；并对SQLserver、NoSQL(MongoDB)有了解✌️

💖💖💖大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注💖💖💖

如标题所示本篇文章是记录一下使用mysqldump导出的SQL文件进行导入时的报错Unknown collation: 'utf8mb4_0900_ai_ci'。众所周知mysqldump是MySQL的逻辑迁移工具，它能够将数据库的内容以 SQL语句的形式导出为文件，为数据库的迁移、备份和恢复工作提供了极大的便利。

但mysqldump导入时很鸡肋真的让人有些哭笑不得，就是使用mysql命令进行导入的，所以没有办法看到每张表的导入情况，也没有办法指定某个表、某个库的导入，导入一个mysqldump导出的sql文件，然后它默默地在那边工作，你除了等待和祈祷，只能看看后台这个导入进程还是否存在，几乎什么都做不了。虽然mysqldump导入有些“鸡肋”，但我们也不要太过苛求。毕竟它为我们提供了基本的数据库备份和恢复功能，这已经足够我们在很多场景下使用了。如果真的需要更高级的功能，那也不妨考虑一下那些收费的商业软件，它们或许能给你带来更好的体验。

那么回到正题这个报错当时是研发人员需要将MySQL8.0中的某个生产库的全量数据导入到MySQL5.7时抛出的错误，属于降版本导入数据。

其实这个需求挺奇葩的，明明已经是高版本MySQL8.0了，MySQL8.0不管是性能上还是新功能上都要比MySQL5.7强，为啥还需要降版本导入到MySQL5.7中呢？原因是因为目前其他项目上有用MySQL8.0的，还有尝试用最新的MySQL8.1等版本的，但当前需要协助导入数据的项目上还用的是MySQL5.7，这个项目要拉其他项目上的数据来进行测试，如果不同版本上的数据都单独创建一个对应版本实例的话，使用MySQL自带的federated功能联调就比较麻烦，并且最要命的是使用federated功能只支持select、insert、update、delete、truncate和索引，不支持ALTER TABLE或任何直接影响表结构的数据定义语句（执行ALTER TABLE报错：ERROR 1031 (HY000): Table storage engine for 'liu_mysqloltp_ywcs_tb88' doesn't have this option），所以最好的办法就是统一将不同版本上的数据统一导入到一个版本中。大家都知道使用mysqldump升版本的导入导出没问题，也就是支持向上兼容；但mysqldump导出的SQL文件不支持降版本导入，所以就需要对导出的SQL文件进行一下处理。

问题描述：

在MySQL8.0环境中导出某个生产库的全部对象，执行如下mysqldump导出语句：

[root@mgr1 ~]# mysqldump -uroot -p123456 --set-gtid-purged=OFF --single-transaction --master-data=2 --flush-logs --routines --events --databases liudbywcs --log-error=mysqldump_liudbywcs_3306_err.log  --skip-add-locks  --socket=/liu_data/mysql8.0/data/3306/liu.sock   > mysqldump_liudbywcs_3306.sql

然后将MySQL8.0中mysqldump导出的SQL文件，通过mysql客户端命令导入到MySQL5.7环境中：

[root@ha1 ~]# mysql -uroot -p123456 --socket=/liu_data/mysql5.7/data/3306/liu.sock --force < mysqldump_liudbywcs_3306.sql

在MySQL5.7环境中通过mysql客户端命令导入时报错信息如下：

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1273 (HY000) at line 28: Unknown collation: 'utf8mb4_0900_ai_ci'
ERROR 1253 (42000) at line 28: COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'
ERROR 1049 (42000) at line 30: Unknown database 'liudbywcs'
ERROR 1046 (3D000) at line 36: No database selected
ERROR 1046 (3D000) at line 39: No database selected
ERROR 1046 (3D000) at line 53: No database selected
ERROR 1046 (3D000) at line 54: No database selected
...............

解决办法：

分析上面两个报错Unknown collation: 'utf8mb4_0900_ai_ci'和COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'。

报错一：Unknown collation: 'utf8mb4_0900_ai_ci'。在MySQL5.7中不支持这个字符集排序规则（collation），所以需要替换成MySQL5.7支持的utf8mb4_general_ci字符集排序规则（collation）。
报错二：COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'。utf8_general_ci 不适用于 utf8mb4 字符集，应该使用 utf8mb4_general_ci 或 utf8mb4_unicode_ci 排序规则来避免该错误。

如果MySQL5.7的默认字符集是utf8，那么将导出来的SQL文件的字符集和排序规则修改如下：
sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' SQL文件
sed -i 's/utf8mb4_general_ci/utf8_general_ci/g' SQL文件
sed -i 's/utf8mb4/utf8/g' SQL文件
如果MySQL5.7的默认字符集是utf8mb4，那么将导出来的SQL文件的字符集和排序规则修改如下：
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' SQL文件

扩展（MySQL的字符集和排序规则）：

字符集：

字符集（Character Set）在 MySQL 中定义了如何将字符映射到字节。它是决定如何存储和表示文本数据的基本规范。字符集与排序规则（Collation）密切相关，但它们的作用不同。字符集负责处理字符的存储和传输，而排序规则则定义了字符的比较和排序方式。

MySQL5.7支持的字符集和字符集默认的排序规则，可以参考MySQL5.7的官方文档学习，支持41种字符集：MySQL :: MySQL 5.7 Reference Manual :: 10.2 Character Sets and Collations in MySQL

MySQL8.0支持的排序规则，达272个之多，详细可以参考MySQL8.0的官方文档学习：MySQL :: MySQL 5.7 Reference Manual :: 10.2 Character Sets and Collations in MySQL

那么下面梳理一下排序规则的重要知识点：



一、字符集的作用：
1、字符的存储和编码：字符集决定了字符如何转换成字节序列进行存储。不同的字符集采用不同的编码方式来表示字符。例如：

UTF-8：将字符映射到1到4个字节之间。它支持全球绝大多数语言，并且是网络传输中常用的编码。
Latin1（ISO-8859-1）：每个字符用1个字节表示，通常用于西欧语言。
UTF-16：通常使用2个字节或4个字节来表示字符，适合用于表示亚洲字符集。

2、支持不同语言和符号：

字符集允许数据库支持多种语言的字符。例如，UTF-8字符集可以存储几乎所有语言的字符，包括汉字、阿拉伯字母、日文字符等。
选择合适的字符集对于支持多语言的应用程序至关重要。

3、数据存储和读取的一致性：

字符集确保数据在存储和读取时的一致性。如果数据库和应用程序使用不匹配的字符集，可能会导致乱码或数据损坏。

4、跨平台数据交换：

使用标准化字符集（如 UTF-8）有助于不同系统之间的数据交换。这样，无论数据如何迁移，字符都能正确显示。

5、节省存储空间：

不同的字符集在存储上有所不同。比如 latin1 每个字符占用 1 字节，而 utf8 占用的字节数则与字符内容的复杂性有关，可能是 1 到 4 个字节。在存储英文等简单字符时，使用 latin1 可以节省存储空间；但如果有多语言需求，使用 utf8 或 utf8mb4 可以更好地支持国际化。



二、常见的字符集：

latin1（ISO-8859-1）：这是最常见的西欧语言字符集之一，每个字符占用 1 个字节。适合处理简单的西欧语言字符（如英语、西班牙语、法语等）。
utf8：这个字符集可以表示全球几乎所有的字符，支持从 1 到 3 字节的编码。对于大多数现代应用，尤其是需要支持多语言的系统，utf8 是非常常用的字符集。
utf8mb4：它是 utf8 的超集，支持更多的字符，例如 emoji 表情和某些少数民族的字符。utf8mb4 使用 1 到 4 字节存储字符，能够存储所有 Unicode 字符。
utf16：这个字符集使用 2 或 4 个字节表示字符，适用于需要表示大量不同语言字符的情况。
gbk 和 gb2312：这些是用于中文字符的字符集，在中文环境中较为常见。gb2312 是较旧的字符集，支持简体中文，而 gbk 是对 gb2312 的扩展，支持更多的中文字符。
big5：这个字符集用于繁体中文，主要在台湾和香港地区使用。


三、字符集与排序规则的关系
字符集和排序规则是密切相关的。在 MySQL 中，字符集定义了如何存储和表示字符，而排序规则定义了如何比较和排序字符。每个字符集通常会有一个默认的排序规则。例如：

utf8 字符集的默认排序规则是 utf8_general_ci，其中 ci 代表“不区分大小写”。
utf8mb4 字符集的默认排序规则是 utf8mb4_general_ci，也是不区分大小写。需要注意MySQL5.7中utf8mb4默认排序规则是 utf8mb4_general_ci，而在MySQL8.0中默认排序规则则成了 utf8mb4_0900_ai_ci。

可以在创建数据库、表、列时，单独指定字符集和排序规则。排序规则影响的是比较、匹配、排序等操作，而字符集影响的是字符的存储。

字符集的排序规则：

排序规则（Collation）在 MySQL 中的作用主要是决定如何对字符进行比较和排序。并且排序规则与字符集密切相关，但排序规则的重点在于字符的比较方式，尤其是如何在查询中对字符进行排序、查找和匹配。

MySQL5.7支持的排序规则，达222个之多，详细可以参考MySQL5.7的官方文档学习：

MySQL :: MySQL 5.7 Reference Manual :: 10.10 Supported Character Sets and Collations

MySQL8.0支持的排序规则，达272个之多，详细可以参考MySQL8.0的官方文档学习：MySQL :: MySQL 8.0 Reference Manual :: 12.10 Supported Character Sets and Collations

那么下面梳理一下排序规则的重要知识点：

一、排序规则的作用：
1、字符比较：排序规则定义了字符在查询时如何进行比较。对于同一个字符集，不同的排序规则可以影响字符比较的行为，尤其在大小写、重音符号、语言等方面的处理。例如：

utf8_general_ci： ci 代表不区分大小写（Case Insensitive），即在比较时，A 和 a 被认为是相等的。
utf8_bin： bin 代表二进制比较，即字符的二进制值完全一致才认为相等，这意味着 A 和 a 是不同的字符。

2、字符排序：排序规则定义了字符如何排序。例如，某些语言的字符可能会根据特定的语言规则进行排序，而不是按照字母的顺序排列。例如：

utf8_unicode_ci：使用 Unicode 字符集进行排序和比较，遵循 Unicode 排序规则，这通常会考虑语言差异和字符的实际顺序。
utf8_general_ci：这是一个比较简单的排序规则，适用于大多数情况下的字符排序，但可能不会考虑所有语言的细微差别。

3、语言和文化敏感性：排序规则会根据特定语言的语法进行排序。例如，德语中的 ß 字符在某些排序规则中可能会与 ss 组合字符视为相等。例如：

utf8_spanish_ci：这是西班牙语的排序规则，它会遵循西班牙语的特定排序规则来比较字符。

4、大小写敏感性：排序规则决定了字符比较时是否区分大小写。

ci：不区分大小写，例如 a 和 A 被认为相等。
cs：区分大小写，例如 a 和 A 被认为不同。

5、重音符号敏感性：一些排序规则区分带有重音符号的字符与没有重音符号的字符，另一些则不区分。

ai：不区分重音符号，例如 é 和 e 被认为相等。
as：区分重音符号，例如 é 和 e 被认为不同。

二、排序规则的常见类型
1、区分大小写（Case Sensitivity）：

ci（Case Insensitive，不区分大小写）：在比较时， A 和 a 被认为相同。
cs（Case Sensitive，区分大小写）：在比较时， A 和 a 被认为不同。

2、区分重音符号（Accent Sensitivity）：

ai（Accent Insensitive，不区分重音符号）：在比较时， é 和 e 被认为相同。
as（Accent Sensitive，区分重音符号）：在比较时， é 和 e 被认为不同。

3、二进制比较：

bin（Binary，二进制比较）：对字符进行字节级别的比较， A 和 a 被认为不同。