为什么MAX函数返回错误值？text类型存储数值时取最大值异常问题

问题根因 #

• item列为text类型，直接调用MAX()时默认按字符串字典序比较大小：字符'9'的字典序大于'6'，因此会返回9.38而非数值最大的62612159.13。
• 转换数值类型失败的核心原因是item列中存在不符合数值格式的异常值（如前后空格、不可见字符、特殊符号、空字符串等），普通转换语句遇到异常值会直接中断，导致仅返回转换成功的前几个值。

解决方案 #

1. 用安全转换函数计算最大值 #

主流数据库均支持安全类型转换函数，转换失败时会返回NULL而非中断查询，MAX()计算时会自动忽略NULL值，最终得到正确的数值最大值：

-- 通用语法，支持PostgreSQL/MySQL 8.0+/SQL Server等主流数据库
SELECT MAX(TRY_CAST(item AS DECIMAL(20,2))) AS max_item FROM items;

如果你的数据库不支持TRY_CAST，可以先通过正则过滤合法数值再转换：

-- 以PostgreSQL为例，正则匹配合法小数格式后再计算最大值
SELECT MAX(CAST(item AS DECIMAL(20,2))) AS max_item
FROM items
WHERE item ~ '^[0-9]+\.?[0-9]*$';

注：DECIMAL(20,2)的精度可根据你的实际业务数值范围调整，当前你给出的最大值仅需DECIMAL(10,2)即可覆盖。

2. 排查异常值（可选） #

如果需要找出所有无法转换为数值的异常记录，执行以下查询即可：

SELECT item FROM items
WHERE TRY_CAST(item AS DECIMAL(20,2)) IS NULL;

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