mysql - 使用 CAST : Success; Update with CAST: Failure 选择

Question

我看不懂以下作品

SELECT price, CAST(price AS DECIMAL(10,2)) FROM orders;

但是更新失败:

UPDATE orders SET price = CAST(price AS DECIMAL(10,2));
UPDATE orders SET price = CAST(CAST(price AS DECIMAL(10,2)) AS CHAR(20));

错误:

Error Code: 1366. Incorrect DECIMAL value '0' for column '' at row -1

价格列是TEXT

根据我的理解，SELECT 应该与 UPDATE 执行完全相同的操作

Answer 1

在选择中，您只显示转换为小数的结果，在更新中，您将小数保存在非数字中
尝试将结果作为字符串转换为

    UPDATE orders SET price = CAST(CAST(ifnull(price,0) AS DECIMAL(10,2) AS char(20));

或者您可能有一个无法转换为十进制的值，例如为此留出一些空间，您可以使用大小写检查不同的条件

    UPDATE orders SET price = CAST(CAST(
         CASE when price IS NULL THEN 0 
              when trim(price) ='' THEN 0 
                ELSE price 
              END )  AS DECIMAL(10,2) AS char(20));