MySQL里各种排序规则是咋回事，搞懂这些对数据库操作挺重要的

要搞清楚MySQL里的排序规则是咋回事，咱们得先弄明白两个最基本的概念：字符集和排序规则本身，这俩是绑在一起的，不能分开谈。（来源：MySQL官方手册“字符集支持”章节）

字符集是啥？ 简单说，它就是一套密码本，规定了计算机用什么数字代码来代表一个文字符号，在utf8mb4这个现在最常用的字符集里，“中”这个字可能对应着一串特定的二进制数字“11100101 10001001 10111100”，没有字符集，电脑根本不认识你输入的是啥，而排序规则，就是建立在这个密码本之上的“字典的检字法”，一本《新华字典》，所有的汉字（字符集）是固定的，但你可以用拼音顺序查，也可以用偏旁部首查，拼音顺序和偏旁部首，就是两种不同的“排序规则”，它定义了一堆规则，比如这些字符谁应该排在谁前面、大小写字母算不算一样、要不要区分重音符号等等。

（来源：对字符集和排序规则关系的通用数据库知识解释）

那为什么说搞懂这个对数据库操作很重要呢？因为你几乎所有的查询和排序都离不开它,我举几个实际的例子你就明白了。

第一个场景：查数据时，大小写到底算不算一样？ 假设你有一张用户表，用户名字段用的排序规则是utf8mb4_general_ci，这里后缀_ci是关键，意思是“Case Insensitive”，即不区分大小写，这时候，你执行SELECT * FROM users WHERE name = 'john'，那么叫“John”、“JOHN”、“john”的用户都会被找出来，因为在这种规则下，所有大小写字母都被视为等价。 但如果你故意或者不小心，把这个字段的排序规则改成了utf8mb4_bin呢？_bin后缀意思是“Binary”，就是直接比较字符的二进制编码，这时候，大写字母‘J’的编码和小写字母‘j’的编码完全不同，所以WHERE name = 'john'就只会精确匹配“john”，而查不到“John”，如果你没意识到排序规则变了，就会纳闷：“明明数据库里有John这个用户，为啥查不出来？”这就是排序规则在背后捣鬼。

（来源：MySQL官方手册对_ci和_bin后缀的说明）

第二个场景：排序时，顺序为啥跟你想的不一样？ 还是用户表，你想按用户名从A到Z排序，如果你用utf8mb4_general_ci，它可能是一种为了速度优化过的、相对简单的排序规则，它可能会认为某些不同重音符号的字母是“一样”的，a’和‘á’可能被排在一起，不分先后。 但如果你换用utf8mb4_unicode_ci，这个规则更符合Unicode标准，对各种语言的排序支持更精确，它可能会严格区分‘a’和‘á’，让它们有明确的先后顺序，同样一批数据，用不同的排序规则ORDER BY，出来的顺序可能天差地别，在做国际化应用，需要正确显示法语、德语、土耳其语等有特殊字符的语言时，选对排序规则至关重要,否则用户会觉得你的App排序乱七八糟。

（来源：社区中关于general_ci与unicode_ci区别的广泛讨论和官方文档的间接说明）

第三个场景：建索引和性能问题。 排序规则直接影响字符串比较的方式，而索引的核心就是快速比较和定位，如果一个查询的WHERE条件或者JOIN条件涉及字符串比较，MySQL会使用列上定义的排序规则，如果排序规则很复杂（比如unicode_ci），比较起来可能就比简单的规则（如general_ci）稍微慢一点点，尤其是在海量数据下，更重要的是，如果你在联表查询时，两个表的字符串字段使用了不同的排序规则，MySQL就无法使用为连接准备的索引了，它可能得被迫进行全表扫描然后转换字符集再比较，性能会急剧下降，MySQL通常会报一个类似“Illegal mix of collations”的警告，保持数据库里相关表字段排序规则的一致性,是保证查询性能的一个小诀窍。

（来源：数据库性能优化相关实践总结）

MySQL里常见的排序规则有哪些呢？除了刚才提到的：

1. _ci结尾的：不区分大小写，最常用，比如utf8mb4_general_ci（较老，速度快但规则简单）和utf8mb4_unicode_ci（较新，更标准更准确）。
2. _cs结尾的：区分大小写（Case Sensitive），比如utf8mb4_general_cs,用的比较少。
3. _bin结尾的：直接按二进制编码值比较，区分大小写，且对所有字符严格排序，当你需要绝对精确的、按底层编码顺序的比较时用它。

怎么查看和修改排序规则呢？你可以用SHOW CREATE TABLE 表名;这个命令，它会显示建表语句，里面就能看到每个字段的字符集和排序规则，如果你觉得不合适，可以在建表时指定，比如CREATE TABLE my_table (name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci);，也可以修改现有字段ALTER TABLE my_table MODIFY name VARCHAR(100) COLLATE utf8mb4_bin;，但修改要谨慎,因为可能会影响现有查询和索引。

排序规则不是高深的理论，它就是数据库处理文字排序和比较的一套接地气的规则，你把它想象成字典的检字法，平时可能感觉不到它的存在，但一旦用错地方或者发生冲突，就会带来查不到数据、排序混乱、性能变慢各种怪问题，所以在设计表结构的时候，根据你的业务需求（是否需要区分大小写？是否要支持多语言？）选对排序规则，并且在数据库内部保持一致性,就能避免很多后期的麻烦。

（综合自MySQL官方文档、数据库管理实践经验以及常见问题排查案例）