爱站程序员基地MySQL 字符串类型用数字可以查出来 MySQL隐式类型转换
一、发现问题
1、在一次MySQL查询中，某字段为 varchar 字符串类型，传入参数值为 int 数字类型，发现查询的结果和预期的不一致。 如： 某两列 name=‘11’ , name = ‘11aa’ 。 where name = 11 ， 可以查到 ‘11’ 和 ‘11aa’ 两个结果，这里是错误的；而 where name =‘11’ ，能得到预期结果。
2、反之，字段为 int 数字类型，传入参数值为 varchar 字符串类型，也能查到数据，同样查询的结果和预期的不一致。如：age=2的数据有2条。where age = 2 ， 可以正常查到数据 ； 而 where age = ‘2aabbcc’，查到的数据结果和 where age = 2 是一样的，这里是错误的，应该查不到数据。
二、代码理解
1、针对【一】中的问题，描述的不好理解，晦涩难懂，下面用代码加以理解，推动理解问题！
2、在MySQL数据中，创建一个 implicit 表 ， 如下： （MySQL version 5.7+）
SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for implicit-- ----------------------------DROP TABLE IF EXISTS `implicit`;CREATE TABLE `implicit` (`id` varchar(255) NOT NULL COMMENT \'编号\',`name` varchar(255) DEFAULT NULL COMMENT \'名称\',`age` int(11) DEFAULT NULL COMMENT \'年龄\',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=\'MySQL Implicit conversion （MySQL 隐式类型转换）\';-- ------------------------------ Records of implicit-- ----------------------------INSERT INTO `implicit` VALUES (\'11\', \'小明\', \'2\');INSERT INTO `implicit` VALUES (\'11qq\', \'小红\', \'2\');INSERT INTO `implicit` VALUES (\'12\', \'小新\', \'54\');INSERT INTO `implicit` VALUES (\'tt11\', \'小刚\', \'92\');
3、implicit 表数据如下：
三、针对问题进行测试
1、字段类型是 varchar 字符串，传入参数为 int 数字类型 。
1.1、– 字段类型是 varchar ,传入参数是 varchar 【预期正确结果】
SELECT *FROM implicit aWHERE a.id = \'11\' ;
1.2、– 字段类型是 varchar , 传入参数是 int 【错误结果】
SELECT *FROM implicit aWHERE a.id = 11 ;
2、字段类型是 int 数字类型，传入参数为 varchar 字符串类型。
2.1、– 字段类型是 int类型,传入参数是 int类型 【预期正确结果】
SELECT *FROM implicit aWHERE a.age = 2 ;
2.2、– 字段类型是 int类型,传入参数是varchar类型 【错误结果】
SELECT *FROM implicit aWHERE a.age = \'2aa\' ;
四、问题原因及避免
1、原因： 当MySQL字段类型和传入条件数据类型不一致时，会进行隐形的数据类型转换（MySQL Implicit conversion）
2、若字符串是以数字开头，且全部都是数字，则转换为数字结果是整个字符串；部分是数字，则转换为数字结果是截止到第一个不是数字的字符为止。 理解： varchar str = “123dafa”，转换为数字是123 。 SELECT ‘123dafa’+1 ; — 124 。
3、若字符串不是以数字开头，则转换为数字结果是 0 。 varchar str = “aabb33” ; 转换为数字是 0 。 SELECT ‘aabb33’+100 ; — 100 。
4、更多隐式转换规则摘录：
两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换两个参数都是字符串，会按照字符串来比较，不做类型转换两个参数都是整数，按照整数来比较，不做类型转换十六进制的值和非数字做比较时，会被当做二进制串有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较所有其他情况下，两个参数都会被转换为浮点数再进行比较。
五、问题总结
1、MySQL 隐式转换问题，隐藏的深，不容易被发现，在进行 delete ， update 等操作时，一不小心很容易大问题，从而造成事故。
2、对于 delete , update 等操作时，建议先使用 select 语句，看看获取的结果和预期的是否一致，再进行操作，相对会更安全一些。
3、为了便于更好理解，MySQL 隐式转换规则，增加下面sql测试理解。
3.1、SELECT \'a10\'+10 ; -- 10SHOW WARNINGS ; -- WARNINGS: Truncated incorrect DOUBLE value: \'a10\'查看warnings可以看到隐式转化把字符串转为了double类型。但是因为字符串是非数字型的，所以就会被转换为0，因此最终计算的是0+10=10 .3.2、SELECT \'10a\'+10 ; -- 20SHOW WARNINGS ; -- WARNINGS: Truncated incorrect DOUBLE value: \'10a\'3.3、SELECT \'a\'=0 ; -- 1 , 相当于 trueSHOW WARNINGS ; -- WARNINGS: Truncated incorrect DOUBLE value: \'a\'3.4、SELECT \'a23423\' = 0 ; -- 1 , 相当于 trueSHOW WARNINGS ; -- WARNINGS：Truncated incorrect DOUBLE value: \'a23423\'3.5、SELECT \'11dafdfwwe\'=11; -- 1, 相当于 trueSHOW WARNINGS ; -- WARNINGS：Truncated incorrect DOUBLE value: \'11dafdfwwe\'3.6、SELECT 11= 11 ; -- 1, 相当于 trueSHOW WARNINGS ; -- 无3.7、SELECT \'abc\'=\'abc\' ; -- 1, 相当于 trueSHOW WARNINGS ; -- 无3.8、SELECT \'abc\'=\'abc232322\' ; -- 0 , 数据类型一样，不会进行转换SHOW WARNINGS ; -- 无3.9、SELECT \'a\'+\'b\'; -- 0 , 都转换为0了， 0+0=0 。SHOW WARNINGS ; -- WARNINGS: Truncated incorrect DOUBLE value: \'a\' , Truncated incorrect DOUBLE value: \'b\'3.10、SELECT \'a\'+\'b\'=\'c\' ; -- 1 ,等价于 0+0=0 --> 0=0=1。SHOW WARNINGS ; -- WARNINGS: Truncated incorrect DOUBLE value: \'a\' , Truncated incorrect DOUBLE value: \'b\' , Truncated incorrect DOUBLE value: \'c\'
