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

转载于:https://www.cnblogs.com/micrari/p/6358652.html