如何在SQL中按字母数字顺序排序JSON_EXTRACT/JSON_VALUE的值

问题描述 #

当前执行以下SQL时，排序结果是MySQL默认的二进制顺序，无法实现字母数字自然排序（如dnn16应排在dnn9之前）：

SELECT * 
FROM config_server_db.configuration_item 
where configuration_item.topic_info_id = 96 AND FIND_IN_SET('6f517a22-2df5-4b75-30af-bce2bd7b066a', labels)  
order by JSON_VALUE(configuration_item.cfg_value, '$.\"rows\".\"4af8ecaf-4437-615a-7abd-937cd6883ce6\"') desc; 

预期降序排序结果：

dnn16
dnn13
dnn11
dnn10
dnn9
dnn8
dnn7
dnn6
dnn3 
dnn1

实际二进制排序结果：

dnn9
dnn8
dnn7
dnn6
dnn3
dnn16
dnn13
dnn11
dnn1

cfg_value的JSON结构示例：

{
    "tableId": "6f517a22-2df5-4b75-30af-bce2bd7b066a",
    "rows": {
        "4af8ecaf-4437-615a-7abd-937cd6883ce6": "dnn9"
    }
}

需要支持多种字符串格式的自然排序，包括1dnn、dnn1、abc123gef、abc、123等。

解决方案 #

通过正则表达式拆分字符串中的字母与数字部分，分别按字母前缀、数字数值排序，实现自然排序效果。修改后的SQL如下：

SELECT *,
  JSON_VALUE(configuration_item.cfg_value, '$.\"rows\".\"4af8ecaf-4437-615a-7abd-937cd6883ce6\"') AS sort_val
FROM config_server_db.configuration_item 
WHERE configuration_item.topic_info_id = 96 AND FIND_IN_SET('6f517a22-2df5-4b75-30af-bce2bd7b066a', labels)  
ORDER BY
  -- 提取字符串开头的非数字前缀（如"dnn"、"abc"）
  REGEXP_SUBSTR(sort_val, '^[^0-9]+'),
  -- 提取字符串中的数字部分并转为整数，按数值排序
  CAST(REGEXP_SUBSTR(sort_val, '[0-9]+') AS UNSIGNED) DESC,
  -- 处理无数字的纯字母/纯数字字符串，按原字符串排序
  sort_val DESC;

逻辑说明 #

1. 提取非数字前缀：REGEXP_SUBSTR(sort_val, '^[^0-9]+')匹配字符串开头的所有非数字字符，确保相同前缀的字符串归为一组。
2. 数字数值排序：CAST(REGEXP_SUBSTR(sort_val, '[0-9]+') AS UNSIGNED)提取字符串中的数字部分并转为整数，这样16的数值大于9，降序时dnn16会排在dnn9之前。
3. 兜底处理：最后按sort_val DESC排序，覆盖纯字母、纯数字等无数字前缀/数字部分的场景，保证所有格式的字符串都能正确排序。

注意：该方案依赖MySQL 8.0及以上版本的REGEXP_SUBSTR函数。若使用5.7及以下版本，可通过SUBSTRING结合LOCATE等函数实现类似的字符串拆分逻辑。

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