SQL非统一格式字符串alphanumerical排序实现方案咨询

你遇到的是典型的**自然排序（人类友好排序）**需求，默认的ORDER BY走的是字典序，会逐字符对比ASCII值，所以会出现2排在12后面、11排在100后面的问题，解决核心是把字符串中的数字段按数值排序，文本段按字典序排序。

不同场景实现方案 #

1. MySQL 数据库场景 #

SELECT 你的字段名
FROM 你的表名
ORDER BY
  -- 优先把数字开头的条目排在前面
  IF(你的字段名 REGEXP '^[0-9]', 0, 1),
  -- 数字开头的条目，提取前缀数字转数值排序
  IF(你的字段名 REGEXP '^[0-9]', CAST(SUBSTRING_INDEX(你的字段名, '-', 1) AS UNSIGNED), 0),
  -- 数字开头的条目，横杠后的部分按字符串排序
  IF(你的字段名 REGEXP '^[0-9]', SUBSTRING_INDEX(你的字段名, '-', -1), ''),
  -- 非数字开头的条目，提取横杠后的数字转数值排序
  CAST(SUBSTRING_INDEX(你的字段名, '-', -1) AS UNSIGNED),
  -- 兜底排序，适配100A这类带字母后缀的场景
  你的字段名;

2. PostgreSQL 数据库场景 #

SELECT 你的字段名
FROM 你的表名
ORDER BY
  -- 优先把数字开头的条目排在前面
  (你的字段名 ~ '^[0-9]') DESC,
  -- 数字开头的条目，提取前缀数字转数值排序
  CASE WHEN 你的字段名 ~ '^[0-9]' THEN split_part(你的字段名, '-', 1)::integer END,
  -- 拆分横杠后的内容，先按文本部分排序
  regexp_replace(split_part(你的字段名, '-', -1), '[0-9]', '', 'g'),
  -- 再按数字部分转数值排序
  regexp_replace(split_part(你的字段名, '-', -1), '[^0-9]', '', 'g')::integer,
  -- 兜底排序
  你的字段名;

3. 非数据库（代码处理）场景 #

以Python为例，可以直接用成熟的自然排序库实现，无需自己写拆分逻辑：

from natsort import natsorted

raw_data = [
    "12-ABC",
    "1-ABC",
    "ABC-10",
    "ABC-11",
    "ABC-100",
    "2-ABCD",
    "ABC-100A"
]

sorted_data = natsorted(raw_data)

运行后得到的sorted_data就是你需要的预期排序结果。

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