utf8mb4 和 utf8mb4_bin 编码与排序规则有何本质区别？

Question

**常见技术问题：** 在MySQL中，`utf8mb4` 是字符集（Character Set），定义了可存储的字符范围（支持4字节UTF-8编码，含Emoji、古汉字等）；而 `utf8mb4_bin` 是一种**排序规则（Collation）**，表示按字符的二进制码点值逐字节严格比较（区分大小写、重音、变音符号）。二者本质不同：字符集解决“能存什么”，排序规则解决“如何比较和排序”。例如，`utf8mb4_general_ci`（已弃用）不区分大小写且忽略重音，而 `utf8mb4_bin` 中 `'A' ≠ 'a'`、`'é' ≠ 'e'`，索引和WHERE查询行为截然不同。若误将 `utf8mb4_bin` 用于需模糊匹配的业务字段（如用户昵称搜索），会导致查询结果不符合预期；反之，在密码哈希或唯一Token校验中，却正需要其精确二进制语义。理解这一区别，是避免数据检索异常、索引失效及国际化兼容问题的关键基础。

Answer 1

```html

一、基础认知：字符集与排序规则的语义分界

在MySQL中，utf8mb4 是字符集（Character Set），定义数据存储的编码能力——它支持完整的UTF-8四字节编码，可安全存储Emoji（如 🌍、👍）、CJK扩展B/C区古汉字（如 𠜎、𡿨）、数学符号（∑、ℵ）及带组合标记的国际化字符（如 “café” 中的 é）。而 utf8mb4_bin 是排序规则（Collation），不参与存储编码，仅控制比较（=、WHERE、ORDER BY）、索引排序和唯一性校验的语义逻辑。二者属于正交维度：同一字符集可绑定多种Collation（如 utf8mb4_0900_as_cs、utf8mb4_unicode_ci），但一个列只能指定一种Collation。

二、行为差异：从SQL执行层看“相等”的本质

• 二进制精确匹配：utf8mb4_bin 将字符串视为字节数组，逐字节比对ASCII/UTF-8编码值。例如：'A' (0x41) ≠ 'a' (0x61)，'e' (0xC3 0xA9) ≠ 'é' (0xC3 0xA9) —— 注意：此处为示例简化，实际 é 在UTF-8中是单个2字节序列 0xC3 0xA9，而 utf8mb4_bin 对其整体做二进制判等，故 'cafe' = 'café' 恒为 false。
• 语言感知匹配：对比 utf8mb4_unicode_900_as_cs（推荐替代旧版 _ci），它按Unicode标准执行大小写敏感、重音敏感的排序（如 'É' > 'E'），但允许相同基字符+变音符归类为等价（需配合 GENERATE COLLATION 或规范化函数）。

三、典型误用场景与根因分析

业务场景	错误配置	现象	底层原因
用户昵称模糊搜索（LIKE '%admin%'）	列使用 utf8mb4_bin	WHERE nickname LIKE '%Admin%' 无结果	Bin Collation 不进行大小写折叠，索引无法利用前缀通配优化，且隐式转换失效
JWT Token 唯一性约束	列使用 utf8mb4_unicode_ci	插入两个视觉相同但码点不同的Token（如含零宽空格）触发唯一键冲突或漏检	Unicode Collation 执行标准化折叠，掩盖了二进制差异，破坏密码学唯一性语义

四、工程实践：选型决策树与验证方法

graph TD A[字段用途] --> B{是否需密码学级精确性？} B -->|是| C[强制 utf8mb4_bin
+ BINARY 类型提示] B -->|否| D{是否面向终端用户检索？} D -->|是| E[utf8mb4_0900_as_cs
或 utf8mb4_unicode_900_as_cs] D -->|否| F[utf8mb4_0900_ai_ci
兼顾性能与基础国际化] C --> G[验证：SELECT 'A' = 'a' COLLATE utf8mb4_bin → 0] E --> H[验证：SELECT 'café' = 'cafe' COLLATE utf8mb4_0900_as_cs → 0]

五、深度加固：索引、连接与ORM适配要点

当列使用 utf8mb4_bin 时：
• 索引有效性：B+Tree索引仍高效，但 WHERE col LIKE 'abc%' 可用，而 WHERE UPPER(col) = 'ABC' 将导致全表扫描（函数破坏索引）；
• JOIN 行为：若左表用 utf8mb4_bin，右表用 utf8mb4_unicode_ci，MySQL强制转换为较严格Collation（bin），可能引发隐式转换警告及性能下降；
• ORM适配：Hibernate需显式声明 @Column(columnDefinition = "VARCHAR(255) COLLATE utf8mb4_bin")；MyBatis应避免 <if test="name != null">AND name = #{name}</if> 而未指定Collation上下文，建议在JDBC URL追加 connectionCollation=utf8mb4_bin 统一连接级默认。

```