mysql中null转double,如何避免MySQL的隐式转换（截断了错误的DOUBLE值）

When I execute this query

mysql> SELECT * FROM test WHERE sample_col = 'foo';

with this table.

mysql> SHOW CREATE TABLE test\G

*************************** 1. row ***************************

Table: test

Create Table: CREATE TABLE `test` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`sample_col` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> SELECT * FROM test;

+----+------------+

| id | sample_col |

+----+------------+

| 1 | 0 |

| 2 | 1 |

| 3 | 2 |

+----+------------+

3 rows in set (0.00 sec)

MySQL automatically convert 'foo' of WHERE clause to zero and I received this warning message.

mysql> SELECT * FROM test WHERE sample_col = 'foo';

+----+------------+

| id | sample_col |

+----+------------+

| 1 | 0 |

+----+------------+

1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;

+---------+------+-----------------------------------------+

| Level | Code | Message |

+---------+------+-----------------------------------------+

| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |

+---------+------+-----------------------------------------+

1 row in set (0.00 sec)

How can I configure MySQL to report this warning as error? (Or how can I stop implicit conversion?)

I've set SQL_MODE as strict but it seems not affect SELECT statement.

mysql> SELECT @@SQL_MODE;

+------------------------------------------+

| @@SQL_MODE |

+------------------------------------------+

| STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION |

+------------------------------------------+

Version of MySQL is 5.6.16.

mysql> SELECT @@VERSION;

+------------+

| @@VERSION |

+------------+

| 5.6.16-log |

+------------+

[UPDATE]

The query SELECT * FROM test WHERE sample_col = 'foo'; was a bug of my code. And I cannot notice the bug because of implicit conversion.

My purpose is "how to avoid such query" or "how to detect this bug soon". So I want to know the way to stop implicit conversion or way to change error level of this from warning to error.

解决方案

You should rather try casting it explicitly using CAST or CONVERT function like below and don't count on implicit casting.

SELECT * FROM test WHERE sample_col = cast('foo' as int);

(OR)

SELECT * FROM test WHERE cast(sample_col as varchar) = 'foo';

Moreover, there is no point is comparing a INT type column with string value and AFAIK, there is no such setting present to stop implicit casting. if you really want to result in error rather than converting it to 0 then cast it explicitly; in which case it will error out.