MySQL "Truncated Incorrect INTEGER Value"

Question

I get an odd "Truncated Incorrect INTEGER Value" error when I run the following UPDATE query:

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).

When I run it as a direct selection, I do not receive an error message, and I see the results as expected. Any ideas? Essentially, I'm trying to update the projectNumber field, where the end of the comments in the imported notes is 7 numeric characters (but projectNumber is not always 7 numeric, so the field is varchar (10)).

Answer 1

It's not a mistake. This is a warning that comes from CONVERT () when you ask it to convert a non-numeric value to an integer;

Run these queries in the 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, this is a warning, not a mistake. Your request should correctly update.

Answer 2

Another common reason for this warning is a space in the string to be converted. Use trim() to convert() to get rid of this.

Answer 3

If you use a text type for the column data, and you tried to set the default value to 0 , just set it to NULL :

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

Answer 4

I found one solution that is out of the box and can be easily implemented. The solution is very similar to SET ANSI_WARNING OFF in MS SQL Server.

In MySQL, you first need to check if the configuration for "sql_mode" is set using the following command:

 SHOW VARIABLES LIKE 'sql_mode'; 

OR use below:

 SELECT @@GLOBAL.sql_mode; 

In CSV there can be the following sets of values.

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 mistake, you can delete the settings and reset them with the following command:

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

Use the above command to reset it. This solved a problem like the "Truncated invalid INTEGER value" in my case. I hope this should work from other users who encounter this problem.

See here for more details on this sql_mode.

Hope this will use the full!

Answer 5

As stated in other answers, this is a 2019 error that prevents the request from being executed. To execute a query, even if there are strings that cannot be converted to numbers, simply use UPDATE IGNORE .

For example, the minimum version of the source code:

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