第14章：字符集与排序规则
第14章：字符集与排序规则
正确处理中文和emoji的关键
14.1 字符集概述
14.1.1 什么是字符集？
定义：
- 字符集（Character Set）：字符的编码方式
- 排序规则（Collation）：字符的比较规则
常用字符集：
latin1：西欧字符（MySQL默认，已过时）gbk：简体中文utf8：UTF-8编码（最多3字节，不支持emoji）⚠️utf8mb4：UTF-8编码（最多4字节，支持emoji）✅ 推荐
14.2 字符集详解 ⭐⭐⭐⭐⭐
14.2.1 utf8 vs utf8mb4
重要区别：
-- utf8：最多3字节，不支持emoji
-- utf8mb4：最多4字节，支持emoji
-- ❌ utf8无法存储emoji
CREATE TABLE test_utf8 (
content VARCHAR(100) CHARACTER SET utf8
);
INSERT INTO test_utf8 VALUES ('😀'); -- 报错
-- ✅ utf8mb4可以存储emoji
CREATE TABLE test_utf8mb4 (
content VARCHAR(100) CHARACTER SET utf8mb4
);
INSERT INTO test_utf8mb4 VALUES ('😀'); -- 成功
推荐：
- ✅ 统一使用utf8mb4
- ❌ 不要使用utf8
14.2.2 查看字符集
查看系统字符集：
-- 查看所有字符集
SHOW CHARACTER SET;
-- 查看字符集变量
SHOW VARIABLES LIKE 'character%';
-- 输出：
-- character_set_client: utf8mb4 -- 客户端字符集
-- character_set_connection: utf8mb4 -- 连接字符集
-- character_set_database: utf8mb4 -- 数据库字符集
-- character_set_results: utf8mb4 -- 结果字符集
-- character_set_server: utf8mb4 -- 服务器字符集
-- character_set_system: utf8 -- 系统字符集（固定）
查看表字符集：
-- 查看表字符集
SHOW CREATE TABLE users;
-- 查看列字符集
SHOW FULL COLUMNS FROM users;
14.2.3 设置字符集
服务器级别：
# my.cnf
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[client]
default-character-set = utf8mb4
数据库级别：
-- 创建数据库时指定
CREATE DATABASE mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
-- 修改数据库字符集
ALTER DATABASE mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
表级别：
-- 创建表时指定
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 修改表字符集
ALTER TABLE users
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
列级别：
-- 创建列时指定
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);
-- 修改列字符集
ALTER TABLE users
MODIFY COLUMN name VARCHAR(100)
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
14.3 排序规则（Collation）⭐⭐⭐⭐
14.3.1 常用排序规则
utf8mb4排序规则：
utf8mb4_general_ci：通用，不区分大小写，性能好utf8mb4_unicode_ci：Unicode标准，区分重音，性能稍差 ✅ 推荐utf8mb4_bin：二进制，区分大小写
示例：
-- utf8mb4_general_ci：不区分大小写
SELECT 'A' = 'a' COLLATE utf8mb4_general_ci; -- 1（相等）
-- utf8mb4_bin：区分大小写
SELECT 'A' = 'a' COLLATE utf8mb4_bin; -- 0（不相等）
14.3.2 排序规则对比
示例：
-- 创建测试表
CREATE TABLE test_collation (
name VARCHAR(100)
) CHARACTER SET utf8mb4;
INSERT INTO test_collation VALUES ('A'), ('a'), ('B'), ('b');
-- utf8mb4_general_ci：不区分大小写
SELECT * FROM test_collation ORDER BY name COLLATE utf8mb4_general_ci;
-- 结果：A, a, B, b 或 a, A, b, B
-- utf8mb4_bin：区分大小写
SELECT * FROM test_collation ORDER BY name COLLATE utf8mb4_bin;
-- 结果：A, B, a, b（ASCII码顺序）
14.3.3 查看排序规则
查看所有排序规则：
-- 查看utf8mb4的排序规则
SHOW COLLATION WHERE Charset = 'utf8mb4';
-- 查看排序规则变量
SHOW VARIABLES LIKE 'collation%';
14.4 字符集转换
14.4.1 转换表字符集
方法1：ALTER TABLE（推荐）
-- 转换表和所有列的字符集
ALTER TABLE users
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
方法2：分步转换
-- 1. 修改表默认字符集
ALTER TABLE users
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
-- 2. 修改每列字符集
ALTER TABLE users
MODIFY COLUMN name VARCHAR(100)
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
14.4.2 转换数据库字符集
步骤：
-- 1. 备份数据
mysqldump -u root -p --default-character-set=utf8mb4 mydb > backup.sql
-- 2. 修改数据库字符集
ALTER DATABASE mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
-- 3. 转换所有表
SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';
-- 执行生成的SQL
14.5 字符集最佳实践
14.5.1 推荐配置
my.cnf配置：
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
应用程序连接：
-- 连接后设置字符集
SET NAMES utf8mb4;
-- 或在连接字符串中指定
jdbc:mysql://localhost:3306/mydb?characterEncoding=utf8mb4
14.5.2 常见问题
问题1：中文乱码
-- 原因：字符集不一致
-- 解决：统一使用utf8mb4
-- 检查字符集
SHOW VARIABLES LIKE 'character%';
-- 设置字符集
SET NAMES utf8mb4;
问题2：emoji无法存储
-- 原因：使用utf8而不是utf8mb4
-- 解决：转换为utf8mb4
ALTER TABLE users
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
问题3：索引长度超限
-- 错误：Specified key was too long; max key length is 767 bytes
-- 原因：utf8mb4每字符4字节，VARCHAR(255)需要1020字节
-- 解决方案1：减少长度
CREATE TABLE users (
email VARCHAR(191) PRIMARY KEY -- 191*4=764字节
) CHARACTER SET utf8mb4;
-- 解决方案2：使用前缀索引
CREATE TABLE users (
email VARCHAR(255),
INDEX idx_email (email(191))
) CHARACTER SET utf8mb4;
-- 解决方案3：开启innodb_large_prefix（MySQL 5.7+默认开启）
SET GLOBAL innodb_large_prefix = ON;
SET GLOBAL innodb_file_format = Barracuda;
14.6 本章总结
本章学习内容：
- ✅ 字符集概述
- ✅ 字符集详解（utf8 vs utf8mb4）⭐⭐⭐⭐⭐
- ✅ 排序规则（Collation）⭐⭐⭐⭐
- ✅ 字符集转换
- ✅ 字符集最佳实践
重点掌握：
- 统一使用utf8mb4
- utf8不支持emoji，utf8mb4支持
- 推荐：utf8mb4_unicode_ci
- SET NAMES utf8mb4
字符集选择：
- ✅ utf8mb4：支持emoji，推荐
- ❌ utf8：不支持emoji，不推荐
- ❌ latin1：不支持中文，不推荐
排序规则选择：
- utf8mb4_unicode_ci：推荐（Unicode标准）
- utf8mb4_general_ci：性能好，但不够准确
- utf8mb4_bin：区分大小写
面试重点：
- utf8和utf8mb4的区别
- 如何解决中文乱码
- 如何存储emoji
- 排序规则有什么作用
- 索引长度超限如何解决
下一章预告： 用户与权限管理
练习题
- utf8和utf8mb4有什么区别？
- 如何查看数据库的字符集？
- 如何设置表的字符集为utf8mb4？
- 什么是排序规则？
- utf8mb4_general_ci和utf8mb4_unicode_ci有什么区别？
- 如何转换表的字符集？
- 为什么会出现中文乱码？
- 如何存储emoji？
- 索引长度超限如何解决？
- 实战：将一个表转换为utf8mb4
继续学习： 第29章：用户与权限管理