MySQL字符集和排序规则详解

概念

• 字符集的内容包含：字符集（character set）和排序规则（collation rule）；

• 每种字符集可对应一到多个排序规则，每种排序规则对应一种字符集；

• 字符集是一套字符与一套编码的映射集合，像这样：

  字符	编码
  A	0
  B	1

• 排序规则是字符集内用来比较每个字符的一套规则，也就是字符的排序方式
  比如要比较字符 A 和 B 的大小，最简单直观的方法就是对比他们对应的编码。显然编码 0 < 1，这种规则下 A < B。那么类似这样的规则集合就是排序规则。单字节字符编码如此，多字节的编码排序也以此类推。

排序规则后缀说明：

1. _ci : 不区分大小写,Case-insensitive的缩写；
2. _cs : 区分大小写，Case-sensitive的缩写；
3. _ai : 不区分重音，Accent-insensitive的缩写；
4. _as : 区分重音，Accent-sensitive的缩写；
5. _bin : 二进制；

NO PAD vs PAD SPACE

含义：表示排序时是否需要比较字符后面的空格

• NO PAD（处理）

如果字符后面有空格，那就把空格当作一个字符处理。也就是在对比的时候不会忽视空格的存在。

• PAD SPACE（忽略）

表示如果字符后面有空格，可以忽略空格来比较。也就是空格可有可无。

查询已有的字符集

执行mysql命令：SHOW CHARSET ，查看当前mysql支持的所有字符集；如下表所示：

mysql8推荐是utf8mb4，所以看到最后一行：字符集（Chartset）utf8mb4使用的事UTF-8 Unicode编码，它的默认排序规则（collation）是utf8mb4_0900_ai_ci，该字符集占用最大字节为4个。

查询指定字符集的排序规则

这里我们只关心utf8mb4，查询utf8mb4的所有排序规则，执行mysql命令：SHOW COLLATION LIKE 'utf8mb4%'；结果如下表格：

其中以下排序规则为通用的：

下面这个格式代表地区或国家语言支持，这里只关注中文中国。

其他没有例举出来的，表示其他国家或地区的排序规则，不关注。

字符排序含义区别

MySQL 8.0 .1默认的是 utf8mb4_0900_ai_ci，属于 utf8mb4_unicode_ci 中的一种，具体含义如下：

1. uft8mb4 表示用 UTF-8 编码方案，每个字符最多占 4 个字节。
2. 0900 指的是 Unicode 校对算法版本。（Unicode 归类算法是用于比较符合 Unicode 标准要求的两个 Unicode 字符串的方法）。
3. ai 指的是口音不敏感。也就是说，排序时 e，è，é，ê 和 ë 之间没有区别。
4. ci 表示不区分大小写。也就是说，排序时 p 和 P 之间没有区别。
5. utf8mb4 已成为默认字符集，在 MySQL 8.0.1 及更高版本中将 utf8mb4_0900_ai_ci 作为默认排序规则。以前，utf8mb4_general_ci 是默认排序规则。由于 utf8mb4_0900_ai_ci 排序规则现在是默认排序规则，因此默认情况下新表格可以存储基本多语言平面之外的字符。现在可以默认存储表情符号。如果需要重音灵敏度和区分大小写，则可以使用 utf8mb4_0900_as_cs 代替。
6. utf8mb4_general_ci（general_ci 则是在 unicode_ci 排序上做了一定优化，排序效率提高了一点（十分之一左右），只支持单字符对比排序。）在StackOverflow中推荐是使用 unicode_ci，因为general_ci的效率提升有限。

例如：

在德语中 ß 书写为 ss。
unicode_ci 只有 ß = ss，单个 s 和 ß 是不同的。
general_ci 做了优化，只支持单字符比较，但是又需要 对 ß 进行排序，所以结果是：ß = s

• utf8mb4_unicode_ci（遵守的Unicode组织的4.0.0归类算法排序）
• utf8mb4_unicode_520_ci（遵守的Unicode组织的 5.2.0归类算法排，不区分大小写）
• utf8mb4_0900_ai_ci（遵守的Unicode组织的 9.0.0归类算法排，不区分重音，不区分大小写，mysql8的默认排序规则）
• utf8mb4_0900_as_ci（遵守的Unicode组织的 9.0.0归类算法排，区分重音，不区分大小写）
• utf8mb4_0900_as_cs（遵守的Unicode组织的 9.0.0归类算法排，区分重音，区分大小写）
  utf8mb4_0900_bin（遵守的Unicode组织的 9.0.0归类算法排，对于二进制字符集，字母大小写和重音等效的概念不适用，对于存储为二进制字符串的单字节字符，字符和字节边界是相同的，因此在比较中，字母大小写和重音符号的差异是显著的。也就是说，排序规则区分大小写和重音）
• utf8mb4_zh_0900_as_cs（遵守的Unicode组织的 9.0.0归类算法排，区分重音，区分大小写，支持中国中文）
• utf8mb4_bin：将字符串每个字符用二进制数据编译存储，区分大小写，而且可以存二进制的内容。
• utf8mb4_0900_bin和utf8mb4_bin去区别：归类算法排版本不同，前者会处理字符串结尾的空格，后者会忽略字符串结尾的空格。这两个二进制排序规则具有相同的排序顺序，但它们的pad属性和排序权重特征不同。请参见排序板属性和字符排序权重

官网文档：

For the binary character set, the concepts of lettercase and accent equivalence do not apply:

• For single-byte characters stored as binary strings, character and byte boundaries are the same, so lettercase and accent differences are significant in comparisons. That is, the binary collation is case-sensitive and accent-sensitive.

**译文：**对于存储为二进制字符串的单字节字符，字符和字节边界是相同的，因此字母大小写和重音差异在比较中很重要。也就是说，二进制排序规则区分大小写和重音。

对于存储为二进制字符串的多字节字符，字符和字节边界不同。字符边界丢失，因此依赖于它们的比较没有意义。

总结

1、通用业务场景使用utf8mb4和排序规则utf8mb4_0900_ai_ci，不需要区分大小写，不需要区分重音；

2、不区分大小写区分重音使用：utf8mb4_0900_as_ci；

3、不区分大小写也不区分重音可以使用以下三种，三种区别可以对比以上排序规则：

4、下面这三个版本的排序归类算法版本较低，不推荐使用

5、如果业务场景种使用的中文很多，推荐使用utf8mb4_zh_0900_as_cs，优点：包含所有目前支持的中文，占用空间较小。

详情参阅mysql官网：MySQL :: MySQL 8.0 Reference Manual :: 10.10.1 Unicode Character Sets

---

标题：MySQL字符集和排序规则详解
作者：JayGao
地址：https://www.jaygao.top/articles/2022/04/21/1650516606373.html
版权：版权JayGao所有，如需转载，请注明出处
捐赠：