MySQL中字符串与数字比较的坑

公司项目代码中,某枚举字段数据库表中类型是char(1),在代码中,误以为是TINYINT,所以用数字筛选,后来发现结果不对。发现了一个现象,用数字0筛选会把所有的记录给筛选出来。
经过排查发现是在MySQL查询语句中,'abc'如果和'0'比较结果显然是不等的,但如果'abc'和0比较呢?结果居然是相等的。

随意测试了几下,结果如下:

mysql> select 'abc'=0;
+---------+
| 'abc'=0 |
+---------+
|       1 |
+---------+
1 row in set, 1 warning (0.00 sec)
mysql> select '0abc'=0;
+----------+
| '0abc'=0 |
+----------+
|        1 |
+----------+
1 row in set, 1 warning (0.00 sec)
mysql> select '01abc'=0;
+-----------+
| '01abc'=0 |
+-----------+
|         0 |
+-----------+
1 row in set, 1 warning (0.00 sec)
mysql> select '013abc'=13;
+-------------+
| '013abc'=13 |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

以上几个查询通过查看MySQL给出的警告,都可以看到类似如下的信息

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

查阅MySQL 5.7官方文档中关于比较的章节,其中说明Strings are automatically converted to numbers and numbers to strings as necessary.。也就是说在比较的时候,String是可能会被转为数字的。

而对于数字开头的字符串来说,转为数字的结果就是截取前面的数字部分

mysql> select cast('123abc' as signed);
+-----------------------------+
| cast('123abc' as signed) |
+-----------------------------+
|                         123 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select cast('123.45abc' as decimal(5,2));
+-----------------------------------+
| cast('123.45abc' as decimal(5,2)) |
+-----------------------------------+
|                            123.45 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select cast('abc' as signed);
+-----------------------+
| cast('abc' as signed) |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

而对于开头部分不能截取出数字的字符串来说,转换的结果自然就是0了。

关于字符串类型与整数直接进行比较的坑,说穿了就是MySQL中字符串转为数字的逻辑,没遇到过确实可能不太清楚,遇到过一次以后经验就是,看清楚数据库表字段,尽量避免字符串与数字的直接比较。

此外,书写sql语句的时候务必注意不要犯类型的错误,也许查出来的结果是对的,但是由于类型不匹配的原因,将会导致表索引无法用上

posted @ 2017-01-31 10:09  活在夢裡  阅读(27555)  评论(1编辑  收藏  举报