以下内容是 MySQL 官方文档中关于 字符集（Character Sets）和排序规则（Collations） 的核心理论部分，涵盖了从基本概念到实际机制的深入解释。我们来一步步、清晰地理解这些内容。

---

🧩 一、核心概念回顾：字符集 vs 排序规则

✅ 字符集（Character Set）

• 是“符号与编码的集合”。
• 它定义了每个字符如何被存储为二进制数据。
• 例如：
  • A → 编码 0
  • B → 编码 1
  • a → 编码 2
  • b → 编码 3
• 这个“字母+数字对应关系”就构成了一个简单的字符集。

🔹 所以：字符集 = 字符 ↔ 编码 的映射表

---

✅ 排序规则（Collation）

• 是“比较字符的规则集合”，用于排序（ORDER BY）、比较（WHERE）、去重（GROUP BY）等操作。
• 它决定了两个字符串是否相等、谁大谁小。

举个例子：

字符	编码
A	0
B	1
a	2
b	3

• 如果直接按编码比较：A(0) < B(1) < a(2) < b(3)
  • 这叫 二进制排序（Binary Collation）
• 但如果我们希望 A = a，B = b，即不区分大小写
  • 那就需要一条规则：“比较前先转成大写”
  • 这种就是 大小写不敏感排序（Case-Insensitive Collation）

🔹 所以：排序规则 = 如何比较字符的逻辑

---

📚 二、MySQL 中的字符集与排序规则支持

1. 查看可用字符集

使用命令：

SHOW CHARACTER SET;

或过滤 UTF-8 相关：

SHOW CHARACTER SET LIKE 'utf%';

输出关键字段：

列名	含义
Charset	字符集名称
Description	描述
Default collation	默认排序规则
Maxlen	每个字符最多占用字节数

常见字符集对比：

字符集	支持语言	最大长度	是否推荐
latin1	西欧语言（英文、法语等）	1 字节	❌ 不推荐
utf8mb3	基本 Unicode（不含 emoji）	3 字节	⚠️ 已废弃
utf8mb4	完整 Unicode（含 emoji）	4 字节	✅ 强烈推荐
utf16, utf32	其他 Unicode 编码方式	2~4 字节	少用

💡 utf8mb4 是现代 MySQL 的默认字符集（从 8.0 开始），应始终优先使用。

---

2. 查看某个字符集的所有排序规则

SHOW COLLATION WHERE Charset = 'utf8mb4';

输出示例：

| Collation                  | Charset | Default |
|----------------------------|---------|---------|
| utf8mb4_0900_ai_ci         | utf8mb4 | Yes     |
| utf8mb4_bin                | utf8mb4 |         |
| utf8mb4_general_ci         | utf8mb4 |         |
| utf8mb4_unicode_ci         | utf8mb4 |         |
| utf8mb4_zh_0900_as_cs      | utf8mb4 |         |

排序规则命名规则（命名约定）：

格式：字符集_版本_重音敏感_大小写敏感

• utf8mb4_0900_ai_ci
  • utf8mb4: 字符集
  • 0900: Unicode 9.0 标准
  • ai: accent insensitive（不区分重音，如 é = e）
  • ci: case insensitive（不区分大小写，如 A = a）

🆚 对比：

• utf8mb4_0900_as_cs → 区分大小写 + 区分重音
• utf8mb4_bin → 按二进制直接比较（最严格）

✅ 推荐默认排序规则：utf8mb4_0900_ai_ci

---

🔤 三、字符集“库容”（Character Set Repertoire）

这是个高级但非常重要的概念。

什么是 Repertoire？

• 表示一个字符串表达式中可能包含的字符范围。
• 有两个值：
  1. ASCII：只包含 U+0000 ~ U+007F 的字符（基本英文）
  2. UNICODE：可包含所有 Unicode 字符（包括中文、emoji、阿拉伯文等）

为什么需要 Repertoire？

场景问题：混合字符集操作时容易出错

SELECT CONCAT(latin1_column, ascii_column) FROM t;

如果没有 repertoire，MySQL 会报错：

ERROR 1267: Illegal mix of collations

解决方案：利用“子集 → 超集”自动转换

• ASCII 是 UNICODE 的子集
• 所以 MySQL 允许将 ASCII 字符串安全地转换为其他字符集（如 latin1、utf8mb4）

👉 因此，repertoire 的作用是：

在无法确定排序规则优先级时，通过判断字符集“容量”来决定是否可以安全转换，避免不必要的错误。

---

✅ Repertoire 的判断规则（重点）

表达式	Repertoire
'abc'（纯英文）	ASCII
_utf8mb4'abc'	仍是 ASCII（因为内容没超出 ASCII）
_utf8mb4'你好'	UNICODE（含中文）
列 CHAR(1) CHARACTER SET ascii	ASCII
UPPER('abc')	继承参数的 repertoire → ASCII
FORMAT(123.4, 2)	若 character_set_connection=ascii → ASCII，否则 UNICODE
CONCAT(str1, str2)	取两者中最“宽”的：UNICODE > ASCII

---

🌐 四、元数据使用 UTF-8 存储（UTF-8 for Metadata）

什么是元数据（Metadata）？

“关于数据的数据”，比如：

• 数据库名、表名、列名
• 用户名、版本号
• SHOW 命令的结果
• INFORMATION_SCHEMA 表中的内容

为什么元数据必须用 UTF-8？

因为要满足两个要求：

1. 统一编码：不能有的列名是 latin1，有的是 utf8，否则查询会乱。
2. 支持所有语言：允许用户用中文、阿拉伯文命名表。

✅ 所以：MySQL 内部用 UTF-8 存储所有元数据

相关系统变量：

SHOW VARIABLES LIKE 'character_set_system';
-- 输出：utf8mb3（当前元数据使用的字符集）

⚠️ 注意：虽然内部是 utf8mb3，但建议客户端连接时使用 utf8mb4。

---

元数据的传输与显示

当你执行：

SELECT user_name FROM my_table;

• 列名 user_name 是元数据
• 它从服务器发送给客户端时，会根据 character_set_results 设置进行编码转换

默认流程：

1. 服务器内部元数据：utf8mb3（由 character_set_system 控制）
2. 发送给客户端前：转换为 character_set_results 指定的字符集（默认 utf8mb4）
3. 客户端收到后正确显示

🔁 如果你设置 SET NAMES 'latin1';，服务器会自动把元数据（如列名）从 UTF-8 转成 latin1 再发给你。

---

自动转换机制（避免乱码）

MySQL 在某些场景下会自动做字符集转换：

SELECT * FROM t1 WHERE USER() = latin1_column;

• USER() 返回的是 UTF-8 字符串
• latin1_column 是 latin1 字符集
• MySQL 会自动把 latin1_column 转成 UTF-8 再比较 ✅

INSERT INTO t1 (latin1_column) SELECT USER();

• USER() 是 UTF-8
• 插入目标是 latin1 列
• MySQL 自动转码后再插入 ✅

✅ 这种自动转换基于“Unicode 是超集”的原则，让不同字符集之间可以互通。

---

✅ 总结：关键要点一览

主题	关键结论
🔤 字符集	定义字符如何编码存储；推荐使用 utf8mb4
📏 排序规则	定义字符如何比较；推荐 utf8mb4_0900_ai_ci
⚠️ utf8 vs utf8mb4	utf8 是 utf8mb3 的别名，已废弃，不支持 emoji
🧩 多层级设置	支持在 server / db / table / column / string 级别设置
🔄 客户端通信	使用 SET NAMES 'utf8mb4'; 确保通信编码一致
🧠 Repertoire	判断表达式是否只含 ASCII，用于解决混合字符集运算问题
🌍 元数据编码	所有元数据（列名、表名等）内部用 UTF-8 存储
🔁 自动转换	MySQL 会在必要时自动在 UTF-8 和其他字符集间转换

---

💡 实践建议

1. 建库建表时显式指定：

   CREATE DATABASE mydb 
     CHARACTER SET utf8mb4 
     COLLATE utf8mb4_0900_ai_ci;
   

2. 连接数据库后立即设置：

   SET NAMES 'utf8mb4';
   

3. 配置文件中设置默认值（my.cnf）：

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

4. 避免使用 utf8，全部替换为 utf8mb4

5. 检查现有系统是否还在用 utf8：

   SELECT TABLE_SCHEMA, TABLE_NAME 
   FROM INFORMATION_SCHEMA.TABLES 
   WHERE TABLE_COLLATION LIKE 'utf8_%';
   

---

如果你想进一步了解：

• 如何将 utf8mb3 升级到 utf8mb4
• 如何处理 Illegal mix of collations 错误
• 不同排序规则对性能的影响

欢迎继续提问！我可以提供具体 SQL 示例和迁移方案。