MySQL 字符集和比较规则

基于 MySQL 8.0.42，默认字符集为 utf8mb4

字符集和比较规则是什么

字符集是用于定义字符如何存储和展示的编码规则集合。它规定了数据库中可以使用哪些字符以及这些字符在计算机中如何表示。我们可以通过一个简单的例子来理解字符集：假设有一个包含
A、B、a、b 四个字符的字母表,我们为每个字母分配一个编码值:A=0、B=1、a=2、b=3。这里 A 是符号,0 是编码,所有字母与编码的对应关系共同构成了一个字符集。

常见的字符集包括:

• ASCII：一种基础字符集,仅包含128个字符,主要是英文字母、数字和常用符号
• utf8mb4：支持完整的Unicode字符集(包含emoji表情),是MySQL 8.0的默认字符集
• latin1：支持西欧语言的字符集

字符集对数据库系统有着重要影响,主要体现在以下方面:

1. 数据存储空间的使用
2. 数据传输时的编码转换
3. 数据的可读性和跨系统兼容性

假设我们需要比较两个字符串 A 和 B。一种方法是按照它们在字符集中的编码值进行比较，如果 A 的编码是 0，B 的编码是 1，那么因为 0
小于 1，所以 A 小于 B。这种字符编码的比较规则就是比较规则。

比较规则定义了如何比较字符集中的字符。最基础的比较规则是二进制比较，即直接比较字符的编码值大小。

但是如果我们想说小写字母和大写字符是等价的呢，那么就需要两条规则：a)大写字母和小写字母是相等的；b）然后比较它们的编码。这被称之为大小写不敏感的比较规则， 他比二进制比较要稍微复杂点。

MySQL 中的字符集和比较规则

mysql 支持多种的字符集和比较规则，可以分别使用 SHOW CHARACTER SET [LIKE 匹配模式] 以及 SHOW COLLATION [LIKE 匹配模式] 语句来查看。

mysql> SHOW CHARACTER SET LIKE 'utf%';
+---------+------------------+--------------------+--------+
| Charset | Description      | Default collation  | Maxlen |
+---------+------------------+--------------------+--------+
| utf16   | UTF-16 Unicode   | utf16_general_ci   |      4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci |      4 |
| utf32   | UTF-32 Unicode   | utf32_general_ci   |      4 |
| utf8mb3 | UTF-8 Unicode    | utf8mb3_general_ci |      3 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_0900_ai_ci |      4 |
+---------+------------------+--------------------+--------+

每个字符集都至少包含一个比较规则，大多数字符集都拥有多个比较规则。

mysql> SHOW COLLATION LIKE 'utf8mb4_0900%';
+--------------------+---------+-----+---------+----------+---------+---------------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+--------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs | utf8mb4 | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin   | utf8mb4 | 309 |         | Yes      |       1 | NO PAD        |
+--------------------+---------+-----+---------+----------+---------+---------------+

比较规则通常具备以下的特征：

• 两个不同的字符集不会拥有相同的比较规则
• 每个字符集都有着默认的比较规则
• 比较规则的名称以字符集名称开头，后面跟着一个或多个的表示排序特征的后缀

比较规则命名规则：

• 以字符集名称开头，例如 utf8mb4_
• 后面跟着版本号（可选），如 0900_
• 接着是一个或多个后缀来表示特定的排序行为：
  • _ai：accent insensitive，不区分重音
  • _as：accent sensitive，区分重音
  • _ci：case insensitive，不区分大小写
  • _cs：case sensitive，区分大小写
  • _bin：binary，二进制比较
• 例如：utf8mb4_0900_ai_ci 表示：
  • utf8mb4 字符集
  • 0900 版本
  • ai 不区分重音
  • ci 不区分大小写

默认行为

• 如果创建或者修改列时没有显式指定字符集和比较规则，则默认使用表的字符集和比较规则
• 如果创建表时没有显式指定字符集和比较规则，则默认使用数据库的字符集和比较规则
• 如果创建数据库时没有显示指定字符集和比较规则，则默认使用服务器的字符集和比较规则