MySQL中字符集和排序规则选择

一般使用:

  • 字符集:utf8mb4
  • 排序规则:utf8mb4_unicode_ci

utf8和utf8mb4字符集的区别

在 MySQL 中,utf8utf8mb4 都是用于存储 UTF-8 编码的字符集,但它们有一个关键区别:

1. utf8 不是完整的 UTF-8

MySQL 里的 utf8 其实是一个不完整的 UTF-8 实现,它最多只能存储 3 字节的字符,因此无法存储一些 4 字节的 Unicode 字符,如:

  • 部分 emoji(😄、🚀、❤️)
  • 某些生僻汉字(𠜎、𡃁)
  • 其他特殊符号(𝄞、𝟘)

如果你的数据库使用的是 utf8,而你尝试插入 4 字节字符,就会导致存储失败或数据被截断。

2. utf8mb4 是完整的 UTF-8

utf8mb4UTF-8 Multi-Byte 4)是MySQL 对 UTF-8 的完整实现,它支持所有 UTF-8 字符,包括 4 字节的 Unicode 字符,因此推荐使用 utf8mb4 代替 utf8

3. utf8utf8mb4 的存储对比

字符集 支持字节数 能存储 Emoji? 支持所有 Unicode?
utf8 最多 3 字节 ❌ 不支持 ❌ 不完整
utf8mb4 最多 4 字节 ✅ 支持 ✅ 完整

4. 迁移 utf8utf8mb4

如果你的数据库是 utf8,可以升级到 utf8mb4 以支持完整的 UTF-8:

1
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

同时,建议修改 MySQL 配置(my.cnfmy.ini):

1
2
3
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

5. 结论

结论:如果可能,永远使用 utf8mb4,而不是 utf8

排序规则如何选择

在 MySQL 中,排序规则(Collation)决定了字符串的比较规则和排序方式。不同的排序规则影响字符比较的大小写敏感性重音区别等。以下是选择排序规则的主要依据:


1. 常见的排序规则

排序规则通常以 字符集_规则 的格式命名,例如:

排序规则 说明 大小写敏感 重音敏感
utf8mb4_general_ci 通用的,不区分大小写 ❌(不区分) ❌(不区分)
utf8mb4_unicode_ci 基于 Unicode 规则排序 ❌(不区分) ❌(不区分)
utf8mb4_unicode_520_ci Unicode 5.2 标准,支持最新字符集 ❌(不区分) ❌(不区分)
utf8mb4_bin 二进制比较,完全区分大小写和重音 ✅(区分) ✅(区分)
utf8mb4_general_cs 区分大小写但不区分重音 ✅(区分) ❌(不区分)
utf8mb4_unicode_cs 区分大小写和重音 ✅(区分) ✅(区分)
  • ci(Case Insensitive):不区分大小写abcABC 视为相同)
  • cs(Case Sensitive):区分大小写abcABC 视为不同)
  • bin(Binary):按二进制逐字节比较abcABC,更严格)

2. 如何选择合适的排序规则

(1)推荐使用 utf8mb4_unicode_ci

适用于大多数情况,包括多语言支持性能较好

1
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

(2)如果希望区分大小写

使用 utf8mb4_unicode_cs

1
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_cs;

(3)如果需要最高性能

  • utf8mb4_general_ci 速度稍快(但排序准确性较低)。
  • 适用于大小写不敏感、重音不敏感的场景,如昵称搜索:
1
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

(4)如果需要严格匹配

使用 utf8mb4_bin,适用于密码存储、唯一性检查等

1
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

3. 影响排序规则的 SQL 操作

(1)指定列的排序规则

1
2
3
CREATE TABLE users (
name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

(2)在查询时使用特定排序规则

1
SELECT * FROM users WHERE name = 'Alice' COLLATE utf8mb4_bin;

(3)设置数据库默认排序规则

1
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4. utf8mb4_unicode_ci vs utf8mb4_general_ci

对比项 utf8mb4_unicode_ci utf8mb4_general_ci
排序准确性 ✅ 更符合 Unicode 规则 ❌ 略有偏差
性能 ⏳ 稍慢 ⚡ 稍快
适用场景 推荐使用,适用于大多数文本 可用于简单文本,但不够准确

5. 总结

  • 最佳选择: utf8mb4_unicode_ci(支持 Unicode 规范,大小写不敏感)
  • 区分大小写: utf8mb4_unicode_cs(严格匹配)
  • 最高性能: utf8mb4_general_ci(适用于简单文本)
  • 严格二进制比较: utf8mb4_bin(用于密码存储)

如果你不确定,建议**默认使用 utf8mb4_unicode_ci**!