MySQL - 排序规则

MySQL 字符集与排序规则：现代指南

在 MySQL 中，**字符集（character set）是一组符号及其编码的集合，而排序规则（collation）**则是用于在该字符集内比较和排序字符的一套规则。正确理解和设置它们对于正确存储国际化数据并确保查询按预期行为至关重要。

可以将字符集看作是字母表（例如，英文字母表），将排序规则看作是字典的排序规则（例如，‘Apple’ 应该在 ‘apple’ 之前还是之后？）。每个字符集都至少有一个默认的排序规则。

最佳实践：使用 utf8mb4

对于现代应用程序，推荐的字符集是 utf8mb4。它提供完整的 Unicode 支持，包括表情符号（emojis）和各种国际符号，而旧的 utf8（在现代 MySQL 中是 utf8mb3 的别名）则不提供。从一开始就使用 utf8mb4 可以避免未来的数据损坏或迁移麻烦。

在 MySQL 8.0 及更高版本中，utf8mb4 的推荐排序规则是 utf8mb4_0900_ai_ci。让我们来分解一下它的名称：

• utf8mb4：字符集。
• 0900：基于 Unicode 9.0.0 版本，为多种语言提供更准确的排序。
• ai：不区分重音（Accent-insensitive）。在排序时，它将 ‘a’、‘á’ 和 ‘ä’ 视为相同字符。
• ci：不区分大小写（Case-insensitive）。在排序时，它将 ‘a’ 和 ‘A’ 视为相同字符。

其他常见的排序规则类型包括用于严格匹配的 _as_cs（区分重音、区分大小写），以及基于字符二进制字节值进行比较的 _bin（二进制）。

设置字符集和排序规则

您可以在四个不同的级别定义字符集和排序规则。MySQL 使用一种层级结构：列设置会覆盖表设置，表设置会覆盖数据库设置，数据库设置会覆盖服务器设置。

1. 服务器级别

您可以在配置文件（my.cnf 或 my.ini）中为整个 MySQL 服务器配置默认字符集和排序规则。这确保所有新建的数据库和表都使用现代默认值。

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

2. 数据库级别

您可以在创建特定数据库时或之后修改它来指定默认值。这是管理应用程序特定设置最常用且推荐的方法。

-- 创建一个使用现代默认设置的新数据库
CREATE DATABASE my_app_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

-- 更新现有数据库
ALTER DATABASE existing_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

3. 表级别

您可以为特定表覆盖数据库的默认设置。这对于具有特殊语言需求的表可能很有用。

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)
)
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;

4. 列级别

为了实现最高级别的控制，您可以为单个基于文本的列指定字符集和排序规则。例如，如果某个列需要区分大小写的比较，而表的其余部分不需要，这就会很有用。

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(100) COLLATE utf8mb4_0900_as_cs, -- 用户名区分大小写
  bio TEXT -- 继承表的默认排序规则（不区分大小写）
);

验证排序规则

您可以使用 SHOW CHARACTER SET 命令列出所有可用的字符集及其默认排序规则。

SHOW CHARACTER SET;

要查找特定字符集或匹配某种模式的所有可用排序规则，您可以使用带有 WHERE 或 LIKE 子句的 SHOW COLLATION 命令。

示例：查找 utf8mb4 排序规则

SHOW COLLATION WHERE Charset = 'utf8mb4';

示例输出（缩略）

排序规则	字符集	ID	默认	已编译	排序长度
utf8mb4_0900_ai_ci	utf8mb4	255	是	是	0
utf8mb4_0900_as_cs	utf8mb4	305		是	0
utf8mb4_bin	utf8mb4	46		是	1
utf8mb4_general_ci	utf8mb4	45		是	1

注意：用户自定义的排序规则在现代 MySQL 版本中已被弃用，强烈不建议使用它们。