★ FEATURED ARTICLE 说实话很多来看这个话题的人都是被标题里的“吃”字吸引过来的。我先把结论放在前面utf8mb4_general_ci 和 utf8mb4_bin 最核心的区别一句话就是“一个不区分大小写一个区分大小写”。但如果你以为只有这一个区别直接在业务里乱用后面踩的坑大概率会让你想把自己吃下去重来一次。用户名在 general_ci 下可能“撞车”优惠码在 bin 下排序排得让人一头雾水两个表的排序规则不一致直接抛 Illegal mix of collations 错误甚至唯一索引在 general_ci 下会拦下你本来想放进去的数据……这些真实发生过的生产问题都藏在这两个排序规则的行为差异里。这篇文章我会从原理到实测 SQL再到选型建议和排错经验把这两个排序规则彻底掰开揉碎讲清楚保证你看完不需要吃任何东西。1. 从一条“诡异”的登录 SQL 说起假设你有一个用户表创建的时候用了默认的 utf8mb4_general_ci里面存了一个用户名为Admin的账号。然后用户在前端输入admin去登录执行下面这条 SQLSELECT * FROM users WHERE username admin;结果这个账号居然被查出来了。如果你还加了唯一索引更麻烦的情况是想注册admin的用户会被告知“用户名已被占用”哪怕你数据库里根本没有小写的admin。这就是 general_ci 的“ci”在做怪。它把A和a当成同一个字符看待。那换成 bin 呢SELECT * FROM users WHERE username admin COLLATE utf8mb4_bin;这时候查询结果为空因为Admin和admin在 bin 规则下是两个完全不同的字符串。对很多程序猿来说第一次踩到这种坑的时候第一反应是“卧槽我的 SQL 写错了”第二反应才是去查排序规则。所以我觉得有必要先把最基础的概念讲清楚后面所有差异都是由这个概念延伸出来的。1.1 先搞清楚字符集和排序规则是两码事很多人会把字符集charset和排序规则collation混在一起说其实它们是两个层级的配置。字符集解决的是“字符怎么存的”问题。utf8mb4 是一种字符集它把每个字符映射成 1 到 4 个字节存储在数据库中。你可以把字符集理解成仓库里的货架货架的形状决定了你能放什么尺寸的货物。utf8mb4 能放下 emoji、生僻汉字、各种特殊符号因为它支持 4 字节字符。排序规则解决的是“字符怎么比”的问题。同样是两个字符串怎么判断它们相等怎么排先后顺序这就是 collation 干的事。拿仓库来类比货架都有了你还得定规矩是按保质期先后来上架还是按商品编码大小来上架不同规矩同一个仓库里商品的摆放顺序完全不一样。所以字符集决定了你“能存什么”排序规则决定了你“查出来的结果怎么比、怎么排”。如果你在建表的时候只指定了字符集没指定排序规则MySQL 会用一个默认值。在 5.7 时代这个默认值往往是utf8mb4_general_ci在 8.0 时代默认变成了utf8mb4_0900_ai_ci。很多人根本不知道自己的表里用的是什么排序规则这是后面出问题的根源。查看当前表用的什么排序规则非常简单SHOW TABLE STATUS LIKE users;结果里有一列Collation会直接告诉你这个表用的规则。1.2 数据库层面设 utf8mb4到底在设什么你可能会说“我建库的时候直接写了CHARACTER SET utf8mb4不就行了”其实你只设了一半。完整的写法通常是CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;如果你只写了CHARACTER SET utf8mb4MySQL 会用字符集对应的默认排序规则。问题在于不同 MySQL 版本默认值不一样这就导致迁移环境的时候可能本地是 general_ci线上却变成了 0900_ai_ci行为立刻变得不一致。更值得注意的一点是MySQL 里字符集和排序规则是一对多的关系。同一个utf8mb4字符集下面挂着一堆排序规则常见的有utf8mb4_general_ciutf8mb4_binutf8mb4_unicode_ciutf8mb4_0900_ai_ciutf8mb4_0900_bin所以“用 utf8mb4”和“用哪个 utf8mb4 规则”是两件事。搞清楚这个前提我们再来看 general_ci 和 bin 的区别。2. 核心机制ci 和 bin 到底差在哪很多人对这两个规则的认知停留在“ci 不区分大小写bin 区分大小写”这个方向没错但远远不够。因为字符串比较的行为除了大小写还包括重音符号、尾随空格、排序顺序等多个维度。而且 general_ci 的“不敏感”并不是你想象的那种全方位不敏感。2.1 general 的“不敏感”和你以为的不一样先说 ci 的全称case-insensitive即大小写不敏感。general_ci 在处理英文字母时会把A-Z和a-z折叠成同一组字符来比较所以在它的规则下MySQL、mysql、MYsql都是同一个字符串。但这里有个容易误解的地方general_ci 不等于“所有字符都不敏感”。它对重音是敏感的。什么意思在utf8mb4_general_ci下é和e是两个不同的字符Å和A也不相等。这一点和 8.0 的utf8mb4_0900_ai_ci不一样0900_ai_ci里的ai全称是 accent-insensitive重音不敏感在那种规则下é就能等于e。还有更微妙的MySQL 的字符串比较对于带尾随空格的情况默认是忽略的。这两个排序规则都属于 PAD SPACE 族也就是说account和account 在一般等值比较下是相等的。这又是另一个容易埋雷的细节。所以我们可以把 general_ci 的行为总结成一个矩阵行为维度utf8mb4_general_ciutf8mb4_bin大小写是否敏感不敏感敏感重音是否敏感敏感敏感尾随空格是否忽略忽略PAD SPACE忽略PAD SPACE比较方式先大小写折叠再按规则映射比较直接按 UTF-8 字节或 Unicode 码点比较排序结果大小写混排、偏向人类习惯严格按编码值排大写集中在前唯一索引行为Admin和admin会冲突互不冲突可同时存在bin 的全称就是 binary它做的事情非常简单粗暴比较的时候直接看字节序。因为 utf8mb4 的编码和 Unicode 码点是单调映射的所以 bin 规则下比较两个字符串约等于按 Unicode 码点一个个字符去比。2.2 关键差异一览大小写、重音、尾随空格、排序上面那张表信息量已经不小但我觉得还是有必要把每个维度再拆细一点因为你实际写 SQL 的时候这些差异会直接暴露出来。第一个维度是大小写。这是两个规则最直观的区别SELECT mysql MySQL COLLATE utf8mb4_general_ci AS result;结果是 1表示相等。SELECT mysql MySQL COLLATE utf8mb4_bin AS result;结果是 0表示不相等。第二个维度是重音。前面说过general_ci 虽然是ci但它只对大小写不敏感对重音仍然敏感。拿法语字符试一下SELECT é e COLLATE utf8mb4_general_ci AS result;结果是 0é和e不相等。这一点对多语言业务很重要。如果你的系统将来要放法语、德语这类带重音字符的内容又希望重音和不带重音的字符等价那么utf8mb4_general_ci根本不能满足你你得用utf8mb4_0900_ai_ci。第三个维度是尾随空格。这个坑特别隐蔽平时大家几乎不会注意到。PAD SPACE 的意思是比较两个字符串时如果长度不一致MySQL 会把较短字符串的末尾自动补一些空格让两者长度对齐后再比较。所以SELECT mysql mysql COLLATE utf8mb4_general_ci AS result; SELECT mysql mysql COLLATE utf8mb4_bin AS result;这两个查询结果都是 1。看到没有bin 在这个场景下也会“容忍”尾随空格。这其实不符合很多人对 binary 的直觉但 MySQL 对utf8mb4_bin的实现就是 PAD SPACE而不是 NO PAD。如果你想要那种连一个空格都严格区分的效果得用 MySQL 8.0 的utf8mb4_0900_bin那个才是 NO PAD。第四个维度是排序。同样一批数据用不同排序规则得到的顺序完全不同。这个对列表页、分页查询影响很大后面实测部分我会用 SQL 展示。2.3 另外一个容易忽略的版本变化8.0 的 0900 系说实话如果你用的 MySQL 是 8.0 及以上你大概率已经在用utf8mb4_0900_ai_ci而不是utf8mb4_general_ci了只是你没意识到。MySQL 8.0 把默认字符集改成了 utf8mb4默认排序规则改成了utf8mb4_0900_ai_ci。这个 0900 系列基于 Unicode 9.0 标准的排序算法对多语言的支持比 general_ci 完善得多重音不敏感还改成了 NO PAD 行为。这里有个现实问题很多老项目是从 5.7 迁移到 8.0 的表是以前建的排序规则还停留在 general_ci。新表呢默认已经变成 0900_ai_ci。于是同一个数据库里不同表用着不同规则联结查询的时候就会出幺蛾子。这也是我为什么一直建议团队在建表的时候显式写明COLLATE不要让默认值给你暗中做决定。3. 实测演示用 SQL 把两者“打回原形”光讲理论是很虚的我们直接建表、插入数据看这两个排序规则在真实 SQL 里的表现。3.1 准备数据同一张表两个排序规则先建两张结构完全一样、只有排序规则不同的表CREATE TABLE t_general_ci ( name VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ); CREATE TABLE t_bin ( name VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin );然后插入同一批数据INSERT INTO t_general_ci VALUES (Apple), (apple), (Banana), (banana), (admin); INSERT INTO t_bin VALUES (Apple), (apple), (Banana), (banana), (admin);现在我们对这两张表分别查询等值条件和排序结果差异马上就出来了。3.2 等值查询和排序结果实测先看等值查询。在 general_ci 表里执行SELECT * FROM t_general_ci WHERE name APPLE;你会看到结果返回了Apple和apple两行。同样的查询在 bin 表里SELECT * FROM t_bin WHERE name APPLE;结果为空因为APPLE是全大写的和Apple、apple都不同。然后看排序。执行SELECT * FROM t_general_ci ORDER BY name; SELECT * FROM t_bin ORDER BY name;general_ci 的结果大概是这样大小写被折叠对待apple和Apple会排在一起整体视觉上符合人对英文字母表的认知。bin 的结果就完全不一样了。因为大写字母的 Unicode 码点65-90比小写字母的码点97-122小所以所有以大写开头的字符串会全部排在小写开头的字符串前面。你会看到像Apple、Banana先出现然后是apple、banana。这个排序差异在列表页也许无所谓但如果你的业务依赖数据库排序做分页换一个排序规则可能导致同样的数据被重复翻出来或者漏掉对用户体验影响很大。3.3 唯一索引下的“隐形合并”再来做一个实验在 general_ci 字段上建唯一索引然后尝试插入Admin和admin。CREATE TABLE users_general ( username VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci UNIQUE ); INSERT INTO users_general VALUES (Admin); INSERT INTO users_general VALUES (admin);第二条语句会直接报错提示Duplicate entry。原因很清晰general_ci 认为Admin和admin是同一个字符串因此唯一索引判定冲突。同样的操作在 bin 字段上就没任何问题CREATE TABLE users_bin ( username VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin UNIQUE ); INSERT INTO users_bin VALUES (Admin); INSERT INTO users_bin VALUES (admin);两条都能插入成功。这个特性你说它是 bug 还是 feature看业务。如果你希望用户名不允许大小写重复注册那 general_ci 正好帮你省了代码层的逻辑。如果你希望Admin和admin是两个完全不同的账号那你必须用 bin。4. 性能与索引bin 真的更快吗网上一直有一种说法说 bin 比较效率更高推荐凡事都上 bin。这话有依据但也很容易误导人。我们需要把比较成本、索引行为、报错现象分开来看。4.1 比较成本拆解字节比较 vs 规则比较从算法角度说bin 的比较确实更快。因为它的逻辑非常简单拿出两个字符串的 UTF-8 字节序列一个字节一个字节地比大小本质上就是memcmp一类的操作。计算机对这类操作是非常高效的而且几乎不需要额外的状态转换。general_ci 就不一样了。它要先做大小写折叠每个字符可能还要查映射表然后才进入比较逻辑。虽然 MySQL 对这块做了很多性能优化但不管怎么优化计算步骤都比直接字节比较多。所以在纯 CPU 密集、大量字符串比较的场景下bin 有优势。但实际业务系统里查询的瓶颈很少出现在字符串比较这一步更多时候是磁盘 IO、索引回表、网络传输。你用一个带索引的等值查询数据库通过 B-Tree 定位记录比较次数非常有限bin 和 general_ci 的差异完全可以忽略。只有当你需要对超大结果集做ORDER BY或者在大表上频繁做字符串排序时才能感受到一点点性能差别。但为了这点差别去牺牲业务语义完全不值得。4.2 排序规则不一致导致的 Illegal mix 问题这个坑我几乎每年都要帮人排查好几次报错就是那句经典的ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_bin,IMPLICIT) for operation 什么叫 illegal mix举个简单的例子。你有两张表一张t_general_ci的字段是 general_ci另一张t_bin的字段是 bin。你做联结查询SELECT * FROM t_general_ci g JOIN t_bin b ON g.name b.name;MySQL 一看两个字段的排序规则不同不知道按谁的规则来比较直接给你报错。解决办法有几种。最简单的是在比较的时候显式指定规则SELECT * FROM t_general_ci g JOIN t_bin b ON g.name b.name COLLATE utf8mb4_bin;或者反过来SELECT * FROM t_general_ci g JOIN t_bin b ON g.name COLLATE utf8mb4_bin b.name;也可以用CONVERT转换SELECT * FROM t_general_ci g JOIN t_bin b ON CONVERT(g.name USING utf8mb4) COLLATE utf8mb4_bin b.name;临时语法能解决问题但长期来看还是得统一表的排序规则。比较合理的方式是同库同表同一个排序规则除非某个字段有特别明确的使用理由。4.3 索引长度与 utf8mb4 的 191 字符魔咒顺便说一个和排序规则一起出现的高频问题utf8mb4 下建索引字段长度稍微一长就直接报错。utf8mb4 一个字符最多占 4 字节老版本 InnoDB 的索引键最大限制是 767 字节算下来单个索引列最多只能存 191 个字符。所以 5.6、5.7 时代你给一个VARCHAR(255)字段加索引经常踩到Specified key was too long。解决办法要么是用前缀索引CREATE INDEX idx_name ON users (name(191));要么等 MySQL 8.0 用 DYNAMIC 行格式索引键上限提升到 3072 字节这个问题才有所缓解。这跟 general_ci 还是 bin 没有直接关系但只要你把字符集改成 utf8mb4就一定会碰到。很多人在换了排序规则又发现索引失效或建不出来的时候才会想起字节长度这个隐形的天花板。5. 业务选型哪些字段必须 bin哪些适合 general_ci讲完原理和实操最后落到怎么选。我的观点非常简单排序规则是一种业务语义的数据库表达选哪个不是看“哪个更高级”而是看你希望字符串之间怎么比较。5.1 按业务语义一张表做选择不同类型的字段建议直接照下面这个表来。字段类型推荐排序规则原因用户名登录账号general_ci / unicode_ci多数业务不希望出现Admin和admin同时存在大小写不敏感更符合常规体验邮箱general_ci / unicode_ci邮箱规范化时默认不区分大小写能防止同一个邮箱注册多个账号密码/哈希值bin哈希字符串大小写敏感用 ci 会直接让不同哈希值互相匹配Token/API Keybin这类值是精确匹配任何字符差异都有意义订单号/优惠码bin很多编码规则生成时区分大小写排序也应该按精确码值文章标题/标签general_ci 或 0900_ai_ci需要做模糊匹配和展示大小写敏感反而影响体验多语言内容法语、德语0900_ai_ci 优先支持重音不敏感排序也更符合 Unicode 标准内部编码/状态码bin一般由系统生成精确比较与排序更稳妥需要注意用户名选 general_ci 不代表一定安全。如果你还要求用户名里不能有大小写完全相同但视觉不同的字符那就得配合应用层校验或者用更严格的规则。数据库排序规则只是第一道防线不能解决所有问题。5.2 迁移现有表ALTER 或重建的注意事项老项目想从 general_ci 改成 bin或者反过来操作本身不复杂ALTER TABLE users MODIFY username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;也可以直接改表默认ALTER TABLE users DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;注意MODIFY和DEFAULT的区别很大。改了默认值只影响后续新增列已有列不会变。想全表统一就得逐列MODIFY或者直接用ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;这个语句会转换整张表的字符集和排序规则包括所有字符串列。迁移前务必想清楚两个问题。第一排序规则的改变会影响索引比较。如果之前 general_ci 下建立的唯一索引现在改成 bin原本被拦住的大小写重复数据之后可能就能插入了。应用逻辑没变的话你可能在用户登录时突然出现“多个账号对应同一用户名”的诡异现象。第二大表ALTER TABLE会锁表或者消耗大量 IO。虽然 8.0 支持在线 DDL但还是建议在低峰期操作并先用小表演练一遍。我自己的习惯是先备份然后导出一份到测试环境验证数据一致性再在线上执行。5.3 推荐配置建表时就把规则写死我见过太多团队把排序规则的决策完全交给数据库默认值导致建出来的表五花八门。最好的方式是在建表 SQL 里显式写明COLLATE。如果你希望用户名大小写不敏感就写CREATE TABLE users ( username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL );如果你希望密码哈希大小写敏感就写CREATE TABLE user_auth ( password_hash VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL );同一个库中的字段明确自己的语义然后让代码和数据库的规则保持一致这是最省心的做法。6. 真实案例复盘与避坑清单最后这部分我想把自己踩过以及帮人排查过的几个典型问题复盘一下都是真实发生过的事供你对照排查。6.1 案例一用户名大小写锁号有个朋友做社交 App注册时允许用户自己输入用户名表用的是 general_ci。某天客服收到一堆账号登录不上的投诉一查发现是有用户注册了ALice另一个人注册了Alice。因为 general_ci 的唯一索引判定这两个用户名相同后注册的人直接被拒。但问题出在登录侧老用户 A 一直用ALice登录某天改成了alice系统按 general_ci 查居然也能查到账号。看起来是“贴心”可一旦账号走找回密码流程验证逻辑对大小写敏感的字段做了严格匹配就会出现数据库能查到但业务校验不通过的情况。最后我们把用户名字段改成 bin并且在应用层加了一道统一的用户名规范化处理一律转成小写再注册。这样既防止了大小写撞车又保持了用户输入的灵活性。6.2 案例二优惠码唯一索引报错另一个场景是我们公司内部的营销系统发优惠码时用随机生成的字符串格式类似XK7a2BfQ。当时建表用的是 general_ci并且给优惠码字段加了唯一索引。上线第二天运营反馈说系统提示优惠码重复。排查后发现两个批次生成了xK7A2bFQ和XK7a2BfQ这两串在 general_ci 下被判成同一个字符串唯一索引直接拒绝第二条记录。解决办法是把优惠码字段改成 bin然后加一个普通索引不需要唯一唯一性在应用层生成的时候保证。从此再没出过这类问题。6.3 避坑速查清单我把平时最容易踩的点汇总成一张清单你可以直接截图保存风险点现象规避方式登录时大小写误判输入大小写不同也能查到账号业务侧明确用户名是否大小写不敏感选用匹配的 collation唯一索引撞车合法、不同的字符串被判定重复对大小写区分的字段令牌、码、hash用 bin联结查询报 Illegal mixjoin/比较时报错两个字段统一 collation或显式 COLLATE排序结果不符合预期大写字母集中在前分页数据错位预期人类排序用 ci预期精确二进制用 bin尾随空格被忽略保存了带空格的值查询时等效理解 PAD SPACE 行为需要严格比较用 0900_bin迁移后索引失效改 collation 后部分 SQL 不能走索引MODIFY 前做 explain 检查执行计划6.4 一个小技巧用 COLLATE 临时验证问题如果你怀疑线上某个字段的排序规则有问题但不想立刻改表可以用 COLLATE 在查询时临时指定规则先验证行为是否符合预期。打个比方你要验证用户的输入在 bin 规则下是否唯一SELECT username, COUNT(*) FROM users GROUP BY username COLLATE utf8mb4_bin HAVING COUNT(*) 1;这样你就可以快速知道当前数据在更严格的比较规则下有没有隐藏的“重复”。注意这个写法在 GROUP BY 中可能出现索引失效但作为一次性排查工具完全够用。另外千万不要在已经有线上流量的核心表上直接改排序规则。我见过有人觉得 bin 更“高级”顺手把用户表全改了结果应用层还能登录但所有涉及大小写的唯一性逻辑全部失效数据隔天就乱了。改之前先在测试环境完整模拟一遍。我个人用到现在的体会是collation 这个东西平时看不见摸不着一旦出问题往往是那种“每条 SQL 看过去都没毛病但整体行为就是不对”的疑难杂症。与其事后排查不如在建表的时候多想两分钟把每个字段的语义和排序规则对齐。USERNAME 要大小写不敏感就大大方方用 general_ciTOKEN、哈希、优惠码这一类机器生成且大小写敏感的值就直接 bin。规则和业务语义保持一致后面能省掉大把时间。如果你现在正在追查一个莫名其妙的“字符相等但又不相等”的问题先跑一句SHOW CREATE TABLE 表名;看看每个字段的 COLLATE 是什么再对照这篇文章里的行为矩阵去排查八成能定位到根因。
阅读完成 · 觉得有帮助？
