如何避免MySQL的隐式转换(截断了错误的DOUBLE值) [英] How to avoid implicit conversion of MySQL (Truncated incorrect DOUBLE value)

问题描述

当我执行此查询时

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

带有此表.

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自动将WHERE子句的'foo'转换为零，我收到此警告消息.

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)

如何配置MySQL以将此警告报告为错误? (或者如何停止隐式转换?)

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

我已将SQL_MODE设置为严格，但似乎不影响SELECT语句.

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 |
+------------------------------------------+

MySQL版本是5.6.16.

Version of MySQL is 5.6.16.

mysql> SELECT @@VERSION;
+------------+
| @@VERSION  |
+------------+
| 5.6.16-log |
+------------+

[UPDATE]

查询SELECT * FROM test WHERE sample_col = 'foo';是我的代码的错误.而且由于隐式转换，我无法注意到该错误. 我的目的是如何避免此类查询"或如何尽快检测到此错误".因此，我想知道停止隐式转换的方法或将其错误级别从警告更改为错误的方法.

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.

推荐答案

您应该尝试使用如下所示的CAST或CONVERT函数显式地对其进行转换，并且不要指望隐式转换.

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';

此外，将INT类型的列与string值和AFAIK进行比较是没有意义的，没有这样的设置可以停止隐式转换.如果您真的想导致错误，而不是将其转换为0，则将其显式转换；如果不希望将其转换为0，则将其显式转换.在这种情况下，它将出错.

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.

有关更多信息，请参见类型转换信息.

See Type Conversion in Expression Evaluation For more information.

这篇关于如何避免MySQL的隐式转换(截断了错误的DOUBLE值)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！