现象:部署成功但是存在数据缺失。调查发现主要原因是数据库字符集COLLATE 不同导致。
一般情况下,我们认为字符集保持一致即可,事实上COLLATE也很重要。COLLATE是用来指定字符集内字符之间的比较规则的关键词。它主要有以下作用:
- 确定字符等价性:COLLATE定义了在同一字符集内哪些字符是等价的,这在处理大小写不敏感的语言或特殊字符时尤为重要。
- 影响排序顺序:COLLATE规定了字符之间的大小关系,这直接影响到
ORDER BY
语句的结果。 - 应用于列和数据库级别:在MySQL中,可以在列级别或数据库级别指定COLLATE,以确定字符串数据的排序和比较方式。
- 影响字符比较操作:不仅排序,COLLATE还影响到
WHERE
子句中的比较操作,如=
、<
、>
等。 - 语言和文化敏感性:不同的COLLATE可能会根据语言和文化的不同而有不同的排序规则,例如,一些COLLATE会考虑字母的重音符号。
- 性能考虑:不同的COLLATE可能在性能上有所差异,一般来说,更复杂的排序规则可能会牺牲一些性能。
- 兼容性:在选择COLLATE时,还需要考虑与其他系统的兼容性,尤其是在多语言环境中。
接下来回到我们的问题,一般情况下,使用相同collate不会有问题,但是在mysql5.7和mysql8中,有如下不同:
- 在MySQL 5.7中,服务器的默认字符集(server character set)和排序规则(server collation)是latin1和latin1_swedish_ci。
- 而在MySQL 8中,它们被更改为utf8mb4和utf8mb4_0900_ai_ci。这一变化意味着MySQL 8现在默认使用更现代、更通用的字符集和排序规则,这对于支持更多种类的字符(如表情符号)和提供更好的排序性能是有益的。
了解这点,再查看我们的sql发现存在大量字段COLLATE为utf8mb4_0900_ai_ci,而我们部署时使用的数据库版本为5.7,这导致这些表创建sql将无法正确执行,造成了表缺失。
总的来说,从MySQL 数据还原时都应该注意字符集和COOLATE的问题。