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语句的时候务必注意不要犯类型的错误，也许查出来的结果是对的，但是由于类型不匹配的原因，将会导致表索引无法用上。