Disable CAST () warnings in MySQL?

Question

I use MySQL in strict mode ( SET sql_mode = 'STRICT_TRANS_TABLES' ) to convert all warnings to errors. However, I have a query that is expected to generate warnings because it is trying to convert a VARCHAR field that may be empty or contain integer letters.

Example:

 mysql> select CAST("123b" AS SIGNED); +------------------------+ | CAST("123b" AS SIGNED) | +------------------------+ | 123 | +------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1292 | Truncated incorrect INTEGER value: '123b' | +---------+------+-------------------------------------------+ 1 row in set (0.00 sec) 

Is there a way to suppress a warning caused by CAST() without disabling strict mode? Or, conversely, can you disable strict mode for one request or function (something like the @ operator in PHP) without calling SET twice to temporarily disable strict mode?

Background: I have a table with street numbers. Most of them are numeric, but some of them contain letters at the end. To implement a simplified “natural sort”, I would like to use ORDER BY CAST (StreetNr AS SIGNED), StreetNr , and the value returned by CAST () would be great for first-level sorting.

Answer 1

I assume the problem is that you are trying to insert data from one table into another using this query:

 INSERT INTO ... SELECT ... FROM ... WHERE ... ORDER BY ... 

And the inserts do not work due to the CAST() problem described in your question.

Is this accurate?

If so, it is easiest to use INSERT IGNORE . This syntax is useful for ignoring repeated key errors, but it can also be used to ignore CAST() errors that affect you.

Your updated request will look something like this:

 INSERT IGNORE INTO target_table SELECT ... FROM source_table WHERE ... ORDER BY CAST (StreetNr AS SIGNED), StreetNr