MySQL5.7中截断了不正确的十进制值

0aydgbwb 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(582)

在MySQL5.6.13中使用sql模式“ STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ，以下查询正常工作，但有警告：

UPDATE Company SET company_CurrentYearValueGBP = COALESCE((
SELECT SUM(
(COALESCE(salesdata_Month01Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month02Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month03Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month04Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month05Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month06Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month07Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month08Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month09Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month10Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month11Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month12Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
0) FROM SalesData 
WHERE salesdata_SalesDataTypeID = 3 
AND salesdata_CompanyID = company_ID 
AND salesdata_SalesYearID = 6 
AND NOT salesdata_IsBeingProcessed 
),0)

输出：

0 row(s) affected, 5 warning(s):
 1265 Data truncated for column 'company_CurrentYearValueGBP' at row 127
 1265 Data truncated for column 'company_CurrentYearValueGBP' at row 127
 1265 Data truncated for column 'company_CurrentYearValueGBP' at row 127
 1265 Data truncated for column 'company_CurrentYearValueGBP' at row 127
 1265 Data truncated for column 'company_CurrentYearValueGBP' at row 127
 Rows matched: 1470  Changed: 0  Warnings: 5

在MySQL5.7.14中使用sql模式“ STRICT_TRANS_TABLES ，同一查询正在创建以下错误：

Error Code: 1292. Truncated incorrect DECIMAL value: ''

我理解为什么会发生错误（因为比较salesdata_exchangerate<>''），但是有人能解释为什么在mysql 5.6中，即使启用了严格模式，查询也只产生警告，而没有因为错误而中止吗？两个版本的行为似乎不同。
更新1在MySQL5.6.13和MySQL5.7.14上创建了一个简单的表，其结构如下：

添加了以下记录：

运行以下查询会在MySQL5.7.14中再现相同的错误：

UPDATE testtable 
SET testtable_CalculatedValue = IF(testtable_DecimalValue<>'',testtable_DecimalValue,0) 
WHERE testtable_ID > 0

Error Code: 1292. Truncated incorrect DECIMAL value: ''

在mysql 5.6.13中，testtable_calculatedvalue按预期设置为1.35。

mysql Decimal mysql-error-1292

来源：https://stackoverflow.com/questions/53868586/truncated-incorrect-decimal-value-in-mysql-5-7

1条答案

按热度按时间

hvvq6cgz1#

设置：

mysql> CREATE TABLE so53868586 (
         id INT AUTO_INCREMENT,
         deci  DECIMAL(12,2) DEFAULT NULL,
         calc DECIMAL(12,2) DEFAULT NULL,
         PRIMARY KEY(id)
     ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> SET @@sql_mode = "STRICT_TRANS_TABLES";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO so53868586 (deci)
         VALUES (1.35), (NULL), (0), (12345);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

测试

mysql> SELECT * FROM so53868586;
+----+----------+------+
| id | deci     | calc |
+----+----------+------+
|  1 |     1.35 | NULL |
|  2 |     NULL | NULL |
|  3 |     0.00 | NULL |
|  4 | 12345.00 | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

mysql> 
mysql> UPDATE so53868586 SET calc = IF(deci <> 0, deci, 0)
         WHERE id > 0;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> 
mysql> SELECT * FROM so53868586;
+----+----------+----------+
| id | deci     | calc     |
+----+----------+----------+
|  1 |     1.35 |     1.35 |
|  2 |     NULL |     0.00 |
|  3 |     0.00 |     0.00 |
|  4 | 12345.00 | 12345.00 |
+----+----------+----------+
4 rows in set (0.01 sec)

（使用的是5.6.22。）
5.7.15得到相同的结果，但抱怨 SET @sql_mode 具有

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3135 | 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. |
| Warning | 3090 | Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.                                                                          |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

我复制你的测试用例失败了吗？
我在5.7.15的changelog（或者其他任何地方）中没有看到任何相关内容，但是您可以考虑更新(5.7.14是两年前发布的。）

赞(0）回复(0）举报 2021-06-16

我来回答

MySQL5.7中截断了不正确的十进制值

1条答案

相关问题

热门标签

最新问答