strict mode to avoid type conversion

Question

Is there any sql mode that will return an error instead of implicitly converting the string to integer?

mysql> select * from todel ;
+------+--------+
| id   | name   |
+------+--------+
|    1 | abc    |
|    2 | xyz    |
|    0 | ABCxyz |
+------+--------+
3 rows in set (0.00 sec)

I expect an error message instead of a row with id 0

mysql> select * from todel where id = 'abc';
+------+--------+
| id   | name   |
+------+--------+
|    0 | ABCxyz |
+------+--------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'abc' |
+---------+------+-----------------------------------------+
1 row in set (0.01 sec)

Answer 1

I understand your concerns, but it's for this very reason you should never have an id set to 0. In the long run I think you should reconsider your table rows before the behavior which isn't a problem in ideal situations. I haven't found anything relevant to this through a little searches, and that's probably because it's probably not a problem unless you make it one.

Apart from that, you could read relevant column data and act accordingly in php/whatev. From the table COLUMNS in information_schema, you can filter by TABLE_SCHEMA (database), TABLE_NAME and COLUMN_NAME to get DATATYPE (double). If the column you're changing has a certain DATATYPE, let the script give error before running the MySQL query.

Another way to do it would simply be to convert input before parsing:

if ( ! is_numeric($id))
    $id = 'NULL';

To prevent incorrect INSERTs or UPDATEs, you already have that mode.

---

In the end I can't come up with many practical ways that this strict mode you're after would benefit the MySQL users.

Answer 2

You can use STRICT_ALL_TABLES sql mode:

set @@GLOBAL.sql_mode  = "STRICT_ALL_TABLES";
set @@SESSION.sql_mode = "STRICT_ALL_TABLES";

However it works just on write operations:

MariaDB [(none)]> insert into test.test values ( "abc", "lol" );
--------------
insert into test.test values ( "abc", "lol" )
--------------

ERROR 1366 (22007): Incorrect integer value: 'abc' for column 'id' at row 1

There is no such thing to disable implicit conversions for read queries; instead you can just check if there are warnings and if yes, just free the result, abort the statement, and threat those warnings as errors.