MySQL'截断不正确的整数值'
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4946899/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL 'Truncated incorrect INTEGER value'
提问by Todd Sharp
I'm getting an odd 'Truncated incorrect INTEGER value' error when I run the following UPDATE query:
当我运行以下 UPDATE 查询时，我收到一个奇怪的“截断不正确的整数值”错误：
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 is varchar(10).
projectNumber 是 varchar(10)。
When I run it as a straight select I do not get an error and I see results as expected. Any ideas? Essentially I'm trying to update the projectNumber field where the end of the comments in imported notes are 7 numeric characters (but projectNumber's are not always7 numeric, which is why the field is varchar(10)).
当我将它作为直接选择运行时，我没有收到错误，并且看到了预期的结果。有任何想法吗？本质上，我正在尝试更新 projectNumber 字段，其中导入注释中的注释末尾是 7 个数字字符（但 projectNumber 并不总是7 个数字，这就是该字段为 varchar(10) 的原因）。
采纳答案by Mchl
It's not an error. It's a warning that comes from CONVERT() when you ask it to convert non-numeric to integer;
这不是错误。当您要求将非数字转换为整数时，这是来自 CONVERT() 的警告；
Run these queries in console to see:
在控制台中运行这些查询以查看：
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)
As I said, it's a warning, not an error. Your query should be doing the update correctly.
正如我所说，这是一个警告，而不是一个错误。您的查询应该正确进行更新。
回答by Ryan Shirley
Another common cause for this warning is white space in the string to be converted. Use trim()before convert()to get rid of that.
此警告的另一个常见原因是要转换的字符串中的空格。使用trim()之前convert()摆脱它。
回答by Unkulunkulu
As stated in other answers, this is an error as of 2019, which prevents the query from running. To run the query even if there are strings that cannot be converted to numbers, simply use UPDATE IGNORE.
正如其他答案中所述，这是 2019 年的错误，它阻止了查询运行。即使存在无法转换为数字的字符串，也要运行查询，只需使用UPDATE IGNORE.
So for example a minimal version of the original code:
例如，原始代码的最小版本：
UPDATE IGNORE tbl
SET projectNumber = RIGHT(comments, 7)
WHERE CONVERT(RIGHT(COMMENTS, 7), SIGNED INTEGER) > 0
回答by John
If you're using a text type for column data and you tried to set the default value as 0then just set it as NULL:
如果您对列数据使用文本类型，并且您尝试将默认值0设置为，则只需将其设置为NULL：
ALTER TABLE `__table__`
CHANGE `__column_name__old__` `__column_name__new__`
INT(4) NOT NULL DEFAULT '0';
回答by Bhavesh Harsora
I found one solution, which is out of box and could easily be implemented.
The solution is much similar to SET ANSI_WARNING OFFin MS SQL Server.
我找到了一种解决方案，它是开箱即用的，可以轻松实现。该解决方案与SET ANSI_WARNING OFFMS SQL Server 中的解决方案非常相似。
In MySQL, first you need to check whether what is configurations is set for "sql_mode" by using below command:
在 MySQL 中，首先您需要使用以下命令检查“sql_mode”是否设置了配置：
SHOW VARIABLES LIKE 'sql_mode';
OR else use below:
或者在下面使用：
SELECT @@GLOBAL.sql_mode;
There might have following sets of value in CSV.
CSV 中可能有以下几组值。
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_ENGINE_SUBSTITUTION
Based on your error, you can remove settings and Reset it using below command:
根据您的错误，您可以使用以下命令删除设置并重置它：
SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
By using above command to reset it. It has solved similar of this problem "Truncated incorrect INTEGER value" in my case. I hope it should be working from other user also who are facing this issue.
通过使用上面的命令来重置它。在我的情况下，它已经解决了类似的问题“截断了不正确的整数值”。我希望它也应该适用于也面临此问题的其他用户。
For more details on this "sql_mode", please refer this.
有关此“sql_mode”的更多详细信息，请参阅此。
Hope this would be use full!
希望这将被充分利用！
