字符集与校对规则

引言

提出问题

使用MySQL时，经常遇到字符编码问题：

中文乱码：插入"你好"变成"???"
emoji存储失败：插入😀报错
utf8和utf8mb4有什么区别？
为什么查询"a"和"A"结果一样？

这就是字符集（Character Set）和校对规则（Collation）的问题！

字符集基础

什么是字符集？

字符集：定义了字符与二进制编码的映射关系。

示例：

ASCII：A → 0x41（65）
UTF-8：你 → 0xE4BDA0
UTF-8：😀 → 0xF09F9880

MySQL常用字符集

字符集	字节数	支持字符	推荐使用
latin1	1字节	西欧字符	❌ 不支持中文
gbk	1-2字节	简体中文	⚠️ 老项目
utf8	1-3字节	多语言	❌ 不支持emoji
utf8mb4	1-4字节	多语言+emoji	✅ 推荐

utf8 vs utf8mb4

⚠️ 重要：MySQL的utf8不是真正的UTF-8！

utf8：最多3字节，不支持emoji（😀需要4字节）
utf8mb4：最多4字节，支持emoji和生僻字

-- ❌ 错误：utf8存储emoji
CREATE TABLE test (name VARCHAR(50)) CHARACTER SET utf8;
INSERT INTO test VALUES ('你好😀');  -- ERROR 1366: Incorrect string value

-- ✅ 正确：utf8mb4存储emoji
CREATE TABLE test (name VARCHAR(50)) CHARACTER SET utf8mb4;
INSERT INTO test VALUES ('你好😀');  -- 成功

查看字符集

-- 查看数据库字符集
SHOW VARIABLES LIKE 'character_set%';

-- 输出示例：
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| 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;

校对规则（Collation）

什么是校对规则？

校对规则：定义字符的比较和排序规则。

示例：

utf8mb4_general_ci：不区分大小写（a = A）
utf8mb4_bin：区分大小写（a ≠ A）

常用校对规则

校对规则	说明	区分大小写	性能
utf8mb4_general_ci	通用，不区分大小写	❌	快
utf8mb4_unicode_ci	Unicode标准，不区分大小写	❌	较慢
utf8mb4_bin	二进制比较，区分大小写	✅	最快
utf8mb4_0900_ai_ci	MySQL 8.0默认，最准确	❌	中等

实战对比

-- 创建测试表（不区分大小写）
CREATE TABLE test_ci (
  name VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

INSERT INTO test_ci VALUES ('Apple'), ('apple'), ('APPLE');

-- 查询（不区分大小写）
SELECT * FROM test_ci WHERE name = 'apple';
-- 结果：3条记录（Apple, apple, APPLE）

-- 创建测试表（区分大小写）
CREATE TABLE test_bin (
  name VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

INSERT INTO test_bin VALUES ('Apple'), ('apple'), ('APPLE');

-- 查询（区分大小写）
SELECT * FROM test_bin WHERE name = 'apple';
-- 结果：1条记录（apple）

选择建议

场景	推荐校对规则
用户名、邮箱	`utf8mb4_general_ci`（不区分大小写）
密码（已加密）	`utf8mb4_bin`（区分大小写）
文章内容	`utf8mb4_unicode_ci`（准确排序）
新项目（MySQL 8.0+）	`utf8mb4_0900_ai_ci`（默认）

字符集的4个层级

MySQL字符集设置有4个层级：

服务器级 → 数据库级 → 表级 → 列级

1. 服务器级（全局默认）

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

-- 修改（配置文件my.cnf）
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

2. 数据库级

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

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

3. 表级

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

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

4. 列级

-- 列级指定（覆盖表级设置）
CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,  -- 区分大小写
  nickname VARCHAR(50)  -- 使用表级字符集
);

-- 修改列字符集
ALTER TABLE users
  MODIFY COLUMN username VARCHAR(50)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

解决中文乱码

原因分析

乱码通常因为字符集不一致：

客户端 → 连接 → 服务器 → 数据库 → 表 → 列
utf8mb4    utf8    utf8mb4    utf8    latin1  ❌ 不一致导致乱码

解决方案

方案1：修改配置文件（推荐）

# my.cnf
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

重启MySQL：

sudo systemctl restart mysql

方案2：连接时指定字符集

-- 连接时设置
mysql -u root -p --default-character-set=utf8mb4

-- 或连接后执行
SET NAMES utf8mb4;

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

方案3：修改已有数据库和表

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

-- 修改所有表（批量执行）
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

验证字符集

-- 插入中文和emoji
INSERT INTO users (username) VALUES ('张三😀');

-- 查询
SELECT * FROM users;
-- 如果显示正常，说明字符集配置正确

emoji存储问题

问题复现

-- 使用utf8（不支持emoji）
CREATE TABLE test (content VARCHAR(100)) CHARACTER SET utf8;
INSERT INTO test VALUES ('Hello😀');
-- ERROR 1366: Incorrect string value: '\xF0\x9F\x98\x80' for column 'content'

解决方案

-- 使用utf8mb4
CREATE TABLE test (content VARCHAR(100)) CHARACTER SET utf8mb4;
INSERT INTO test VALUES ('Hello😀');
-- Query OK

已有表的迁移

# 1. 导出数据
mysqldump -u root -p --default-character-set=utf8mb4 mydb > backup.sql

# 2. 修改表结构
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4;

# 3. 如果数据已乱码，重新导入
mysql -u root -p --default-character-set=utf8mb4 mydb < backup.sql

实战案例：社交平台用户表

CREATE TABLE social_users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,

  -- 用户名：不区分大小写，方便登录
  username VARCHAR(50) NOT NULL UNIQUE
    CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,

  -- 密码（已加密）：区分大小写
  password VARCHAR(255) NOT NULL
    CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,

  -- 昵称：支持emoji和特殊字符
  nickname VARCHAR(100)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,

  -- 个性签名：支持emoji
  bio TEXT
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入测试数据
INSERT INTO social_users (username, password, nickname, bio) VALUES
  ('zhangsan', SHA2('Password123', 256), '张三😊', '热爱生活💖'),
  ('ZHANGSAN', SHA2('Password456', 256), 'Zhang San', 'Hello World🌍');

-- 查询（用户名不区分大小写）
SELECT * FROM social_users WHERE username = 'ZhangSan';
-- 结果：2条记录（zhangsan和ZHANGSAN）

性能影响

字符集对性能的影响

字符集	存储空间	索引空间	查询性能
latin1	1字节/字符	最小	最快
utf8	1-3字节/字符	中等	较快
utf8mb4	1-4字节/字符	较大	稍慢

校对规则对性能的影响

校对规则	比较方式	性能
utf8mb4_bin	二进制比较	最快
utf8mb4_general_ci	简单规则	较快
utf8mb4_unicode_ci	Unicode标准	较慢

优化建议

-- ❌ 不推荐：所有列都用TEXT
CREATE TABLE articles (
  content TEXT CHARACTER SET utf8mb4  -- 占用大量空间
);

-- ✅ 推荐：根据实际需要选择类型
CREATE TABLE articles (
  title VARCHAR(200) CHARACTER SET utf8mb4,  -- 标题用VARCHAR
  summary VARCHAR(500) CHARACTER SET utf8mb4,  -- 摘要
  content MEDIUMTEXT CHARACTER SET utf8mb4  -- 正文用TEXT
);

最佳实践

新项目推荐配置

# 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

-- 创建数据库
CREATE DATABASE mydb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- 创建表
CREATE TABLE users (
  username VARCHAR(50) COLLATE utf8mb4_general_ci,  -- 不区分大小写
  password VARCHAR(255) COLLATE utf8mb4_bin  -- 区分大小写
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

老项目迁移

-- 1. 备份数据
mysqldump -u root -p --default-character-set=utf8 mydb > backup.sql

-- 2. 修改数据库
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 3. 批量修改表（生成SQL脚本）
SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.tables
WHERE table_schema = 'mydb';

-- 4. 执行生成的SQL

常见问题（FAQ）

Q1：为什么MySQL的utf8不是真正的UTF-8？

A：历史遗留问题。MySQL创建utf8时，UTF-8标准还未完善，只支持到3字节。后来添加了utf8mb4（mb4=most bytes 4）支持4字节。

Q2：如何查看表的字符集？

A：

SHOW CREATE TABLE users;
-- 或
SHOW TABLE STATUS LIKE 'users';

Q3：已有数据如何从utf8迁移到utf8mb4？

A：

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Q4：字符集会影响性能吗？

A：有轻微影响。utf8mb4比utf8慢约10-15%，但为了支持emoji和生僻字，这个代价是值得的。

总结

核心要点

字符集选择：
- 新项目：utf8mb4（支持emoji）
- 老项目：尽快迁移到utf8mb4
校对规则选择：
- 通用场景：utf8mb4_unicode_ci
- 区分大小写：utf8mb4_bin
- MySQL 8.0+：utf8mb4_0900_ai_ci（默认）
4个层级：
- 服务器级 > 数据库级 > 表级 > 列级
- 低层级继承高层级设置
中文乱码解决：
- 统一字符集为utf8mb4
- 连接时指定字符集

记忆口诀

字符集选择：utf8mb4最完美

utf8mb4：支持emoji和生僻字
校对规则：通用用unicode_ci，密码用bin
统一配置：服务器、数据库、表、列

下一步学习

上一篇：《约束与完整性：主键外键唯一非空》
下一篇：《数据导入导出》
返回目录：MySQL从入门到精通

💡 本文是 “MySQL从入门到精通” 系列的第 8 篇（共86篇）。
📚 建议配置好字符集后，测试插入中文和emoji。

引言#

提出问题#

字符集基础#

什么是字符集？#

MySQL常用字符集#

utf8 vs utf8mb4#

查看字符集#

校对规则（Collation）#

什么是校对规则？#

常用校对规则#

实战对比#

选择建议#

字符集的4个层级#

1. 服务器级（全局默认）#

2. 数据库级#

3. 表级#

4. 列级#

解决中文乱码#

原因分析#

解决方案#

方案1：修改配置文件（推荐）#

方案2：连接时指定字符集#

方案3：修改已有数据库和表#

验证字符集#

emoji存储问题#

问题复现#

解决方案#

已有表的迁移#

实战案例：社交平台用户表#

性能影响#

字符集对性能的影响#

校对规则对性能的影响#

优化建议#

最佳实践#

新项目推荐配置#

老项目迁移#

常见问题（FAQ）#

总结#

核心要点#

记忆口诀#

下一步学习#

参考资料#

引言

提出问题

字符集基础

什么是字符集？

MySQL常用字符集

utf8 vs utf8mb4

查看字符集

校对规则（Collation）

什么是校对规则？

常用校对规则

实战对比

选择建议

字符集的4个层级

1. 服务器级（全局默认）

2. 数据库级

3. 表级

4. 列级

解决中文乱码

原因分析

解决方案

方案1：修改配置文件（推荐）

方案2：连接时指定字符集

方案3：修改已有数据库和表

验证字符集

emoji存储问题

问题复现

解决方案

已有表的迁移

实战案例：社交平台用户表

性能影响

字符集对性能的影响

校对规则对性能的影响

优化建议

最佳实践

新项目推荐配置

老项目迁移

常见问题（FAQ）

总结

核心要点

记忆口诀

下一步学习

参考资料