记录 Mysql 8.0的坑

下午给其中一个网站搬家到别的机器，打包转移好网站数据和数据库后，发现存在问题。
打开Debug参数看了下数据库连接失败，确认数据库账户和密码配置没问题后，进数据库看了下竟然是空的。
再次尝试手动把数据库导入，依旧存在问题。
后导入了许久之前的数据库，数据正常。
查看了下是现如今备份数据库编码和之前相比存在差异问题。
想起来前段时间升级了mysql升级到了8.0版本。
这里查了下两种编码的资料
    

首先从utf8mb4_0900_ai_ci这个诡异的名字说起。

Unicode编码的诞生，是为了解决之前各国的计算机文字编码自成一体的问题。不同国家采用不同的编码，自己用还算正常，但是跨文化交流必然会出问题，更无法解决“在同一篇文档里又要显示中文又要显示韩文还要显示日文”之类的问题。

有了Unicode，地球上所有的文字都有独一无二的编码（Code Point，也就是为它分配的码值，或者说“逻辑代号”），前述问题就解决了。

但是Unicode（有个相关的名字是UCS，Universal Coded Character Set，二者基本等价）只确定了码值，或者说，只分配了逻辑代号。至于这些逻辑代号在实际使用中如何存储，如何传输，那是另一个问题。而UTF-8，就是解决存储和传输等问题的“实际方案”。

实际上，UTF的全名是Unicode Transformation Format，也就是“Unicode变换格式”。这里的“变换”，基本可以类比为：要告诉别人明天早上九点来开会，到底是发邮件呢，还是打电话呢，还是写纸条呢，还是直接去敲门打招呼呢？。

所以，Unicode的变换格式不只UTF-8一种，还有UTF-16、UTF-32等等。UTF-8使用比较普遍，因为它是变长编码，如果只传输ASCII字符，则每个字符只需要一个字节。因此，如果数据中包含大量的ASCII字符，那么UTF-8可以节省很多存储空间。

老一点的程序员大概都知道UTF-8，在MySQL中写作utf8，没有横线。如果要用MySQL存储多种语言的字符，那么把字符集（character set）设定为utf8是合适的选择。注意，MySQL中必须指定utf8，而不是Unicode。因为Unicode只是逻辑规范，utf8才是具体存储和传输的格式。

那么，utf8mb4_0900_ai_ci什么意思呢？

我们分部分来看这个名字，先从开头看起。

utf8mb4，这个名字许多人大概熟悉。如今emoji表情已经大量使用，但MySQL之前的的字符集（character set）是utf8（更准确的名字是utf8mb3，一个字符最多使用3个字节来存储），只能存储编码值从0x000到0xFFFF之间的字符。

然而，emoji表情字符的码值超过了0xFFFF，按照UTF-8规范，存储时需要用4个字节。正因为如此，MySQL才提供了utf8mb4的字符集。如果把数据库表的字符集设定为utf8mb4，就可以正常存储包含表情字符的文本了。

中间的0900，它对应的是Unicode 9.0的规范。要知道，Unicode规范是在不断更新的，每次更新既包括扩充，也包括修正。比如6.0版新加入了222个中日韩统一表义字符（CJK Unified Ideographs），7.0版加入了俄国货币卢布的符号等等。

如果支持新的Unicode规范，就可以直接享受好处，像对待普通字符那样对待这些新字符，当然是好事。

以前的MySQL虽然也会跟随Unicode的更新，但速度太慢了。MySQL 5.7的第一个发行版MySQL 5.7.1是2013年4月23日面世的，它包含的最新的Unicode规范是Unicode 5.2，发布于2009年10月。即便是2020年1月13日发布的MySQL 5.7.29，仍然是这样。

然而Unicode规范早已升级了很多版，即便是9.0版本，也发布于2016年6月，过去了好多年了。到目前为止，最新的版本已经到了12.1，发布于2019年5月。所以从5.2更新到9.0，看起来是一大进步，其实也只是补课而已。

Unicode在不断更新，来源：维基百科

最后两部分_ai_ci，ai表示accent insensitivity，也就是“不区分音调”，而ci表示case insensitivity，也就是“不区分大小写”。

所以，utf8mb4_0900_ai_ci到底是个什么东西呢？其实，它是个collation。

说起“字符集”，许多人想当然认为，给每个字符分配了一个编码，并且能存储、能传输，这就够了。其实这当然不够，我们不但需要给每个字符分配编码，让它们能存储、能传输，还需要定义一套关系来组织它们，找到它们之间的联系。这套关系的定义，就是collation。

collation定义了哪个字符和哪个字符是“等价”的。所以如果指定“不区分大小写”，那么a和A，e和E就是等价的，这样查找时就会方便很多。但这还不够，世界上的文字很多，所以才会有“不区分音调”的要求，这时候e、ē、é、ě、è就是等价的，那么假设我们要进行拼音查找，只要按e去找就可以全部列出来，很方便。甚至，它们也和ê、ë也是等价的，这样就更方便了。

collation也定义了字符的排序规则，如果按照“字符顺序（而不是简单的‘字母顺序’）”来排序，哪个字符应当排在哪个字符前面。所以，尽管“啊”、“副”、“德”三个字的拼音开头分别为A、F、D，但直接选定collation为utf8mb4，它们并不会按照“啊”、“德”、“副”的顺序排序，而是会排成“副”、“啊”、“德”。如果你希望把中文字符按照拼音（英文字母）来排序，指定使用gb18030_chinese_ci作为collation就可以了。

当然，要补充的是，collation依赖于字符集（character set），所以把gb18030_chinese_ci作为collation，就要求字符集是gb18030，而不能是utf8mb4。

这也很好理解，字符集定义了可以使用的字符，对应的collation定义了字符之间的关系。如果collation不依赖于字符集，那么很可能出现“有些字符没有关系定义，不知如何判断等价和顺序”的问题。

MySQL 8.0之后，默认collation不再像之前版本一样是是utf8mb4_general_ci，而是统一更新成了utf8mb4_0900_ai_ci，所以之前建的各种数据表，它们的collation仍然是utf8mb4_general_ci（这个名字够自负），而新建的表是utf8mb4_0900_ai_ci。如果，恰好遇到包含字符串相等或者大小比较的联表查询语句，而关联的表又使用了不同的collation，MySQL就无法决策到底应当使用哪个，就会报错。

最后用alter table table_name collate utf8mb4_0900_ai_ci显式统一所有表的collation，问题就解决了。