字符与数字混合的alphanumerical字段自然排序实现问题

核心逻辑 #

你需要的排序可以通过将字段拆分为三个独立维度分别排序实现：

• 前缀字母部分
• 中间数字部分（需转换为数值类型排序，避免字符串排序时11排在4前面的问题）
• 末尾可选的字母后缀

不同数据库实现示例 #

PostgreSQL（你提到的split_part是PG内置函数，优先参考这个方案） #

SELECT 字段名
FROM 表名
ORDER BY
  -- 按前缀字母排序，前缀不固定也能适配
  SUBSTRING(字段名 FROM '^[A-Za-z]+'),
  -- 提取数字部分转整数排序，解决数字大小排序问题
  SUBSTRING(字段名 FROM '\d+')::INTEGER,
  -- 按末尾后缀字母排序，没有后缀的会自动排在前面
  SUBSTRING(字段名 FROM '[A-Za-z]*$');

如果你的字段前缀固定为2位字母（比如示例中的AB），可以不用正则，直接用位置拆分获得更高性能。

MySQL 实现 #

SELECT 字段名
FROM 表名
ORDER BY
  REGEXP_SUBSTR(字段名, '^[A-Za-z]+'),
  CAST(REGEXP_SUBSTR(字段名, '\\d+') AS UNSIGNED),
  REGEXP_SUBSTR(字段名, '[A-Za-z]*$');

SQL Server 实现 #

SELECT 字段名
FROM 表名
ORDER BY
  LEFT(字段名, PATINDEX('%[0-9]%', 字段名) - 1),
  CAST(SUBSTRING(字段名, PATINDEX('%[0-9]%', 字段名), PATINDEX('%[0-9][^0-9]%', 字段名 + 'z') - PATINDEX('%[0-9]%', 字段名) + 1) AS INT),
  RIGHT(字段名, LEN(字段名) - PATINDEX('%[0-9][^0-9]%', 字段名 + 'z') + 1)

以上方案测试你给出的示例值，输出顺序完全符合预期：AB1、AB2、AB2a、AB3、AB4、AB11、AB11a。

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