【重学MySQL】九十三、MySQL字符集与比较规则完全解析
- 一、字符集概述
- 1.1 支持的字符集
- 1.2 UTF8与UTF8MB4的区别
- 二、比较规则（Collation）
- 2.1 比较规则分类
- 2.2 常见比较规则差异
- 三、配置层级与继承关系
- 3.1 配置层级
- 3.2 继承关系
- 四、最佳实践与问题解决
- 4.1 统一字符集
- 4.2 数据迁移与转换
- 4.3 性能优化
- 4.4 常见问题
- 五、示例配置
- 5.1 服务器级配置（`my.cnf`）
- 5.2 创建数据库与表
- 5.3 修改现有表
- 六、总结
一、字符集概述
字符集是MySQL中用于定义字符如何存储、表示和比较的一组规则。它决定了数据库如何存储和检索文本数据，并处理不同语言和特殊字符。
1.1 支持的字符集
MySQL支持多种字符集，常见字符集如下：
| 字符集 | 描述 |
|---|---|
| UTF8/UTF8MB4 | UTF8支持1-3字节字符，适合大多数场景；UTF8MB4扩展至4字节，支持emoji等特殊字符。 |
| Latin1 | 西欧语言，兼容ASCII，适用于英文和西欧语言。 |
| GBK/GB2312 | 简体中文，GBK覆盖更多字符，GB2312是其前身。 |
| ASCII | 仅支持英文字符，每个字符占1字节。 |
| UTF16 | 使用16位编码，适合存储复杂字符集（如中文、日文、韩文）。 |
1.2 UTF8与UTF8MB4的区别
- UTF8：最大支持3字节字符，无法存储emoji等4字节字符。
- UTF8MB4：支持4字节字符，是UTF8的超集，推荐用于现代应用。
二、比较规则（Collation）
比较规则决定字符串的排序和比较方式，它与字符集密切相关。
2.1 比较规则分类
| 规则类型 | 描述 |
|---|---|
| 大小写敏感 | 如utf8_bin（二进制比较），区分大小写。 |
| 大小写不敏感 | 如utf8_general_ci（通用规则），不区分大小写。 |
| 语言特定 | 如utf8_spanish_ci（西班牙语规则），按特定语言排序。 |
| Unicode标准 | 如utf8_unicode_ci（符合Unicode标准），排序更准确但性能略低。 |
| 最新规则 | MySQL 8.0+支持utf8mb4_0900_ai_ci，基于Unicode 9.0，支持更复杂的排序。 |
2.2 常见比较规则差异
utf8_general_ci：速度快，但排序可能不准确（如某些特殊字符）。utf8_unicode_ci：遵循Unicode标准，排序更准确，但性能略低。utf8mb4_0900_ai_ci：MySQL 8.0+默认规则，支持扩展的排序和比较功能。
三、配置层级与继承关系
MySQL的字符集和比较规则可配置于四个层级，优先级从高到低为：列级 > 表级 > 数据库级 > 服务器级。
3.1 配置层级
| 层级 | 配置方法 |
|---|---|
| 服务器级 | 通过my.cnf配置文件或启动参数（如--character-set-server=utf8mb4）。 |
| 数据库级 | 创建数据库时指定：CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
| 表级 | 创建表时指定：CREATE TABLE table (...) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
| 列级 | 定义字段时指定：name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
3.2 继承关系
- 若未显式指定，低层级继承高层级的设置。例如：
- 表未指定字符集时，使用数据库的字符集。
- 列未指定字符集时，使用表的字符集。
四、最佳实践与问题解决
4.1 统一字符集
- 避免乱码：确保服务器、数据库、表、列的字符集一致，并与客户端连接字符集匹配。
- 客户端连接设置：
或在配置文件SET NAMES utf8mb4;my.cnf中设置：[client] default-character-set = utf8mb4
4.2 数据迁移与转换
- 修改现有表的字符集：
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; - 备份数据：修改字符集前务必备份，避免数据损坏。
4.3 性能优化
- 选择合适的比较规则：
- 对性能敏感的场景，选择
utf8_general_ci。 - 需要准确排序时，使用
utf8_unicode_ci或utf8mb4_0900_ai_ci。
- 对性能敏感的场景，选择
4.4 常见问题
- 乱码问题：检查所有层级的字符集设置是否一致，并确保客户端连接字符集正确。
- 排序错误：确认比较规则是否符合需求，例如中文排序需选择支持中文的规则（如
utf8mb4_unicode_ci）。
五、示例配置
5.1 服务器级配置（my.cnf）
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
5.2 创建数据库与表
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5.3 修改现有表
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
六、总结
- 字符集决定存储方式，比较规则决定排序和比较行为。
- 优先使用
UTF8MB4以支持更广泛的字符，搭配utf8mb4_unicode_ci或utf8mb4_0900_ai_ci确保准确排序。 - 统一各层级的字符集设置，避免乱码和性能问题。
通过合理配置字符集和比较规则，可确保MySQL高效、正确地处理多语言数据。
)
