ERROR 1273 (HY000): Unknown collation: ‘utf8mb4_0900_ai_ci‘ mysql 8.x 数据导入导出低版本 5.x mysql数据库-奇葩测试

偏执的太偏执、 2024-03-31 17:32 55阅读 0赞

#### 目录 ####

*  序言
 *  1. 排除数据库 - 表 - 字段等编码和排序规则的问题
 *   *  重点理解mysql数据库的编码和排序规则的优先级
 *  参考链接：

## 序言 ##

原因是数据库排序规则的问题，MySQL 5.7使用的默认为`utf8mb4_unicode_ci`，但是从MySQL8.0开始使用的已经改成`utf8mb4_0900_ai_ci`  
手动修改sql文件的内容，就可以解决

测试环境：

mysql 8.x ：Windows10系统
    mysql 5.7 ： Centos 7 系统

一开始笔者是将 mysql 8.x 版本的数据库迁移到 mysql 5.7 版本的数据库，后来问题就出现了（`1366 - Incorrect string value: '\xF0\x9D\x9C\x83\xE8\xA1...' for column 'xxxx' at row 2`），  
查找资料，手动改动出现问题的表的编码从utf8mb4 改为 utf8 编码，然后用mysqldump 命令导出 mysql 5.7 版本的指定数据库的数据为 `.sql` 文件，然后回到 mysql 8.x 版本的数据库环境进行测试，过程曲折，解决问题后不明所以。

## 1. 排除数据库 - 表 - 字段等编码和排序规则的问题 ##

### 重点理解mysql数据库的编码和排序规则的优先级 ###

1. 列(字段)的编码 优于 > 表的编码 优于 > 数据库的编码
    2. 列(字段)的排序规则 优于 > 表的排序规则 优于 > 数据库的排序规则 （即 /etc/my.cnf 配置文件的排序规则配置，特别是init-connect='SET NAMES utf8'）

例如：

test varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

那么 该字段 test 的编码被配置为 `utf8mb4` 排序规则(校对规则)为 `utf8mb4_unicode_ci`

例子表 1编码：

DROP TABLE IF EXISTS `mytable`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `mytable` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `test` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
      `test1` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    
    ) ENGINE=InnoDB AUTO_INCREMENT=0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

如果指定了`CHARACTER SET X`和`COLLATE Y`，那么采用`CHARACTER SET X`和`COLLATE Y`。 如果指定了`CHARACTER SET X`而没有指定`COLLATE Y`，那么采用`CHARACTER SET X`和`CHARACTER SET X`的默认校对规则。否则，采用表字符集和服务器校对规则，层层后退匹配有配置的校对规则（排序规则）。

例子表 2

查看系统 mysql的编码配置命令：

show variables like 'character%';

优先级问题大致就先理解到这里，笔者之前遇到过一个如下配置：

那么默认情况下，列的编码和排序规则要优先于表和数据库的编码和排序规则，在一开始笔者是按照如上 例子表 1 那般类似的配置操作，在录入某个数据时，会报错  
`ERROR 1366 (HY000): Incorrect string value: '\xF0\x9D\x9C\x83\xE8\xA1...' for column 'test1' at row 2` (字段**test1**是虚构模拟的)

但是通过输入命令：

mysql>
    `ALTER TABLE  mytable  DEFAULT CHARACTER SET utf8;`

> /etc/my.cnf配置文件内的 `init-connect='SET NAMES utf8'` 相当于 `set names utf8` 命令  
> `set names` 是设置上面三个内置系统变量的快捷键命令。  
> 1.character\_set\_client 是指客户端发送过来的语句的编码;  
> 2.character\_set\_connection 是指 mysqld 收到客户端的语句后，要转换到的编码；  
> 3.character\_set\_results 是指 server 执行语句后，返回给客户端的数据的编码。

将表的编码从 `utfmb4` 更改为 `utf8` ，并不配置其表的排序规则，优先使用表内字段其配置好的排序规则和编码，覆盖表的编码和排序规则，可是既然是覆盖了其表的编码和排序规则，为什么会出现这种报错显示，如果依照我的对utf8 和 utf8mb4的理解，utf8mb4是可以兼容utf8编码的，在测试数据时，一直是笔者前面提到的其中某一个数据项内的一个字段数据内容有问题，由于内容过多看不出是哪个符号文字，导致的问题，不过既然如此，逻辑理解是 /etc/my.cnf 配置了 init-connect=‘SET NAMES utf8’ 那么数据从客户端传回 utf8 编码的数据（`character_set_client utf8mb4` ——> `character_set_connection urf8mb4`），在数据库内 (`character_set_server` ——> `character_set_database`() ——> 表编码和排序规则 ——> 字段编码和排序规则）括号内被一个一个的被覆盖掉，最终是字段的编码和排序规则起作用。

注意：即使在 `/etc/my.cnf` 配置文件里根本就没有配置`character_set_database`，重启mysql后`character_set_database`变量值也会和`character_set_server`保持一致，故只需要配置`character_set_server`就行了

--------------------

后面查看是编码问题，但是其实我是觉得例子表 1 和例子表2 是一样的，因为 `utf8mb4` 编码是兼容`utf8` 编码的，看别人的说是拉丁编码（ latin-1）问题，所以需要手动命令修改表的编码，就可以解决，但是笔者已经是配置 `urf8mb4` 编码了，还能有问题？即使有问题，配置回`utf8`编码，也可以，如下命令：

ALTER TABLE `test_table_name` DEFAULT CHARACTER SET utf8;

笔者将其改回utf8编码，结果能够解决问题，问题解决，完全不懂这什么逻辑！ 表编码 utf8mb4 改为 utf8 ，问题就被解决了。

查看了下windows 10 环境下，sql 文件内容有点复杂，多了挺多细节配置，对比了一下，Centos 环境下 mysqldump 命令导出的 sql 文件的内容，会精简了许多，而且很奇怪，用 Window10 系统导出的sql文件，会比较容易出现不明所以的问题，当然Linux 系统的导出的sql文件也有可能，因为笔者，在Windows 10 系统环境下（mysql 8.x)导入Linux 系统（mysql 5.7)导出的整个数据库的 sql文件，测试没问题，导入成功，但是如果是在这个sql 文件内，把那个出问题的表的结构和数据，在 Navicat 工具内的命令行导入，就会出现问题（`1366 - Incorrect string value: '\xF0\x9D\x9C\x83\xE8\xA1...' for column 'xxxx' at row 2`），但是如果是使用 Linux 系统下，mysqldump 命令导出指定的数据库中的那个出现问题的表，（注意这里是导出指定的表），那么在 Windows10 环境下（mysql 8.x) 不会出现这个问题，真是越测试越纳闷。拿整个mysqldump出来的数据库sql文件，导入到Windows10下的mysql 8.x 数据库就不会出现导入失败的问题，然而如果拿这个sql文件里面的那个问题表结构和数据，命令行手动输入，就会出现导入失败的问题，之后如果是用mysqldump命令导出指定表的sql表文件导入数据到mysql 8.x数据库，就不会出问题，真是古怪。  
看起来逻辑挺乱的，但是确确实实是结果很古怪，最后笔者个人认为，一般而言Linux mysqldump命令导出的 sql 文件，还是比较不会容易出错的，Window 系统导出的 sql 文件，感觉从 Windows 那边弄过来的 Sql文件复杂化且不说，兼容问题也容易触发。

个人建议：

如果是使用 nacicat 软件创建mysql 数据库的，最好是使用新建查询，使用 mysql 命令来创建数据库，否则用可视化创建创建mysql数据库，感觉会有隐患。

## 参考链接： ##

[charset是character set的区别][charset_character set]

[charset_character set]: https://www.jianshu.com/p/5f79f4de6311