MySQL教程 / 第 140 节

第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章：用户与权限管理