在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。
1条答案
按热度按时间xmq68pz91#
设置:
测试
(使用的是5.6.22。)
5.7.15得到相同的结果,但抱怨
SET @sql_mode
具有我复制你的测试用例失败了吗?
我在5.7.15的changelog(或者其他任何地方)中没有看到任何相关内容,但是您可以考虑更新(5.7.14是两年前发布的。)