MySQL 'Truncated incorrect INTEGER value'

Question

MySQL 'Truncated incorrect INTEGER value'

23

当我运行以下的UPDATE查询时，出现了奇怪的'Truncated incorrect INTEGER value'错误：

update tbl
set projectNumber = right(comments, 7)
where createdBy = 'me'
and length(CONVERT(right(comments, 7), SIGNED INTEGER)) = 7 
and CONVERT(right(comments, 7), SIGNED INTEGER) > 0
and CONVERT(right(comments, 7), SIGNED INTEGER) is not null
and createdOn > '2011-01-31 12:00:00'
and projectNumber is null

projectNumber是varchar(10)。

当我直接运行它作为选择时，我不会收到错误消息，并且可以看到预期的结果。有任何想法吗？基本上，我正在尝试更新projectNumber字段，其中导入注释中末尾为7个数字字符（但projectNumber并非始终为7个数字，这就是为什么该字段是varchar(10)的原因）。

- Todd Sharp

7个回答

12

正如其他回答中所述，这是一个在2019年之后的错误，导致查询无法运行。如果要在存在无法转换为数字的字符串的情况下运行查询，请简单使用UPDATE IGNORE。

因此，例如原始代码的最小版本：

UPDATE IGNORE tbl
SET projectNumber = RIGHT(comments, 7)
WHERE CONVERT(RIGHT(COMMENTS, 7), SIGNED INTEGER) > 0

- punsku

如果您正在使用INSERT INTO，同样需要考虑这一点。您应该在查询中添加"IGNORE"：INSERT IGNORE INTO tbl(a,b,c) VALUES (1, CONVERT(RIGHT(COMMENTS, 7), SIGNED INTEGER), 3) - Rosario Russo

使用IGNORE真的安全吗？ - Freedo

2

UPDATE IGNORE 跳过无法转换的行。如果在您的使用情况下这不可接受，您应确保所有行都可以转换，然后在没有 IGNORE 的情况下运行查询。 - punsku

9

出现此警告的另一个常见原因是要转换的字符串中存在空格。在使用convert()之前，请使用trim()消除它。

- Ryan Shirley

3

我找到一个解决方案，它是开箱即用的，可以很容易地实现。这个解决方案与 MS SQL Server 中的 "SET ANSI_WARNING OFF" 非常相似。

在 MySQL 中，首先需要使用以下命令检查是否设置了 "sql_mode" 的配置：

SHOW VARIABLES LIKE 'sql_mode';

最初的回答

如果不是这样，可以使用以下方法：

SELECT @@GLOBAL.sql_mode;

CSV文件中可能包含以下值集合。

ONLY_FULL_GROUP_BY，STRICT_TRANS_TABLES，NO_ZERO_IN_DATE，NO_ZERO_DATE，ERROR_FOR_DIVISION_BY_ZERO，NO_ENGINE_SUBSTITUTION

根据您的错误，您可以使用以下命令删除设置并重置它：

（Original Answer翻译成“最初的回答”）

SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

通过使用上述命令进行重置，已解决了我在这种情况下遇到的“截断不正确的整数值”问题。我希望其他遇到此问题的用户也能正常工作。

有关“sql_mode”的更多详细信息，请参见此链接。

希望这对您有所帮助！

- Bhavesh Harsora

最好只为当前会话设置SQL_MODE，而不是全局值。可以通过运行SET SESSION SQL_MODE = 'YOUR_FLAGS_HERE'来实现。在我的情况下，罪魁祸首是STRICT_TRANS_TABLES标志。 - undefined

1

如果您在列数据中使用文本类型，并尝试将默认值设置为0，则只需将其设置为NULL。

ALTER TABLE `__table__` 
CHANGE `__column_name__old__` `__column_name__new__` 
INT(4) NOT NULL DEFAULT '0';

- John

1

Toby Speight在他的回答中概述了解决该问题的一种方法。我不知道是否会有人像我这样愚蠢，但是对于那些关心的人：

我尝试将列IsDeleted的数据类型从VARCHAR（'Y'= > true，'N'= > false）更改为BIT时，出现错误代码1292。

要解决此问题：将所有“Y”更新为“1”（UPDATE table SET isDeleted = '1' WHERE isDeleted = 'Y'），将所有“N”更新为“0”，再次尝试修改列类型，然后数据类型就被修改了，而且“1”列现在包含TRUE，反之亦然。

- Jimmy

1

感谢@bhavesh-harsora，我通过更新sql_mode来解决错误。错误是由于sql模式中的STRICT_TRANS_TABLES标志引起的。

此外，当我删除此标志时，错误消息发生了变化

从：“错误代码：1292。截断不正确的INTEGER值：''”

到：“影响2333行，警告1024个：1292截断不正确的INTEGER值：''......”。

因此，我理解去除此标志会将某些错误转换为警告。

奇怪的是，当我运行具有相同条件的SELECT查询时，我不会遇到此错误，但是当我运行DELETE查询时，我会遇到此错误。

抛出错误代码：1292。截断不正确的INTEGER值：''错误：

DELETE pv
FROM table_a AS pv
INNER JOIN table_b AS ws ON pv.ws_id = ws.id
INNER JOIN table_c AS w ON w.product_id = pv.product_id
INNER JOIN table_d ppp_l ON ppp_l.id = 7002 and ppp_l.product_id = w.product_id
WHERE CAST(ppp_l.value as UNSIGNED) < CAST(ws.value as UNSIGNED);

但是SELECT查询没有出现任何错误：

SELECT 
pv.id
FROM table_a AS pv
INNER JOIN table_b AS ws ON pv.ws_id = ws.id
INNER JOIN table_c AS w ON w.product_id = pv.product_id
INNER JOIN table_d ppp_l ON ppp_l.id = 7002 and ppp_l.product_id = w.product_id
WHERE CAST(ppp_l.value as UNSIGNED) < CAST(ws.value as UNSIGNED)

这个问题也在这里提到了：https://bugs.mysql.com/bug.php?id=76353

- Nuryagdy Mustapayev

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Mchl · Accepted Answer

这不是一个错误。当你让CONVERT()函数将非数字类型转换为整数时，它会发出一个警告；

在控制台运行这些查询以查看：

mysql> SELECT CONVERT(right('1s23d45678', 7), SIGNED INTEGER);
+-------------------------------------------------+
| CONVERT(right('1s23d45678', 7), SIGNED INTEGER) |
+-------------------------------------------------+
|                                               3 |
+-------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '3d45678' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

就像我说的那样，这只是一个警告而不是错误。你的查询应该可以正确地更新。