MySQL使用utf8mb4_unicode_ci排序时&优先级高于-异常疑问

问题原因分析 #

你遇到的排序异常是utf8mb4_unicode_ci校对规则的固有特性导致的，和ASCII码值无关：

• 所有后缀为*_unicode_ci的MySQL校对规则，遵循Unicode官方的排序算法（UCA），会按照Unicode标准定义的字符权重值排序，而非直接比较字符的编码数值。在Unicode默认的排序权重定义中，连字符-的排序优先级高于&，因此实际排序时-开头的记录会排在&前面，和你预期的ASCII码值排序逻辑不符。

解决方案 #

如果需要严格按照ASCII码值排序，可以选择两种方案：

1. 排序时临时指定二进制校对规则，无需修改表结构：

-- 方式1：显式指定校对规则
SELECT * FROM products ORDER BY name COLLATE utf8mb4_bin ASC;

-- 方式2：通过BINARY关键字强制按字节比较，效果和上面一致
SELECT * FROM products ORDER BY BINARY name ASC;

2. 直接修改name字段的校对规则为utf8mb4_bin，后续所有该字段的排序/比较都会按字节编码值执行，注意该规则会区分大小写：

ALTER TABLE products MODIFY COLUMN name VARCHAR(255) COLLATE utf8mb4_bin NOT NULL;

验证方法 #

你可以执行以下语句直接验证不同校对规则下两个字符的比较结果：

SELECT 
    '&' < '-' COLLATE utf8mb4_unicode_ci AS unicode_rule_result,
    '&' < '-' COLLATE utf8mb4_bin AS bin_rule_result;

返回结果中unicode_rule_result值为0（代表&大于-，所以-排序更靠前），bin_rule_result值为1（代表&小于-，符合ASCII码值排序逻辑）。

内容的提问来源于stack exchange，提问作者jvndev