我要评论建库建表时，character set 和 collate 这两个选项总是挨在一起出现，长得又像，很多人建库时随手一选就过去了。直到某天线上一个简单的 left join 报错：illegal mix of collations (utf8mb4_general_ci,implicit) and (utf8mb4_unicode_ci,implicit) for operation '='——两张表"字符集明明一样"，为什么还是冲突？这个问题的根因，就是没搞清楚字符集和排序规则的区别。
一、先给结论：它俩根本不是一回事
很多人以为"字符集和排序规则"是同一个东西的两种叫法，其实它们解决的是两个完全不同的问题：
| 概念 | 英文 | 解决的问题 | 通俗类比 |
|---|---|---|---|
| 字符集 | character set | 数据库能认识哪些字 | 字典里收录了哪些字 |
| 排序规则 | collation | 这些字怎么比大小、怎么排序 | 字典按什么顺序排列、怎么查 |
一句话记住：字符集管"能不能存"，排序规则管"怎么比"。
而且它们是一对多的关系——一个字符集可以搭配多套排序规则。就像同一本字典，可以按拼音排、按笔画排、按部首排。字典里的字没变（字符集），但排列和查找的规矩变了（排序规则）。
二、一个例子看懂区别
同样是 utf8mb4 字符集，下面几套排序规则对 'a' 和 'a' 的判断结果完全不同：
-- utf8mb4_general_ci：不区分大小写 select 'a' = 'a' collate utf8mb4_general_ci; -- 结果：1（相等） -- utf8mb4_bin：按二进制编码比较 select 'a' = 'a' collate utf8mb4_bin; -- 结果：0（不相等，'a'=65, 'a'=97） -- utf8mb4_0900_as_cs：区分重音、区分大小写 select 'a' = 'a' collate utf8mb4_0900_as_cs; -- 结果：0（不相等）
字符集相同，排序规则不同，比较结果就可能完全相反。这就是为什么两张表"字符集一样"却依然会报排序规则冲突。
三、表情符号：字符集层面的"能力测试"
最直观体现字符集区别的场景，就是 emoji 表情。
mysql 里的 utf8 是一个"阉 割版"——它最多只支持 3 个字节的字符。而 emoji、部分生僻汉字、历史文字等，需要 4 个字节才能编码。
-- 用 utf8 建表，插入 emoji 直接报错
create table t_bad (content varchar(100)) charset=utf8;
insert into t_bad values ('🎉');
-- error 1366: incorrect string value: '\xf0\x9f\x8e\x89'
-- 用 utf8mb4 建表，emoji 正常存储
create table t_good (content varchar(100)) charset=utf8mb4;
insert into t_good values ('🎉');
-- query ok
关键记忆点：utf8mb4 的 “mb4” 就是 most bytes 4，每个字符最多 4 字节，覆盖完整 unicode 范围（u+0000 ~ u+10ffff）。mysql 8.0 已明确将 utf8 标记为 utf8mb3 的废弃别名，新项目无条件使用 utf8mb4。
四、国家语言：排序规则层面的"精准度测试"
字符集解决了"能不能存"的问题，但多语言场景下，"存进去之后怎么比、怎么排"才是真正的坑。
4.1 德语：'ß'到底等于什么？
德语里有一个特殊字符 ß（eszett），在字典排序中它等价于 ss。但不同的排序规则处理方式完全不同：
-- utf8mb4_general_ci：ß 等于 s（不准确） select 'ß' = 'ss' collate utf8mb4_general_ci; -- 结果：0（不相等） select 'ß' = 's' collate utf8mb4_general_ci; -- 结果：1（错误地相等） -- utf8mb4_unicode_ci：ß 等于 ss（正确） select 'ß' = 'ss' collate utf8mb4_unicode_ci; -- 结果：1（相等）
mysql 官方文档明确指出：utf8mb4_general_ci 对德语和法语"基本可用"，但 ß 被当作 s 而非 ss；如果需要德语字典排序（din-1），必须使用 utf8mb4_unicode_ci。
4.2 日语：平假名和片假名的微妙差异
日语的排序规则更加复杂。unicode 排序算法（uca）的版本直接影响日文假名的排序结果。mysql 官方博客曾用"sushi-beer"的例子说明：旧的 utf8mb4_general_ci 和 utf8mb4_unicode_ci（基于 uca 4.0.0）在日文排序上存在问题，而 mysql 8.0 的 utf8mb4_0900_ai_ci（基于 uca 9.0.0）给出了正确结果。
-- 不同 uca 版本对日文假名的排序可能不同 -- utf8mb4_unicode_ci 基于 uca 4.0.0 -- utf8mb4_0900_ai_ci 基于 uca 9.0.0（mysql 8.0 默认）
4.3 多语言建站的最佳实践
wpml（wordpress 多语言插件）官方建议：多语言站点必须使用 utf8mb4 字符集，配合 unicode 兼容的排序规则，如 utf8mb4_unicode_ci 或 utf8mb4_0900_ai_ci。因为多语言内容包含特殊字符（ñ、é、ö）、非拉丁文字（阿拉伯文、日文、中文）以及 emoji，这些都需要 4 字节支持。
五、排序规则命名后缀速查表
看懂后缀，就基本看懂了这条排序规则的行为：
| 后缀 | 含义 | 通俗解释 |
|---|---|---|
_ci | case insensitive | 不区分大小写，'a' 和 'a' 视为相等 |
_cs | case sensitive | 区分大小写，'a' 和 'a' 不相等 |
_ai | accent insensitive | 不区分重音，'e'、'é'、'è' 视为相等 |
_as | accent sensitive | 区分重音 |
_bin | binary | 直接按编码值比较，'a'（65）小于 'a'（97） |
_ks | kana sensitive | 区分日文假名（平假名/片假名） |
还有个容易忽略的规则：如果排序规则名里没写 _ai 或 _as，那它的重音敏感度跟着大小写敏感度走。_ci 隐含着 _ai，_cs 隐含着 _as。
六、排序规则怎么选：general_ci 还是 unicode_ci？
utf8mb4_general_ci：mysql 早期实现的通用排序规则，没有完全遵循 unicode 标准。比较速度快，但某些特殊字符排序结果"不太对"。
utf8mb4_unicode_ci：基于 unicode collation algorithm（uca）实现，更"正确"，支持扩展映射和多语言精确排序。
现代开发的选择建议：
| mysql 版本 | 推荐排序规则 | 说明 |
|---|---|---|
| 5.7 | utf8mb4_unicode_ci | 基于 uca 4.0.0，比 general_ci 准确 |
| 8.0+ | utf8mb4_0900_ai_ci | 基于 uca 9.0.0，mysql 8.0 默认值 |
cpu 性能早已不是瓶颈，general_ci 那点速度优势在真实业务中几乎感受不到，而排序正确性带来的问题（尤其是多语言用户场景）排查成本高得多。
七、回到那个线上报错
现在再看开头的报错，就很好理解了：
illegal mix of collations (utf8mb4_general_ci) and (utf8mb4_unicode_ci) for operation '='
- 字符集层面：两边都是
utf8mb4，完全一样，没问题 - 排序规则层面：一边
general_ci，一边unicode_ci，规矩不一样
left join 的关联字段两边字符集相同但排序规则不同，mysql 无法确定用哪套"规矩"做等值判断，直接拒绝执行。这正是"字符集管存、排序规则管比"最直观的体现。
解决方案有两种：
方案一（推荐）：统一排序规则。 把所有表的排序规则对齐，从根源上消除冲突。
方案二（临时）：用 collate 显式指定。
select * from table1 t1 left join table2 t2 on t1.username = t2.username collate utf8mb4_unicode_ci;
这样能让查询跑起来，但会破坏索引的使用——collate 改变了比较规则，索引无法被优化器利用。大表关联时性能明显下降，只能作为临时手段。
八、实操建议：建库建表显式声明
养成习惯，显式指定字符集和排序规则，不要依赖默认值。 因为不同 mysql 版本默认值不同，依赖默认值会导致迁移时出现意料之外的排序行为变化。
create database my_app character set utf8mb4 collate utf8mb4_unicode_ci; create table users ( id bigint primary key auto_increment, nickname varchar(64) not null comment '用户昵称，支持emoji', email varchar(128) not null ) engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci;
建表时统一指定，后续新增字段不显式指定会自动继承表的字符集和排序规则，能避免很多关联查询时的意外报错。
九、常用排查命令
-- 查看数据库默认字符集和排序规则 show create database my_app; -- 查看表的字符集和排序规则 show create table users; -- 查看所有可用的 utf8mb4 排序规则 show collation like 'utf8mb4%'; -- 查看某个字段的字符集和排序规则 show full columns from users; -- 查看当前连接使用的字符集 show variables like 'character_set%'; show variables like 'collation%';
十、迁移注意事项
如果已有表需要从 utf8 迁移到 utf8mb4：
-- 会转换表中所有字符类型字段的数据，重量级操作 alter table users convert to character set utf8mb4 collate utf8mb4_unicode_ci;
大表执行前务必：
- 在从库或测试环境先验证
- 评估锁表时间，必要时用
pt-online-schema-change或gh-ost - 确认索引长度限制，
utf8mb4下varchar(255)可能超出 innodb 单列索引 767 字节限制（mysql 5.7+ 默认innodb_large_prefix=on后可支持到 3072 字节）
全文总结：字符集管"能不能存"，排序规则管"怎么比"。字符集选 utf8mb4 才能存 emoji 和多语言字符；排序规则选 utf8mb4_unicode_ci（5.7）或 utf8mb4_0900_ai_ci（8.0），才能正确处理德语 ß、日语假名等语言特性。建库建表时显式声明，不要依赖版本默认值。这些配置写一次，能省下未来无数次排查"乱码"和"排序规则冲突"的时间。
以上就是一文详解mysql建库时的字符集和排序规则的区别的详细内容，更多关于mysql建库字符集和排序规则的资料请关注代码网其它相关文章！
发表评论