MySQL中将TEXT类型含符号数值转为DOUBLE返回0的问题求助

直接执行CAST(product_price AS DOUBLE)返回全0的原因很明确：你的product_price字段是文本类型，包含$和千分位逗号这类非数字字符，数据库无法将这类带符号的文本解析为有效数值，因此转换失败返回0。

解决方案 #

需要先清理字符串中的非数字符号，再进行类型转换，同时排序要基于转换后的数值（原文本排序会按字符串规则，结果不符合预期）。

通用方案（适用于大多数数据库）

嵌套使用REPLACE函数先移除$和逗号，再转换为DOUBLE：

SELECT 
  CAST(REPLACE(REPLACE(product_price, '$', ''), ',', '') AS DOUBLE) AS product_price_num
FROM test_db.transactions
ORDER BY product_price_num DESC;

MySQL 8.0.22+简化方案

如果使用的是较新版本的MySQL，可以直接用STR_TO_NUM函数，指定价格的格式模板：

SELECT 
  STR_TO_NUM(product_price, '$#,###.##') AS product_price_num
FROM test_db.transactions
ORDER BY product_price_num DESC;

注意事项 #

• 确保所有product_price值的格式统一，没有其他特殊字符（比如空格、其他货币符号），否则转换可能返回NULL
• 若存在NULL或空值，可根据业务需求用IFNULL或COALESCE处理，例如IFNULL(CAST(... AS DOUBLE), 0)

内容的提问来源于stack exchange，提问作者mohammad ali jalili