MySQL中字符集和排序规则选择
一般使用:
- 字符集:utf8mb4
- 排序规则:utf8mb4_unicode_ci
utf8和utf8mb4字符集的区别
在 MySQL 中,utf8
和 utf8mb4
都是用于存储 UTF-8 编码的字符集,但它们有一个关键区别:
1. utf8
不是完整的 UTF-8
MySQL 里的 utf8
其实是一个不完整的 UTF-8 实现,它最多只能存储 3 字节的字符,因此无法存储一些 4 字节的 Unicode 字符,如:
- 部分 emoji(😄、🚀、❤️)
- 某些生僻汉字(𠜎、𡃁)
- 其他特殊符号(𝄞、𝟘)
如果你的数据库使用的是 utf8
,而你尝试插入 4 字节字符,就会导致存储失败或数据被截断。
2. utf8mb4
是完整的 UTF-8
utf8mb4
(UTF-8 Multi-Byte 4)是MySQL 对 UTF-8 的完整实现,它支持所有 UTF-8 字符,包括 4 字节的 Unicode 字符,因此推荐使用 utf8mb4
代替 utf8
。
3. utf8
和 utf8mb4
的存储对比
字符集 | 支持字节数 | 能存储 Emoji? | 支持所有 Unicode? |
---|---|---|---|
utf8 |
最多 3 字节 | ❌ 不支持 | ❌ 不完整 |
utf8mb4 |
最多 4 字节 | ✅ 支持 | ✅ 完整 |
4. 迁移 utf8
到 utf8mb4
如果你的数据库是 utf8
,可以升级到 utf8mb4
以支持完整的 UTF-8:
1 | ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
同时,建议修改 MySQL 配置(my.cnf
或 my.ini
):
1 | [mysqld] |
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):不区分大小写(abc
和ABC
视为相同)cs
(Case Sensitive):区分大小写(abc
和ABC
视为不同)bin
(Binary):按二进制逐字节比较(abc
≠ABC
,更严格)
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 | CREATE TABLE users ( |
(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
**!