Mysql - max(9) 大于 max(10) 的原因分析与解决方案

前言

在多个项目的开发过程中都遇到过这个问题，所以这里记录下来

问题描述

有一张订单资料表 order_data_record，每次提交都会在资料名的后面累加 1：

首先利用 Mysql - 字符串截取、拆分 这篇文章中的字符串截取函数，把后面的数字截取出来：

SELECT SUBSTRING_INDEX(data_name,'-',-1) FROM `order_data_record` where order_id = '096FC1'

然后再使用 Mysql 的 max() 函数取最大的数值加 1：

SELECT MAX(SUBSTRING_INDEX(data_name,'-',-1)) FROM `order_data_record` where order_id = '096FC1'

这里结果本应该返回 13，然而结果却返回了 9 ！？

原因分析

原因是因为，字符串截取函数截取出来的数字是 varchar 类型的。而 max 函数对于字符串的比较，是先比较首字符的 ASCii 码 的大小，然后依次往后进行比较的，所以字符串 9 比 字符串 13 大。

解决方案

所以如果想返回自然顺序的最大值，需要把类型转换为 int 后再比较。

替换函数：cast(xxx as UNSIGNED INTEGER)

sql：

SELECT MAX(cast(SUBSTRING_INDEX(data_name,'-',-1) as UNSIGNED INTEGER)) FROM `order_data_record` where order_id = '096FC1'
// 输出 13

这里的 UNSIGNED INTEGER 可以为：

浮点数 : DECIMAL 
整数 : SIGNED INTEGER 等同于 INTEGER
无符号整数 : UNSIGNED INTEGER
二进制，同带 binary 前缀的效果 : BINARY    
字符型，可带参数 : CHAR()     
日期 : DATE     
时间: TIME     
日期时间型 : DATETIME

---

注意：当 cast 函数中的值为 null 时，转换后还是 null

SELECT CAST(null as UNSIGNED INTEGER)

拓展

• 获取当前进度

SELECT MAX(cast(SUBSTRING_INDEX(data_name,'-',-1) as UNSIGNED INTEGER)) FROM `order_data_record` where order_id = '096FC1'

• 获取下一个进度

SELECT IFNULL(MAX(cast(SUBSTRING_INDEX(data_name,'-',-1) as UNSIGNED INTEGER)) + 1, 1) FROM `order_data_record` where order_id = '096FC1'

提示：null + 1 = null