一、问题现象
mysql> select 0='abc'; +---------+ | 0='abc' | +---------+ | 1 | +---------+
二、排查原因
今天开发找我说了个查询现象，0值和字符串相比竟然想等，以前没有遇到过该现象，所以排查了下
重点： 数据库开发规范中一定要要求等值对比或者其他运算的时候一定要要求数据类型一致
mysql> select cast('abc' as signed);
+-----------------------+
| cast('abc' as signed) |
+-----------------------+
| 0 |
+-----------------------+
1 row in set, 1 warning (0.27 sec)
mysql> select cast('1abc' as signed);
+------------------------+
| cast('1abc' as signed) |
+------------------------+
| 1 |
+------------------------+
1 row in set, 1 warning (0.27 sec)
mysql> select cast('abc1' as signed);
+------------------------+
| cast('abc1' as signed) |
+------------------------+
| 0 |
+------------------------+
1 row in set, 1 warning (0.27 sec)
mysql> select cast('23abc1' as signed);
+--------------------------+
| cast('23abc1' as signed) |
+--------------------------+
| 23 |
+--------------------------+
1 row in set, 1 warning (0.27 sec)
我们可以从上面的实验看到，只要字符串最左的一位不是数值的话，整体字符串就会变为0值，所以在对比的时候一定要注意数据类型一致
官网文档：https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html
