今天上线一个需求,线上产品验收发现一个bug,lot号范围是1-99,而实际数值应该是1-449才对
原来的sql
SELECT
min( ITEM_LOT ) AS minLot,
max( ITEM_LOT ) AS maxLot
FROM
base_item_control_info
WHERE
SESSION_ID = 61651;
然后排查sql,发现数据字段类型竟然是varchar类型的,字符串类型比较大小他是从最左位开始比较,第一位9就认为比4大,因此最大的数是99,所以需要对字符串类型的值进行转换成数值类型然后再进行比较。
更正后的sql:
SELECT
min(
CAST( ITEM_LOT AS SIGNED INTEGER )) AS minLot,
max(
CAST( ITEM_LOT AS SIGNED INTEGER )) AS maxLot
FROM
base_item_control_info
WHERE
SESSION_ID = 61651;
查询后的结果
解决问题!!