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 相互交流