如何在SQL中获取带固定前缀的VARCHAR列的最大编号值

这个坑我之前踩过！字符串比较和数值比较的逻辑差异确实容易让人头疼，咱们一步步来解决。

问题根源 #

你原来的SQL语句是直接对-后面的字符串取MAX，而字符串比较是逐字符按ASCII码对比：比如'-12346'的第二个字符是'1'，'-012347'的第二个字符是'0'，因为'1'的ASCII码比'0'大，所以字符串层面'-12346'会被判定为更大，但实际数值上12346 < 12347，这就是为什么结果不符合预期。

解决方案 #

核心思路是：把-后面的后缀字符串转换为数值类型，再按数值大小比较，这样就能得到真正的最大值，同时还能保留原始的后缀格式（比如012347）。

MySQL 版本

如果你用的是MySQL，直接用CAST把后缀转成无符号整数，再排序取第一条：

SELECT SUBSTRING(myColumn, LOCATE('-', myColumn) + 1)
FROM MyTable
ORDER BY CAST(SUBSTRING(myColumn, LOCATE('-', myColumn) + 1) AS UNSIGNED) DESC
LIMIT 1;

这个语句会返回你想要的012347——因为我们先按数值降序排列，取第一条对应的原始后缀，完美保留前导0格式。

如果只需要最大的数值（不需要前导0），可以简化成：

SELECT MAX(CAST(SUBSTRING(myColumn, LOCATE('-', myColumn) + 1) AS UNSIGNED))
FROM MyTable;

这个会返回12347。

其他SQL方言适配

• SQL Server：把CAST(... AS UNSIGNED)换成CAST(... AS INT)或者CONVERT(INT, ...)
• Oracle：用TO_NUMBER(SUBSTRING(...))来转换后缀

边界情况说明 #

• 不管后缀是5位（比如12345）还是6位（比如012347），转换为数值后都能正确比较大小
• 最大值999999转换为无符号整数完全没问题，不会溢出

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