如何对MySQL中字母数字组合的字符串字段进行正确排序？

要实现你需要的排序效果（11 < 11a < 12，AB110 < z10）是可行的，但需要自定义排序逻辑——MySQL确实没有内置的自然排序函数。之前用columnName + 0或CAST(columnName AS UNSIGNED)的问题在于，字母开头的字段会被转成0，无法区分它们的字母顺序；数字开头的字段也没法处理后缀字母的排序。

针对MySQL 8.0+的解决方案 #

利用REGEXP_SUBSTR提取字段中的数字和字母部分，分维度排序：

SELECT columnName
FROM your_table
ORDER BY
  -- 数字开头的字段：先按前缀数字排序
  CASE WHEN columnName REGEXP '^[0-9]' THEN CAST(REGEXP_SUBSTR(columnName, '^[0-9]+') AS UNSIGNED) ELSE NULL END,
  -- 数字开头的字段：再按数字后的字母部分排序
  CASE WHEN columnName REGEXP '^[0-9]' THEN REGEXP_SUBSTR(columnName, '[^0-9].*$') ELSE columnName END,
  -- 字母开头的字段：先按前缀字母排序
  CASE WHEN columnName REGEXP '^[^0-9]' THEN REGEXP_SUBSTR(columnName, '^[^0-9]+') ELSE NULL END,
  -- 字母开头的字段：再按字母后的数字排序
  CASE WHEN columnName REGEXP '^[^0-9]' THEN CAST(REGEXP_SUBSTR(columnName, '[0-9]+$') AS UNSIGNED) ELSE NULL END;

逻辑说明 #

1. 数字开头的字段：
   • 先提取前面的纯数字部分转成整数排序，确保11、11a和12按数字大小分组；
   • 再提取数字后的字母部分按字符串排序，让11（无后缀）排在11a前面。
2. 字母开头的字段：
   • 先提取前面的纯字母部分按字典序排序，让AB110排在z10前面；
   • 再提取字母后的数字部分转成整数排序，确保AB9排在AB110前面。

MySQL 5.x兼容方案 #

如果使用MySQL 5.x（无REGEXP_SUBSTR），可以用SUBSTRING结合REGEXP_INSTR来提取部分：

SELECT columnName
FROM your_table
ORDER BY
  CASE
    WHEN columnName REGEXP '^[0-9]+$' THEN CAST(columnName AS UNSIGNED)
    WHEN columnName REGEXP '^[0-9]' THEN CAST(SUBSTRING(columnName, 1, REGEXP_INSTR(columnName, '[^0-9]') - 1) AS UNSIGNED)
    ELSE NULL
  END,
  CASE
    WHEN columnName REGEXP '^[0-9]' THEN SUBSTRING(columnName, REGEXP_INSTR(columnName, '[^0-9]'))
    ELSE columnName
  END,
  CASE
    WHEN columnName REGEXP '^[^0-9]' THEN SUBSTRING(columnName, 1, REGEXP_INSTR(columnName, '[0-9]') - 1)
    ELSE NULL
  END,
  CASE
    WHEN columnName REGEXP '^[^0-9]' THEN CAST(SUBSTRING(columnName, REGEXP_INSTR(columnName, '[0-9]')) AS UNSIGNED)
    ELSE NULL
  END;

测试验证 #

拿你的测试数据：'11'、'11a'、'12'、'z10'、'AB110'、'AB9'、'5'，用上述SQL排序后会得到：
5 → 11 → 11a → 12 → AB9 → AB110 → z10，完全符合需求。

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