跳到主要内容

MySQL 字符集与排序规则

字符集和排序规则是数据库处理文本数据的基础。正确配置字符集不仅能避免中文乱码问题，还能确保数据的正确存储和查询。本章将详细介绍 MySQL 字符集与排序规则的概念、配置和最佳实践。

基本概念​

什么是字符集？​

字符集（Character Set）是一套符号和编码的集合。它定义了哪些字符可以被存储，以及每个字符如何被编码为二进制数据。

打个比方，字符集就像是一本"字典"，告诉计算机如何将人类可读的字符（如"中"、"A"、"😊"）转换为计算机可存储的二进制数据。

字符集示例：
┌─────────────────────────────────────────────┐
│  字符    │  编码（十六进制）│  二进制         │
├─────────────────────────────────────────────┤
│  A       │  41              │  01000001      │
│  中      │  E4B8AD          │  111001001011100010101101 │
│  😊      │  F09F988A        │  11110000100111111001100010001010 │
└─────────────────────────────────────────────┘

什么是排序规则？​

排序规则（Collation）定义了字符集中字符的比较和排序规则。它决定了：

  • 字符如何比较大小
  • 字符串如何排序
  • 查询时是否区分大小写
  • 查询时是否区分重音

例如，在 utf8mb4_general_ci 排序规则中：

  • ci 表示 case-insensitive（不区分大小写）
  • A 和 a 被视为相同
  • 排序时 Apple 和 apple 可能相邻

字符集与排序规则的关系​

一个字符集可以有多个排序规则，但一个排序规则只能属于一个字符集：

utf8mb4 字符集
    ├── utf8mb4_general_ci    （通用排序，不区分大小写）
    ├── utf8mb4_0900_ai_ci    （MySQL 8.0 默认，基于 UCA 9.0）
    ├── utf8mb4_bin           （二进制比较，区分大小写）
    ├── utf8mb4_unicode_ci    （基于 UCA 4.0）
    └── utf8mb4_unicode_520_ci（基于 UCA 5.2）

MySQL 支持的字符集​

查看支持的字符集​

-- 查看所有支持的字符集
SHOW CHARACTER SET;

-- 查看特定字符集
SHOW CHARACTER SET LIKE 'utf8%';

-- 从 information_schema 查询
SELECT 
    CHARACTER_SET_NAME,
    DEFAULT_COLLATE_NAME,
    DESCRIPTION,
    MAXLEN
FROM information_schema.CHARACTER_SETS
WHERE CHARACTER_SET_NAME LIKE 'utf8%';

常用字符集对比​

字符集说明每字符最大字节推荐场景
utf8mb4UTF-8 完整实现4 字节推荐使用，支持所有 Unicode 字符
utf8mb3UTF-8 子集（已弃用）3 字节旧系统兼容，不支持 Emoji
utf8utf8mb3 的别名3 字节不推荐，MySQL 8.0 已弃用
latin1西欧字符集1 字节旧系统，不支持中文
gbk中文 GBK 编码2 字节特定中文场景
gb18030中国国家标准4 字节中国政府项目要求
big5繁体中文2 字节港台地区

utf8 vs utf8mb4 的关键区别​

这是 MySQL 中一个著名的"陷阱"：

-- utf8 实际上是 utf8mb3 的别名，只支持最多 3 字节的字符
-- 无法存储 Emoji 和部分生僻字

-- 尝试存储 Emoji
CREATE TABLE test_utf8 (
    content VARCHAR(100) CHARACTER SET utf8
);

INSERT INTO test_utf8 VALUES ('😊');
-- 错误：Incorrect string value: '\xF0\x9F\x98\x8A'

-- utf8mb4 支持完整的 UTF-8，包括 Emoji
CREATE TABLE test_utf8mb4 (
    content VARCHAR(100) CHARACTER SET utf8mb4
);

INSERT INTO test_utf8mb4 VALUES ('😊');
-- 成功！

重要结论：永远使用 utf8mb4，不要使用 utf8。

字符集的存储开销​

字符集影响字符串的存储空间计算：

-- VARCHAR(n) 中的 n 是字符数，不是字节数
-- utf8mb4 每个字符最多占 4 字节

-- VARCHAR(100) 在 utf8mb4 下的最大存储空间
-- 100 字符 × 4 字节 + 2 字节长度前缀 = 402 字节

-- 创建表时注意索引长度限制
CREATE TABLE articles (
    title VARCHAR(255),  -- utf8mb4 下可能超过索引长度限制
    INDEX idx_title (title(191))  -- 191 × 4 = 764 字节，小于 767 字节限制
);

排序规则详解​

查看排序规则​

-- 查看所有排序规则
SHOW COLLATION;

-- 查看特定字符集的排序规则
SHOW COLLATION WHERE Charset = 'utf8mb4';

-- 从 information_schema 查询
SELECT 
    COLLATION_NAME,
    CHARACTER_SET_NAME,
    IS_DEFAULT,
    IS_COMPILED
FROM information_schema.COLLATIONS
WHERE CHARACTER_SET_NAME = 'utf8mb4'
ORDER BY COLLATION_NAME;

排序规则命名规则​

MySQL 排序规则名称遵循一定的命名规范：

字符集_版本_特殊属性_后缀

示例：utf8mb4_0900_ai_ci
      │       │    │  │
      │       │    │  └── ci: case insensitive（不区分大小写）
      │       │    └───── ai: accent insensitive（不区分重音）
      │       └────────── 0900: UCA 9.0.0 版本
      └────────────────── utf8mb4 字符集

常见后缀含义：

后缀英文含义
_ciCase Insensitive不区分大小写
_csCase Sensitive区分大小写
_binBinary二进制比较，区分大小写
_aiAccent Insensitive不区分重音
_asAccent Sensitive区分重音

MySQL 8.0 默认排序规则​

MySQL 8.0 的默认排序规则是 utf8mb4_0900_ai_ci：

  • 0900：基于 Unicode Collation Algorithm (UCA) 9.0.0
  • ai：不区分重音（accent insensitive）
  • ci：不区分大小写（case insensitive）
-- 查看服务器默认排序规则
SHOW VARIABLES LIKE 'collation_server';

-- 查看数据库默认排序规则
SHOW VARIABLES LIKE 'collation_database';

排序规则的选择​

场景一：需要区分大小写

-- 使用 _bin 或 _cs 排序规则
CREATE TABLE users (
    username VARCHAR(50) COLLATE utf8mb4_bin,
    email VARCHAR(100)
);

-- 查询时区分大小写
SELECT * FROM users WHERE username = 'Admin';  -- 只匹配 'Admin'，不匹配 'admin'

场景二：多语言支持

-- 使用 Unicode 排序规则，支持多种语言的正确排序
CREATE TABLE products (
    name VARCHAR(100) COLLATE utf8mb4_unicode_ci
);

-- 中文、日文、韩文等都能正确排序
SELECT * FROM products ORDER BY name;

场景三：性能优先

-- _general_ci 比 _unicode_ci 更快，但排序准确性稍低
CREATE TABLE logs (
    message VARCHAR(1000) COLLATE utf8mb4_general_ci
);

排序规则对比：

排序规则性能准确性适用场景
utf8mb4_general_ci高一般简单应用，性能优先
utf8mb4_unicode_ci中好多语言应用
utf8mb4_0900_ai_ci中最好MySQL 8.0+ 默认，推荐使用
utf8mb4_bin高精确需要区分大小写

排序规则对查询的影响​

-- 创建测试表
CREATE TABLE test_collation (
    id INT PRIMARY KEY,
    name VARCHAR(50) COLLATE utf8mb4_0900_ai_ci
);

INSERT INTO test_collation VALUES 
(1, 'Apple'),
(2, 'apple'),
(3, 'APPLE'),
(4, '香蕉'),
(5, '苹果');

-- 不区分大小写查询
SELECT * FROM test_collation WHERE name = 'apple';
-- 返回 id=1, 2, 3 的所有记录

-- 区分大小写查询（使用 BINARY）
SELECT * FROM test_collation WHERE BINARY name = 'apple';
-- 只返回 id=2

-- 使用 COLLATE 子句临时指定排序规则
SELECT * FROM test_collation 
WHERE name COLLATE utf8mb4_bin = 'apple';
-- 只返回 id=2

-- 排序结果
SELECT * FROM test_collation ORDER BY name;
-- 结果：Apple, apple, APPLE, 香蕉, 苹果（不区分大小写排序）

字符集设置层级​

MySQL 支持在多个层级设置字符集和排序规则，层级从高到低为：

列级设置（优先级最高）
    ↓
表级设置
    ↓
数据库级设置
    ↓
服务器级设置（优先级最低）

服务器级别​

服务器级别的字符集是所有数据库的默认值：

-- 查看服务器字符集设置
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

-- 启动时设置（my.cnf 或 my.ini）
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

-- 运行时修改（需要 SUPER 权限）
SET GLOBAL character_set_server = utf8mb4;
SET GLOBAL collation_server = utf8mb4_0900_ai_ci;

数据库级别​

-- 创建数据库时指定
CREATE DATABASE mydb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

-- 修改数据库字符集
ALTER DATABASE mydb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

-- 查看数据库字符集
SHOW CREATE DATABASE mydb;

-- 查看当前数据库设置
SELECT 
    @@character_set_database,
    @@collation_database;

注意：修改数据库字符集不会自动修改已有表的字符集，只影响新创建的表。

表级别​

-- 创建表时指定
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 修改表字符集
ALTER TABLE users 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

-- 查看表字符集
SHOW CREATE TABLE users;

注意：修改表字符集不会自动修改已有列的字符集，只影响新添加的列。

列级别​

列级别的设置优先级最高：

-- 创建表时指定列字符集
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    description TEXT CHARACTER SET utf8mb4,
    code VARCHAR(20) CHARACTER SET latin1  -- 特殊需求
);

-- 修改列字符集
ALTER TABLE products 
MODIFY name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

-- 转换列字符集（同时转换数据）
ALTER TABLE products 
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

连接字符集​

连接字符集影响客户端与服务器之间的通信：

-- 查看连接相关字符集
SHOW VARIABLES LIKE 'character_set%';

-- 输出示例：
-- character_set_client     = utf8mb4    -- 客户端发送的语句字符集
-- character_set_connection = utf8mb4    -- 连接字符集
-- character_set_database   = utf8mb4    -- 当前数据库字符集
-- character_set_results    = utf8mb4    -- 返回结果的字符集
-- character_set_server     = utf8mb4    -- 服务器默认字符集
-- character_set_system     = utf8       -- 系统元数据字符集（固定）

-- 设置连接字符集
SET NAMES utf8mb4;  -- 同时设置 client、connection、results

-- 或分别设置
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

配置文件设置：

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

中文支持最佳实践​

推荐配置​

对于中文应用，推荐以下配置：

-- 创建数据库
CREATE DATABASE app_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

-- 创建表
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

排序规则选择​

对于中文数据，排序规则的选择主要看是否需要区分大小写：

-- 场景一：不需要区分大小写（推荐）
-- 使用 utf8mb4_0900_ai_ci（MySQL 8.0+ 默认）
CREATE TABLE users (
    username VARCHAR(50)  -- 'Admin' 和 'admin' 视为相同
);

-- 场景二：需要区分大小写
-- 使用 utf8mb4_bin 或查询时使用 BINARY
CREATE TABLE users (
    username VARCHAR(50) COLLATE utf8mb4_bin
);

-- 或查询时区分
SELECT * FROM users WHERE BINARY username = 'Admin';

中文排序问题​

MySQL 的 Unicode 排序规则对中文的排序是按 Unicode 编码顺序：

-- 创建测试数据
CREATE TABLE chinese_test (
    name VARCHAR(50)
) CHARACTER SET utf8mb4;

INSERT INTO chinese_test VALUES 
('张三'), ('李四'), ('王五'), ('赵六'), ('陈七');

-- 默认排序（按 Unicode 编码）
SELECT name FROM chinese_test ORDER BY name;
-- 结果：李四、王五、张三、赵六、陈七

-- 按拼音排序（需要特定排序规则或函数）
SELECT name FROM chinese_test 
ORDER BY CONVERT(name USING gbk);
-- 结果：陈七、李四、王五、张三、赵六

按拼音排序的实现方式：

-- 方式一：使用 GBK 字符集排序
SELECT name FROM chinese_test 
ORDER BY CONVERT(name USING gbk);

-- 方式二：存储拼音首字母
ALTER TABLE chinese_test ADD COLUMN pinyin VARCHAR(10);
UPDATE chinese_test SET pinyin = 'Z';  -- 张三
-- 使用拼音首字母排序
SELECT name FROM chinese_test ORDER BY pinyin;

字符集转换​

转换已有数据​

将已有表的字符集转换为 utf8mb4：

-- 1. 查看当前字符集
SHOW CREATE TABLE mytable;

-- 2. 转换表字符集（包括数据）
ALTER TABLE mytable 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

-- 3. 转换数据库中所有表
-- 生成转换脚本
SELECT CONCAT(
    'ALTER TABLE ', TABLE_NAME, 
    ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;'
) AS sql_statement
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' 
  AND TABLE_TYPE = 'BASE TABLE';

-- 4. 执行生成的语句

转换注意事项​

-- 警告：转换可能导致数据丢失！

-- 1. 先备份数据
-- mysqldump -u root -p mydb > backup.sql

-- 2. 检查是否有不兼容的字符
SELECT * FROM mytable 
WHERE HEX(content) REGEXP '^[^0-9A-Fa-f]';

-- 3. 转换特定列
ALTER TABLE mytable 
MODIFY content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

字符集转换函数​

-- CONVERT 函数：转换字符集
SELECT CONVERT('中文' USING utf8mb4);

-- CAST 函数：指定字符集
SELECT CAST('中文' AS CHAR CHARACTER SET utf8mb4);

-- HEX 函数：查看十六进制编码
SELECT HEX('中');  -- E4B8AD

-- UNHEX 函数：从十六进制还原
SELECT UNHEX('E4B8AD');  -- 中

常见问题与解决方案​

问题一：中文乱码​

原因分析：字符集不匹配，通常是因为：

  1. 表或列使用了 latin1 字符集
  2. 连接字符集与数据字符集不一致
  3. 客户端显示字符集设置错误

解决方案：

-- 1. 检查各层级的字符集设置
SHOW VARIABLES LIKE 'character%';
SHOW CREATE TABLE your_table;

-- 2. 设置连接字符集
SET NAMES utf8mb4;

-- 3. 修复表的字符集
ALTER TABLE your_table 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

-- 4. 如果数据已经乱码，需要修复
-- 假设 latin1 存储了 utf8mb4 数据
ALTER TABLE your_table MODIFY content BLOB;
ALTER TABLE your_table 
MODIFY content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

问题二：Emoji 无法存储​

原因：使用了 utf8（即 utf8mb3）字符集，无法存储 4 字节的 Emoji。

解决方案：

-- 1. 转换为 utf8mb4
ALTER TABLE your_table 
MODIFY content VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 2. 确保连接也使用 utf8mb4
SET NAMES utf8mb4;

问题三：索引长度超限​

原因：utf8mb4 每字符最多 4 字节，VARCHAR(255) 可能超过索引长度限制。

解决方案：

-- 方法一：限制索引长度
CREATE TABLE users (
    email VARCHAR(255),
    INDEX idx_email (email(191))  -- 191 × 4 = 764 字节
);

-- 方法二：使用前缀索引
CREATE INDEX idx_email ON users(email(100));

-- 方法三：MySQL 5.7+ 启用 innodb_large_prefix
SET GLOBAL innodb_large_prefix = ON;
SET GLOBAL innodb_file_format = 'Barracuda';

-- MySQL 8.0 默认已启用大索引前缀

问题四：查询不区分大小写​

原因：默认的 _ci 排序规则不区分大小写。

解决方案：

-- 方法一：使用 BINARY 关键字
SELECT * FROM users WHERE BINARY username = 'Admin';

-- 方法二：使用 _bin 排序规则
SELECT * FROM users 
WHERE username COLLATE utf8mb4_bin = 'Admin';

-- 方法三：修改列的排序规则
ALTER TABLE users 
MODIFY username VARCHAR(50) COLLATE utf8mb4_bin;

问题五：导入数据乱码​

解决方案：

# 导出时指定字符集
mysqldump -u root -p --default-character-set=utf8mb4 mydb > backup.sql

# 导入时指定字符集
mysql -u root -p --default-character-set=utf8mb4 mydb < backup.sql

最佳实践总结​

配置清单​

# my.cnf / my.ini 配置

[mysqld]
# 服务器字符集
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

# 初始化连接字符集
init_connect='SET NAMES utf8mb4'

[client]
# 客户端默认字符集
default-character-set=utf8mb4

[mysql]
# 命令行客户端字符集
default-character-set=utf8mb4

开发规范​

  1. 统一使用 utf8mb4：新项目全部使用 utf8mb4，避免 utf8 别名
  2. 明确指定字符集：创建数据库和表时显式指定字符集
  3. 连接字符集一致：确保应用连接时设置正确的字符集
  4. 测试中文和 Emoji：开发阶段测试各种字符的存储和查询
  5. 注意索引长度：utf8mb4 下 VARCHAR 索引注意长度限制

迁移检查清单​

  • 备份数据
  • 检查当前字符集配置
  • 确认没有使用 utf8 别名
  • 测试转换脚本
  • 执行字符集转换
  • 验证数据完整性
  • 测试应用功能
  • 更新配置文件
  • 监控运行状态

小结​

本章我们学习了：

  1. 基本概念：字符集定义字符的编码方式，排序规则定义比较和排序规则
  2. 字符集选择：永远使用 utf8mb4，不要使用 utf8（utf8mb3）
  3. 排序规则：MySQL 8.0 默认 utf8mb4_0900_ai_ci，支持多语言
  4. 设置层级：服务器、数据库、表、列四级设置，列级优先级最高
  5. 中文支持：使用 utf8mb4，注意排序规则选择和拼音排序实现
  6. 常见问题：乱码、Emoji、索引长度、大小写敏感的处理方法

练习​

  1. 创建一个支持中文和 Emoji 的数据库和表
  2. 测试不同排序规则下的大小写敏感性
  3. 实现一个按拼音首字母排序的查询
  4. 将一个 latin1 字符集的表转换为 utf8mb4

参考资料​