mysql error 1292

朋友问一个问题： 
delete时出现一个warning 1292. 

重现： 
mysql> create table test (id int); 
Query OK, 0 rows affected (0.01 sec) 

mysql> insert into test values (100); 
Query OK, 1 row affected (0.00 sec) 

mysql> delete from test where id='100a'; 
Query OK, 1 row affected, 1 warning (0.01 sec) 

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

mysql> select * from test; 
Empty set (0.00 sec) 


第一感觉 这个问题 应该是sql_mode 引起的，查看并修改sql_mode后，还是一样的情况。 
mysql> show variables like '%sql_m%'; 
+---------------+------------------------+ 
| Variable_name | Value | 
+---------------+------------------------+ 
| sql_mode | NO_ENGINE_SUBSTITUTION | 
+---------------+------------------------+ 
1 row in set (0.00 sec) 


mysql> set global sql_mode ='STRICT_ALL_TABLES'; 
Query OK, 0 rows affected (0.00 sec) 

mysql> exit 
Bye 
[ root@song ~]# mysql -uroot -ptest 
Warning: Using a password on the command line interface can be insecure. 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 35 
Server version: 5.6.20-ucloudrel1-log Source distribution 

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. 

Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 

mysql> use test; 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 

Database changed 
mysql> insert into test values (100); 
Query OK, 1 row affected (0.00 sec) 

mysql> delete from test where id='100a'; 
Query OK, 1 row affected, 1 warning (0.00 sec) 

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

mysql> show variables like '%sql_m%'; 
+---------------+-------------------+ 
| Variable_name | Value | 
+---------------+-------------------+ 
| sql_mode | STRICT_ALL_TABLES | 
+---------------+-------------------+ 
1 row in set (0.00 sec) 

他真正后台执行的是什么呢？ 
又开始想他是如何解析sql的，未果。 

换个角度一想，从binlog中可以看出来， 得到的结果是： 
### DELETE FROM `test`.`test` 
### WHERE 
### @1=100 /* INT meta=0 nullable=1 is_null=0 */ 

这也就是转换后的sql的。

转载请注明源出处 
QQ 273002188 欢迎一起学习 
QQ 群 236941212 
oracle,mysql,mongo 相互交流

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25099483/viewspace-2059744/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25099483/viewspace-2059744/