有一次在代码中发现了一个BUG,在排查过程中,差点晕翻;作一个DBA也碰到这种低级错误;大为自嘲。其实这个问题在官方手册里就写得很明白;(可见官方文档的重要性)
mysql
>
create
table
h1 (id
int
, col1
char
(
1
));
Query OK, 0 rows affected ( 0.00 sec)
mysql > insert into h1 select 1 ,’I ' ;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into h1 select 2,’F ' ;
Query OK, 1 row affected ( 0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql > insert into h1 select 2 ,’ 0 ′;
Query OK, 1 row affected ( 0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected ( 0.00 sec)
mysql > insert into h1 select 1 ,’I ' ;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into h1 select 2,’F ' ;
Query OK, 1 row affected ( 0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql > insert into h1 select 2 ,’ 0 ′;
Query OK, 1 row affected ( 0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql
>
select
*
from
h1 ;
+ —— + —— +
| id | col1 |
+ —— + —— +
| 1 | I |
| 2 | F |
| 2 | 0 |
+ —— + —— +
3 rows in set ( 0.00 sec)
mysql > select * from h1 where col1 = 0 ;
+ —— + —— +
| id | col1 |
+ —— + —— +
| 1 | I |
| 2 | F |
| 2 | 0 |
+ —— + —— +
3 rows in set , 2 warnings ( 0.00 sec)
mysql > select * from h1 where col1 = ’ 0 ′;
+ —— + —— +
| id | col1 |
+ —— + —— +
| 2 | 0 |
+ —— + —— +
1 row in set ( 0.00 sec)
+ —— + —— +
| id | col1 |
+ —— + —— +
| 1 | I |
| 2 | F |
| 2 | 0 |
+ —— + —— +
3 rows in set ( 0.00 sec)
mysql > select * from h1 where col1 = 0 ;
+ —— + —— +
| id | col1 |
+ —— + —— +
| 1 | I |
| 2 | F |
| 2 | 0 |
+ —— + —— +
3 rows in set , 2 warnings ( 0.00 sec)
mysql > select * from h1 where col1 = ’ 0 ′;
+ —— + —— +
| id | col1 |
+ —— + —— +
| 2 | 0 |
+ —— + —— +
1 row in set ( 0.00 sec)
mysql
>
select
*
from
h1
where
col1
=
’
1
′;
Empty set ( 0.00 sec)
mysql > select * from h1 where col1 = 1 ;
Empty set , 2 warnings ( 0.00 sec)
Empty set ( 0.00 sec)
mysql > select * from h1 where col1 = 1 ;
Empty set , 2 warnings ( 0.00 sec)
发现没有:
col1=0 ; 所有行被选中;
col1=’0′ ; 结果集正常
mysql
>
select
*
from
h1
where
col1
=
’
1
′;
Empty set ( 0.00 sec)
mysql > select * from h1 where col1 = 1 ;
Empty set , 2 warnings ( 0.00 sec)
Empty set ( 0.00 sec)
mysql > select * from h1 where col1 = 1 ;
Empty set , 2 warnings ( 0.00 sec)
想知道为什么吗?
看这里.
mysql
>
show warnings;
+ ——— + —— + ————————————— +
| Level | Code | Message |
+ ——— + —— + ————————————— +
| Warning | 1292 | Truncated incorrect DOUBLE value: ‘I’ |
| Warning | 1292 | Truncated incorrect DOUBLE value: ‘F’ |
+ ——— + —— + ————————————— +
2 rows in set ( 0.00 sec)
+ ——— + —— + ————————————— +
| Level | Code | Message |
+ ——— + —— + ————————————— +
| Warning | 1292 | Truncated incorrect DOUBLE value: ‘I’ |
| Warning | 1292 | Truncated incorrect DOUBLE value: ‘F’ |
+ ——— + —— + ————————————— +
2 rows in set ( 0.00 sec)
MySQL当数据类型不匹配时,尊重用户的输入,将字段的类型做了隐式转换然后来匹配,而字符向数字转,结果都是0;