使用CAST选择:成功；使用CAST更新:失败

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

列price是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));

Answer 2

如你所述：

列price是TEXT。

因此，在第一个示例中，您将文本值转换为十进制值。

第二个查询(更新)是有问题的，因为您试图将文本属性更新为其本身的十进制版本。更新文本值时，不需要将其转换为十进制。

你的陈述，

据我理解，SELECT应该与更新完全相同。

存在缺陷:使用SELECT，您将从数据中提取数据以供其他进程使用。这可能涉及将属性转换为另一种数据类型。

使用UPDATE语句，新值应该与属性相同的数据类型。因为price是文本，所以新的值应该是文本(这就是为什么不应该将属性更新为本身的十进制版本)。

Answer 3

你可能在某个地方有一个空值

CREATE TABLE `orders` (
  `price` VARCHAR(100) NOT NULL);

INSERT INTO orders (price) VALUES (''); -- Empty value

无错误：

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(price AS DECIMAL(10,2)) WHERE price <> ''