MySQL JSON字符串的大小写与重音不敏感排序实现方法

这个问题很常见——MySQL对JSON字段提取出的字符串默认使用utf8mb4_bin排序规则，这种规则会严格区分大小写和重音，所以才会出现你看到的不符合预期的排序结果。要改成utf8mb4_unicode_ci规则排序，有两种实用的方法：

方法1：查询时直接转换排序规则 #

你可以在ORDER BY子句中，通过CONVERT函数指定字符集，再用COLLATE指定目标排序规则：

SELECT `data`->>'$.name' AS `name` 
FROM `my_table` 
ORDER BY CONVERT(`data`->>'$.name' USING utf8mb4) COLLATE utf8mb4_unicode_ci ASC;

这里的CONVERT(...) USING utf8mb4确保提取出的字符串字符集与目标排序规则匹配，COLLATE utf8mb4_unicode_ci则让排序按照unicode的规则进行（忽略重音和大小写差异，符合常规的自然语言排序逻辑）。

方法2：创建生成列优化性能 #

如果需要频繁按这个规则排序，推荐给表添加一个存储型生成列，提前指定好排序规则，这样查询时无需每次转换，性能更优：

ALTER TABLE `my_table` 
ADD COLUMN `name_normalized` VARCHAR(255) 
GENERATED ALWAYS AS (`data`->>'$.name') STORED 
COLLATE utf8mb4_unicode_ci;

添加完成后，后续查询直接按这个生成列排序即可：

SELECT `name_normalized` AS `name` 
FROM `my_table` 
ORDER BY `name_normalized` ASC;

两种方法都能让你得到符合预期的排序结果：

脿 l'avenir
je veux
Que 莽a marche

内容的提问来源于stack exchange，提问作者Samuel De Backer