MySQL如何实现字母数字混合字段的正确自然排序？

这个需求完全可以实现。你遇到的是典型的字符串自然排序场景：MySQL默认对字符串类型字段做排序时会逐位比较字符，M10的第二个字符是1，比M2的第二个字符2的ASCII码更小，所以默认排序会出现M10排在M2前面的问题。

可用解决方案 #

方法1：全版本兼容写法 #

如果你的字段固定为1位字母加数字的结构，直接拆分两部分排序即可，兼容所有MySQL版本：

SELECT * FROM 你的表名
ORDER BY
  -- 第一步按字母前缀排序
  SUBSTRING(number, 1, 1),
  -- 第二步将后缀数字转为数值类型排序
  CAST(SUBSTRING(number, 2) AS UNSIGNED) ASC;

方法2：适配多字母前缀的写法（MySQL 8.0+支持） #

如果后续可能出现AB1、ABC123这类多位字母前缀的场景，可以用正则匹配拆分：

SELECT * FROM 你的表名
ORDER BY
  -- 提取所有开头非数字的前缀部分排序
  REGEXP_SUBSTR(number, '^[^0-9]+'),
  -- 提取结尾所有数字部分转为数值排序
  CAST(REGEXP_SUBSTR(number, '[0-9]+$') AS UNSIGNED) ASC;

方法3：极简隐式转换写法 #

MySQL遇到字符串参与数值运算时，会自动提取字符串开头非数字内容后的数字部分做转换，也可以用这种更简洁的写法：

SELECT * FROM 你的表名
ORDER BY LEFT(number,1), number + 0 ASC;

以上三种方案最终都可以得到你需要的M1, M2, ... M9, M10, U1, U2排序结果。

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