当前位置：首页 > article >正文

MySQL 排序规则（COLLATE）详解

article 2025/1/24 10:50:34

MySQL 排序规则（COLLATE）详解

在 MySQL 中，排序规则（Collation） 定义了字符串比较和排序的方式。每种字符集（Character Set）通常会支持多个排序规则，排序规则影响字符串的大小写敏感性、语言特定排序规则等。

以下是关于 COLLATE 的详细说明，包括它的作用、命名规则和常见选项。

1. COLLATE 的作用

COLLATE 主要用于以下三个方面：

字符串比较
决定了字符串在 WHERE 条件、JOIN、GROUP BY 等场景中的比较规则。例如，utf8mb4_general_ci 中 'A' = 'a' 为 TRUE，但在 utf8mb4_bin 中，'A' = 'a' 为 FALSE。
字符串排序
决定了 ORDER BY 的排序方式。例如，不同语言可能对带有重音的字符排序方式有所不同。
查询匹配行为
决定了 LIKE 查询是否区分大小写。例如，utf8mb4_general_ci 中 LIKE 'A%' 会匹配 'a'，但 utf8mb4_bin 则不会。

2. COLLATE 的命名规则

MySQL 排序规则的命名遵循以下格式：

<字符集>_<规则类型或版本>_<敏感性>

常见字段解析：

字符集：指定字符串的编码方式，例如 utf8mb4（推荐用于现代应用，支持全 Unicode）。
规则类型或版本：
- general：通用规则（效率优先，但准确性较低）。
- unicode：基于 Unicode 的排序规则（准确性更高）。
- 0900：Unicode 9.0 标准（MySQL 8.0+ 支持）。
- 520：Unicode 5.2 标准（例如 utf8mb4_unicode_520_ci）。
敏感性（可选）：
- ci（Case Insensitive）：不区分大小写。
- cs（Case Sensitive）：区分大小写。
- _bin（Binary）：严格按二进制值比较。
- Unicode 9.0+ 规则额外支持：
  - as（Accent Sensitive）：区分重音。
  - ai（Accent Insensitive）：不区分重音。

示例：

utf8mb4_general_ci：基于通用规则的 utf8mb4 排序，不区分大小写。
utf8mb4_unicode_ci：基于 Unicode 排序规则，不区分大小写。
utf8mb4_unicode_520_ci：基于 Unicode 5.2 标准的排序规则，不区分大小写。
utf8mb4_bin：按二进制值比较和排序，区分大小写和重音。
utf8mb4_0900_as_cs：基于 Unicode 9.0，区分大小写和重音（MySQL 8.0+ 支持）。

3. 常见的 COLLATE 选项

以下是 MySQL 中常用的排序规则及其特性：

排序规则	特点	是否区分大小写	是否区分重音	适用场景
`utf8mb4_general_ci`	通用排序规则，效率高，但可能不完全符合 Unicode 标准。	否	否	常见的默认设置，不需要复杂语言支持时可用。
`utf8mb4_unicode_ci`	基于 Unicode 排序规则，更准确但略慢。	否	部分区分^*	支持复杂语言排序需求（如法语、德语）。
`utf8mb4_unicode_520_ci`	基于 Unicode 5.2 的排序规则，比 `unicode_ci` 更准确。	否	部分区分^*	需要更符合 Unicode 5.2 标准的排序规则。
`utf8mb4_bin`	按字节值比较，完全区分大小写和重音。	是	是	区分大小写的唯一键或严格排序时使用。
`utf8mb4_0900_as_cs`	基于 Unicode 9.0，区分大小写和重音（MySQL 8.0+ 支持）。	是	是	需要 Unicode 9.0 标准且严格区分大小写和重音的场景。

^* unicode_ci 和 unicode_520_ci 对部分重音字符可能视为不同（例如 e 和 é），但排序时可能相邻。完全区分重音需使用 utf8mb4_0900_as_cs。

4. 如何选择合适的 COLLATE

选择适合的排序规则要基于应用的需求和场景：

不关心大小写和重音（一般业务系统）
- 推荐：utf8mb4_general_ci 或 utf8mb4_unicode_ci。
- 示例：用户输入搜索时不区分大小写或重音。
需要严格区分大小写
- 推荐：utf8mb4_bin 或 utf8mb4_0900_as_cs。
- 示例：登录系统的用户名 User 和 user 必须被视为不同。
需要支持复杂语言排序（Unicode 标准）
- 推荐：utf8mb4_unicode_ci 或更高版本如 utf8mb4_unicode_520_ci。
- 示例：电商平台需要按照特定语言规则排序商品名称。
需要支持区分重音的排序
- 推荐：utf8mb4_0900_as_cs（MySQL 8.0+ 支持）。
- 示例：区分法语中 'é' 和 'e' 的排序需求。

5. 如何指定 COLLATE

可以在多个层级指定 COLLATE：数据库、表、列，甚至单次查询中。

创建数据库时指定

CREATE DATABASE mydb
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_bin;

创建表时指定

CREATE TABLE ai_player_smy (
    name VARCHAR(255) COLLATE utf8mb4_bin NOT NULL,
    chest INT NOT NULL,
    PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

单列指定

ALTER TABLE ai_player_smy 
MODIFY COLUMN name VARCHAR(255) COLLATE utf8mb4_bin NOT NULL;

查询时指定

SELECT * 
FROM ai_player_smy 
WHERE name = 'Ai' COLLATE utf8mb4_bin;

6. 注意事项

默认排序规则
- MySQL 5.x 默认使用 utf8mb4_general_ci。
- MySQL 8.0+ 默认改为 utf8mb4_0900_ai_ci（不区分大小写和重音）。
性能影响
- _general_ci 效率最高，但准确性较低。
- _unicode_ci 和 _unicode_520_ci 更准确但略慢。
- _0900 规则符合最新 Unicode 标准，但性能可能更低。
版本支持
- utf8mb4_0900_as_cs 仅在 MySQL 8.0+ 中支持，低版本需使用 utf8mb4_bin。