字符集与排序规则

字符集和排序规则是 MySQL 数据库设计的基础，选择不当可能导致乱码、存储空间浪费、性能问题甚至数据丢失。本文将深入解析字符集和排序规则的选择与优化。

一、字符集基础

1.1 什么是字符集

字符集（Character Set）是字符与二进制编码的映射规则：

字符集演变历史：
ASCII (1963) → Latin-1 (1987) → GB2312 (1980) → Unicode (1991) → UTF-8 (1993)

字符 → 编码 → 二进制
'A'  → 65   → 01000001
'中' → 20013 → 11100100 10111000 10101101 (UTF-8)

1.2 常用字符集对比

1.3 UTF8 vs UTF8MB4

-- ⚠️ 注意：MySQL 的 utf8 不是真正的 UTF-8！

-- MySQL utf8：最多 3 字节，不支持 emoji
-- MySQL utf8mb4：最多 4 字节，完整 UTF-8

-- 存储对比
'你'     → utf8: 3 字节，utf8mb4: 3 字节
'😀'     → utf8: ❌ 无法存储，utf8mb4: 4 字节
'🚀🎉'   → utf8: ❌ 无法存储，utf8mb4: 8 字节

-- ✅ 推荐：统一使用 utf8mb4

1.4 字符集查看

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

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

-- 查看表字符集
SHOW TABLE STATUS LIKE 'table_name';

-- 查看列字符集
SHOW FULL COLUMNS FROM table_name;

-- 查看所有可用字符集
SHOW CHARACTER SET;

二、字符集层级

MySQL 字符集配置有四个层级，优先级从高到低：

列字符集 → 表字符集 → 数据库字符集 → 服务器字符集

2.1 服务器层级

# my.cnf 配置
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

-- 查看服务器字符集配置
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

2.2 数据库层级

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

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

2.3 表层级

-- 创建表时指定字符集
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50)
) ENGINE = InnoDB 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 修改表字符集
ALTER TABLE users 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

2.4 列层级

-- 列级别指定字符集（优先级最高）
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  email VARCHAR(100)  -- 继承表字符集
) ENGINE = InnoDB CHARACTER SET utf8mb4;

三、排序规则（Collation）

3.1 什么是排序规则

排序规则（Collation）定义字符比较和排序的规则：

排序规则影响：
1. 字符串比较（=, <, >, LIKE）
2. ORDER BY 排序
3. GROUP BY 分组
4. DISTINCT 去重
5. 索引查找

3.2 排序规则命名规则

utf8mb4_general_ci
└─┬────┘ └─┬───┘ └┬┘
 字符集   语言  特性

后缀说明：
_ci  : Case Insensitive（不区分大小写）
_cs  : Case Sensitive（区分大小写）
_bin : Binary（二进制比较）
_ai  : Accent Insensitive（不区分重音）
_as  : Accent Sensitive（区分重音）

3.3 常用排序规则对比

3.4 排序规则示例

-- 创建测试表
CREATE TABLE test_sort (
  name VARCHAR(50) COLLATE utf8mb4_general_ci
);

INSERT INTO test_sort VALUES 
  ('apple'), ('Apple'), ('APPLE'), ('banana');

-- 不同排序规则的查询结果

-- _ci（不区分大小写）
SELECT * FROM test_sort WHERE name = 'apple';
-- 结果：apple, Apple, APPLE

-- _bin（二进制，区分大小写）
CREATE TABLE test_bin (
  name VARCHAR(50) COLLATE utf8mb4_bin
);
INSERT INTO test_bin VALUES ('apple'), ('Apple'), ('APPLE');

SELECT * FROM test_bin WHERE name = 'apple';
-- 结果：只有 'apple'

3.5 MySQL 8.0 默认排序规则

MySQL 5.7 默认：utf8mb4_general_ci
MySQL 8.0 默认：utf8mb4_0900_ai_ci

0900 表示 Unicode 9.0.0 标准
_ai 表示不区分重音
_ci 表示不区分大小写

-- MySQL 8.0+ 排序规则改进
-- 支持更精确的 Unicode 排序
-- 支持 emoji 排序
-- 支持特殊字符处理

SELECT '😀' < '😁' COLLATE utf8mb4_0900_ai_ci;  -- 正确的 emoji 排序

四、字符集问题与解决

4.1 乱码问题

乱码产生原因：
1. 存储字符集 ≠ 客户端字符集
2. 不同字符集之间转换错误
3. 字符集不支持某些字符（如 emoji）

典型乱码场景

-- 场景 1：客户端与服务器字符集不一致
-- 服务器：utf8mb4
-- 客户端：gbk

-- 插入中文
INSERT INTO users (name) VALUES ('张三');

-- 查询显示乱码
SELECT name FROM users;  -- 显示：å¼ ä¸‰

-- 解决方案：统一字符集
SET NAMES utf8mb4;

-- 场景 2：emoji 存储失败
-- 字符集：utf8（3 字节）

INSERT INTO comments (content) VALUES ('好棒！👍');
-- 错误：Incorrect string value

-- 解决方案：使用 utf8mb4
ALTER TABLE comments CONVERT TO CHARACTER SET utf8mb4;

4.2 字符集转换

-- 查看当前字符集使用情况
SELECT 
  table_name,
  table_collation,
  table_rows,
  data_length
FROM information_schema.tables 
WHERE table_schema = 'your_database';

-- 转换表字符集（会重建表）
ALTER TABLE table_name 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

-- 只修改表默认字符集（不转换现有数据）
ALTER TABLE table_name 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

4.3 转换注意事项

⚠️ 字符集转换风险：
1. 数据丢失（高字节字符集→低字节）
2. 表锁死（大表转换时间长）
3. 索引失效（需要重建索引）
4. 应用兼容性问题

-- 安全转换步骤

-- 1. 备份数据
CREATE TABLE users_backup LIKE users;
INSERT INTO users_backup SELECT * FROM users;

-- 2. 检查是否有不兼容字符
SELECT * FROM users 
WHERE name != CONVERT(name USING utf8mb4);

-- 3. 低峰期执行转换
ALTER TABLE users 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

-- 4. 验证数据
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users_backup;

五、字符集与性能

5.1 存储空间对比

-- 不同字符集存储空间对比（100 万行）

-- ASCII（1 字节/字符）
CREATE TABLE t_ascii (
  name CHAR(10) CHARACTER SET ascii
);
-- 10 字符 = 10 字节

-- Latin-1（1 字节/字符）
CREATE TABLE t_latin1 (
  name CHAR(10) CHARACTER SET latin1
);
-- 10 字符 = 10 字节

-- UTF8MB4（1-4 字节/字符）
CREATE TABLE t_utf8mb4 (
  name VARCHAR(10) CHARACTER SET utf8mb4
);
-- 英文：10 字符 = 10 字节
-- 中文：10 字符 = 30 字节
-- emoji: 10 字符 = 40 字节

5.2 索引长度限制

-- InnoDB 索引前缀限制：767 字节（MySQL 5.6）或 3072 字节（MySQL 5.7+）

-- MySQL 5.6（767 字节限制）
CREATE TABLE t (
  name VARCHAR(255) CHARACTER SET utf8mb4,
  INDEX idx_name (name)  -- ❌ 错误：255*4=1020 > 767
);

-- 解决方案 1：限制字段长度
CREATE TABLE t (
  name VARCHAR(191) CHARACTER SET utf8mb4,  -- 191*4=764 < 767
  INDEX idx_name (name)
);

-- 解决方案 2：使用前缀索引
CREATE TABLE t (
  name VARCHAR(255) CHARACTER SET utf8mb4,
  INDEX idx_name (name(191))
);

-- MySQL 5.7+（3072 字节限制）
CREATE TABLE t (
  name VARCHAR(768) CHARACTER SET utf8mb4,  -- 768*4=3072
  INDEX idx_name (name)
);

5.3 排序规则性能

-- 排序规则性能对比（100 万行）

-- utf8mb4_general_ci（最快）
SELECT * FROM users ORDER BY name;  -- 简单比较

-- utf8mb4_unicode_ci（较慢）
SELECT * FROM users ORDER BY name COLLATE utf8mb4_unicode_ci;  -- Unicode 排序

-- utf8mb4_bin（快）
SELECT * FROM users ORDER BY name COLLATE utf8mb4_bin;  -- 二进制排序

-- 性能差异：
-- general_ci: 基准
-- unicode_ci: 慢 10-20%
-- bin: 与 general_ci 相当

5.4 字符集优化建议

-- ✅ 推荐优化策略

-- 1. 统一使用 utf8mb4
-- my.cnf 配置
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

-- 2. 英文字段使用 ascii（节省空间）
CREATE TABLE users (
  email VARCHAR(100) CHARACTER SET ascii,  -- 邮箱
  phone CHAR(11) CHARACTER SET ascii       -- 手机号
);

-- 3. 需要区分大小写的字段使用 _bin
CREATE TABLE users (
  password_hash VARCHAR(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
);

-- 4. 大字段使用压缩
-- MySQL 8.0+ 支持

六、连接字符集

6.1 连接字符集配置

-- 客户端连接字符集
SET NAMES utf8mb4;

-- 等价于
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

6.2 连接字符集问题

-- 问题：连接字符集与表字符集不一致

-- 表字符集：utf8mb4
-- 连接字符集：gbk

-- 查询时自动转换，可能导致乱码
SELECT name FROM users WHERE name = '张三';

-- 解决方案：应用层统一设置
-- JDBC 连接字符串
jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8mb4

-- PHP PDO
$pdo = new PDO("mysql:host=localhost;dbname=mydb;charset=utf8mb4");

-- Python mysql-connector
mysql.connect(host='localhost', charset='utf8mb4')

6.3 排序规则冲突

-- 问题：不同排序规则的列进行比较

CREATE TABLE t1 (
  name VARCHAR(50) COLLATE utf8mb4_general_ci
);

CREATE TABLE t2 (
  name VARCHAR(50) COLLATE utf8mb4_unicode_ci
);

-- ❌ 错误：排序规则冲突
SELECT * FROM t1 
JOIN t2 ON t1.name = t2.name;

-- 解决方案：统一排序规则
SELECT * FROM t1 
JOIN t2 ON t1.name = t2.name COLLATE utf8mb4_general_ci;

七、最佳实践总结

7.1 字符集选择

-- ✅ 推荐配置（MySQL 8.0+）

-- 服务器配置
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

-- 数据库
CREATE DATABASE mydb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

-- 表（继承数据库配置）
CREATE TABLE users (
  id BIGINT PRIMARY KEY
) ENGINE = InnoDB;

-- 特殊字段
CREATE TABLE users (
  email VARCHAR(100) CHARACTER SET ascii,  -- 英文邮箱
  password_hash VARCHAR(60) COLLATE utf8mb4_bin,  -- 区分大小写
  name VARCHAR(50)  -- 继承表字符集
);

7.2 字符集检查清单

-- 1. 检查服务器字符集
SHOW VARIABLES LIKE 'character_set_server';
-- 期望：utf8mb4

-- 2. 检查数据库字符集
SHOW CREATE DATABASE database_name;
-- 期望：utf8mb4

-- 3. 检查表字符集
SELECT table_name, table_collation 
FROM information_schema.tables 
WHERE table_schema = 'your_database';
-- 期望：utf8mb4_*

-- 4. 检查连接字符集
SHOW VARIABLES LIKE 'character_set_connection';
-- 期望：utf8mb4

-- 5. 检查是否有不一致
SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'your_database'
  AND (character_set_name != 'utf8mb4' OR collation_name NOT LIKE 'utf8mb4%');

7.3 迁移到 utf8mb4

-- 完整迁移脚本

-- 1. 备份所有数据
-- mysqldump -u root -p --all-databases > backup.sql

-- 2. 修改服务器配置
-- my.cnf
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

-- 3. 重启 MySQL
-- sudo systemctl restart mysql

-- 4. 修改数据库
ALTER DATABASE database_name 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

-- 5. 修改表（逐表执行）
ALTER TABLE table_name 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

-- 6. 验证
SHOW FULL COLUMNS FROM table_name;

总结

字符集和排序规则选择原则：

1. 统一使用 utf8mb4：支持全球字符和 emoji
2. MySQL 8.0+ 使用 utf8mb4_0900_ai_ci：更好的 Unicode 支持
3. 英文字段可用 ascii：节省存储空间
4. 密码等字段用 _bin：需要区分大小写
5. 连接字符集保持一致：避免乱码

存储效率（100 万行，10 字符）：

• ascii: 10MB
• latin1: 10MB
• utf8mb4（英文）: 10MB
• utf8mb4（中文）: 30MB
• utf8mb4（混合）: 约 20MB

性能影响：

• general_ci: 最快，但不精确
• unicode_ci: 慢 10-20%，更精确
• 0900_ai_ci: MySQL 8.0 优化后性能接近 general_ci

下一篇：MySQL 8.0 新特性 - 全面解析窗口函数、CTE、JSON 增强等 MySQL 8.0 核心新特性