错误代码：1292。截断不正确的 DOUBLE 值：更新字段值时

Question

下面的查询给出了一列小数和空格中的预期结果：

SELECT IFNULL(CAST((volume/SharesIssued)*100 AS decimal(2,2)) , '') FROM stock_watch.eod

结果

0.33
0.03
0.00
0.02
0.55
0.39
0.00
0.05
0.09
0.06
0.08
0.00
0.00

然而，当我试图将其添加到像这样的字段时

UPDATE stock_watch.eod SET VolumePercentage = IFNULL(CAST((volume/SharesIssued)*100 AS decimal(2,2)) , '')

我得到以下错误：Error Code: 1292. Truncated incorrect DOUBLE value: ''

所有字段均为varchar(45(

表格看起来像这个

CREATE TABLE eod (
id int(11) NOT NULL AUTO_INCREMENT,
code varchar(6) CHARACTER SET utf8mb4 DEFAULT NULL,
date date DEFAULT NULL,
open varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
high varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
low varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
close varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
volume varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
SharesIssued varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
VolumePercentage varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
checksum varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
PRIMARY KEY (id),
KEY fb_groupbyorder_date_INDEX (date)
) ENGINE=InnoDB AUTO_INCREMENT=270331 DEFAULT CHARSET=utf8;

样本数据：dbfiddle

Answer 1

最后，我在my.cnf文件中更改了sql_mode=''。铸造也会因为给出错误的结果而产生问题。通过不强制转换解决了问题，该方法适用于sql mode="。我承认这实际上不是错误消息的解决方案，而是一种让更新发生并将错误减少为警告的方法。然而，根据我的经验，严格模式往往会使mysql无法使用。

Answer 2

其中一个SharesIssued字段为空，您在其中对volume/SharesIssued的除法变为0/' '。这就是错误的原因。

如果你这样更新数据：

UPDATE eod SET SharesIssued=0 WHERE SharesIssued='';

再次运行UPDATE查询，您将收到Error 1365: Division by 0，但现在这是由SharesIssued=''更新为0引起的。相反，由于默认情况下sql_mode与STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION处于打开状态，因此会发生这种情况。对此的一个快速解决方案是关闭会话上的sql_mode：

SET sql_mode = "";
UPDATE eod 
SET VolumePercentage = CAST(IFNULL(volume/IFNULL(SharesIssued,0),0)*100 AS DECIMAL(2,2));

根据您使用的工具以及是否将其设置为显示消息(错误、警告等(，您可能会收到以下警告消息：

警告1292：截断了不正确的DOUBLE值：">
警告1264：超出第1行"CAST((volume/SharesIssued(*100 AS decimal(2,2(("列的值范围

但是列VolumePercentage无论如何都会更新。请注意，当MySQL服务重新启动时，sql_mode将返回默认设置。

演示小提琴