MySQL中如何对字母数字列实现正确的自然排序？

你遇到的是字符串字典序排序和自然数值排序的冲突问题——默认排序会逐个字符比较，所以"10"的第一个字符"1"比"5"小，导致50ACT-1A10排在50ACT-1A5前面。要实现目标排序，需要把字符串拆分成多个可按数值排序的部分，以下是主流数据库的解决方案（假设你的字段名为code）：

MySQL/MariaDB 写法 #

ORDER BY
  -- 先按"-"前的前缀排序（比如50ACT）
  SUBSTRING_INDEX(code, '-', 1),
  -- 按"-"后、"A"前的数字排序（比如1、2）
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(code, '-', -1), 'A', 1) AS UNSIGNED),
  -- 按"A"后的数字排序（没有A则按0处理）
  CASE WHEN LOCATE('A', code) > 0 THEN CAST(SUBSTRING(code, LOCATE('A', code)+1) AS UNSIGNED) ELSE 0 END

或者用正则提取更简洁：

ORDER BY
  REGEXP_SUBSTR(code, '^[^-]+'),
  CAST(REGEXP_SUBSTR(code, '-([0-9]+)', 1, 1, '', 1) AS UNSIGNED),
  CAST(REGEXP_SUBSTR(code, 'A([0-9]+)', 1, 1, '', 1) AS UNSIGNED)

PostgreSQL 写法 #

ORDER BY
  split_part(code, '-', 1),
  -- 拆分出"-"后、"A"前的数字并转成整数
  (split_part(split_part(code, '-', 2), 'A', 1))::integer,
  -- 提取"A"后的数字并转成整数（无A则按0处理）
  CASE WHEN code LIKE '%A%' THEN (substring(code from 'A(\d+)'))::integer ELSE 0 END

SQL Server 写法 #

ORDER BY
  LEFT(code, CHARINDEX('-', code) - 1),
  -- 提取"-"后、"A"前的数字转整数
  CAST(SUBSTRING(code, CHARINDEX('-', code) + 1, CHARINDEX('A', code + 'A') - CHARINDEX('-', code) - 1) AS INT),
  -- 提取"A"后的数字转整数（无A则按0处理）
  CASE WHEN CHARINDEX('A', code) > 0 THEN CAST(SUBSTRING(code, CHARINDEX('A', code) + 1, LEN(code)) AS INT) ELSE 0 END

核心逻辑是把字符串拆分成前缀、-后的主数字、A后的子数字三个部分，将数字部分转换为数值类型后排序，就能实现你要的自然顺序。

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