MySQL COERCIBILITY 怎样判断表达式使用哪个排序规则

MySQL 判断字符串表达式使用哪个排序规则，关键不是只看列定义，而是看参与运算的每个字符串表达式、字符集、排序规则和 coercibility（可强制转换优先级）。最实用的排查顺序是先用 CHARSET()、COLLATION()、COERCIBILITY() 观察，再根据数值大小判断优先级；数值越小，优先级越高。显式写出的 COLLATE 优先级最高，数值为 0。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/charset-collation-coercibility.html

下面的示例只解释 MySQL 8.4 的表达式规则，不依赖某个业务表。这样遇到“同样是 utf8mb4，比较结果为什么不一样”时，可以先定位表达式规则，而不是盲目修改整张表。

先记住三个判断结论

第一，比较或拼接的结果会从参与运算的字符串中选择一个排序规则。第二，COERCIBILITY() 返回的数值越小，表示该表达式越不容易被另一个排序规则覆盖。第三，显式 COLLATE 可以把比较语义固定在指定规则上，但它必须和表达式的字符集兼容。

先观察表达式的三个属性

排查时不要先猜排序规则名称。把待比较的表达式直接放进三个函数，可以同时看到字符集、排序规则和 coercibility。示例使用字符串字面量，因此不会依赖本地表结构：

-- 观察同一个表达式的字符集、排序规则和优先级
SELECT
    CHARSET(_utf8mb4'Abc') AS expr_charset,
    COLLATION(_utf8mb4'Abc') AS expr_collation,
    COERCIBILITY(_utf8mb4'Abc') AS expr_coercibility;

-- 显式 COLLATE 会把这个表达式的优先级降到 0
SELECT
    COLLATION(_utf8mb4'Abc' COLLATE utf8mb4_bin) AS forced_collation,
    COERCIBILITY(_utf8mb4'Abc' COLLATE utf8mb4_bin) AS forced_coercibility;

CHARSET() 回答“使用哪种字符集”，COLLATION() 回答“使用哪套比较和排序规则”，COERCIBILITY() 回答“在混合表达式中谁更有决定权”。三者不能互相替代：只看字符集名称，无法说明大小写、重音或二进制比较的差异。

为什么列通常会压过字符串字面量

下面的查询把一个列和一个字符串字面量放在比较表达式中。列通常具有 coercibility 2，字面量通常为 4，因此列侧排序规则具有更高优先级。这里的“通常”很重要：如果字面量显式带了 COLLATE，它就变成 0，结果会反过来。

-- 列侧通常比普通字符串字面量更有优先权
SELECT user_name
FROM app_user
WHERE user_name = 'Alice';

-- 把比较两侧的规则和 coercibility 一起列出来
SELECT
    COLLATION(user_name) AS column_collation,
    COERCIBILITY(user_name) AS column_coercibility,
    COLLATION('Alice') AS literal_collation,
    COERCIBILITY('Alice') AS literal_coercibility
FROM app_user
LIMIT 1;

-- 显式规则优先于列侧默认规则，适合表达一次性的比较契约
SELECT user_name
FROM app_user
WHERE user_name COLLATE utf8mb4_bin = _utf8mb4'alice' COLLATE utf8mb4_bin;

最后一个查询把两侧都写成同一字符集和排序规则，读者可以清楚地看到“这次比较要按什么规则进行”。如果业务语义是大小写敏感、按二进制值比较，显式写出来比依赖数据库、表和连接的多级默认值更容易维护。

拼接表达式为什么要单独观察

CONCAT() 会把多个字符串合成一个新表达式。参与拼接的字符串排序规则不同，结果的 coercibility 可能是 1；此时继续观察函数结果，而不是只查看某一个输入列。

-- 先查看拼接结果本身的字符集、排序规则和优先级
SELECT
    CHARSET(CONCAT(first_name, ' ', last_name)) AS full_name_charset,
    COLLATION(CONCAT(first_name, ' ', last_name)) AS full_name_collation,
    COERCIBILITY(CONCAT(first_name, ' ', last_name)) AS full_name_coercibility
FROM app_user
LIMIT 1;

-- 需要稳定比较时，把规则加在最终表达式上
SELECT user_id
FROM app_user
ORDER BY CONCAT(first_name, ' ', last_name) COLLATE utf8mb4_0900_ai_ci;

把 COLLATE 放在最终表达式上，意图比只给某个输入列加规则更直接。实际项目还应确认该排序规则属于当前字符集，避免因为字符集不兼容而得到新的错误。

同样的 coercibility 为什么还可能报错

如果两个操作数的 coercibility 相同，MySQL 还会继续判断字符集和排序规则的关系。两边都是 Unicode 或两边都不是 Unicode 时，如果规则无法合并，可能直接报错；一边是 Unicode、另一边是非 Unicode 时，Unicode 一侧可能胜出并触发自动转换。相同字符集混用 _bin 与 _ci 或 _cs 时，二进制规则可能胜出。

因此，“把两列都改成 utf8mb4”不一定自动解决表达式冲突：utf8mb4 只说明字符集，仍然可能有多个排序规则。可以用下面的查询先把列定义和连接侧默认值放在一起观察：

-- 查看库、表和连接侧的默认字符集与排序规则
SELECT
    @@character_set_connection AS connection_charset,
    @@collation_connection AS connection_collation,
    @@character_set_database AS database_charset,
    @@collation_database AS database_collation;

-- 查看目标列的实际字符集与排序规则
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'app'
  AND TABLE_NAME = 'app_user'
  AND COLUMN_NAME = 'user_name';

这里的查询只是收集输入条件，不会替代对业务比较语义的决定。若确实要改变默认值，应单独设计表结构迁移；若只是某一条查询需要固定语义，优先考虑在最终表达式上使用兼容的显式 COLLATE。

用一张表快速定位

可以把这套排查方式概括成一句话：先用函数看清楚表达式，再用 coercibility 判断默认优先级，最后只在确有业务契约时用 COLLATE 固定规则。不要把比较规则问题和数据迁移问题混成一次修改。

常见问题

COERCIBILITY 数值越大是不是优先级越高？

不是。MySQL 的规则是数值越小，coercibility 越低，优先级越高；显式 COLLATE 的数值 0 最高。

utf8mb4 相同，为什么还要看 COLLATION？

字符集决定可表示的字符范围，排序规则决定字符串如何比较和排序。同一个 utf8mb4 字符集可以搭配不同排序规则，大小写、重音和二进制比较语义也会不同。

应该全库统一排序规则，还是每条 SQL 加 COLLATE？

全库统一是结构治理问题，适合迁移窗口和长期规范；单条 SQL 加 COLLATE 是局部语义问题。先确认冲突范围，再选择迁移或局部固定，避免用局部补丁掩盖列定义不一致。