MySQL 字符串类型用数字可以查出来 MySQL字符串类型会转换成数字 MySQL隐式类型转换

短命女 2021-12-23 07:57 829阅读 0赞

## **          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 表数据如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70][]

**三、针对问题进行测试**

1、字段类型是 varchar 字符串，传入参数为 int 数字类型 。

1.1、-- 字段类型是 varchar ,传入参数是 varchar 【预期正确结果】

SELECT * 
    FROM implicit a
    WHERE a.id = '11' ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70 1][]

1.2、-- 字段类型是 varchar , 传入参数是 int 【错误结果】

SELECT * 
    FROM implicit a
    WHERE a.id = 11 ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70 2][]

2、字段类型是 int 数字类型，传入参数为 varchar 字符串类型。

2.1、-- 字段类型是 int类型,传入参数是 int类型 【预期正确结果】

SELECT * 
    FROM implicit a
    WHERE a.age = 2 ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70 3][]

2.2、-- 字段类型是 int类型,传入参数是varchar类型 【**错误结果**】

SELECT * 
    FROM implicit a
    WHERE a.age = '2aa' ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70 4][]

**四、问题原因及避免**

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、更多隐式转换规则[摘录][Link 1]：

*  两个参数至少有一个是 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 ; -- 10
    SHOW WARNINGS ; -- WARNINGS: Truncated incorrect DOUBLE value: 'a10'
    查看warnings可以看到隐式转化把字符串转为了double类型。但是因为字符串是非数字型的，所以就会被转换为0，因此最终计算的是0+10=10 .
    
    3.2、SELECT '10a'+10 ; -- 20 
    SHOW WARNINGS ; -- WARNINGS: Truncated incorrect DOUBLE value: '10a'
    
    3.3、SELECT 'a'=0 ; -- 1 , 相当于 true 
    SHOW WARNINGS ; -- WARNINGS: Truncated incorrect DOUBLE value: 'a'
    
    3.4、SELECT 'a23423' = 0 ; -- 1 , 相当于 true 
    SHOW WARNINGS ; -- WARNINGS：Truncated incorrect DOUBLE value: 'a23423'
    
    3.5、SELECT '11dafdfwwe'=11; -- 1, 相当于 true 
    SHOW WARNINGS ; -- WARNINGS：Truncated incorrect DOUBLE value: '11dafdfwwe'
    
    3.6、SELECT 11= 11 ; -- 1, 相当于 true 
    SHOW WARNINGS ; -- 无 
    
    3.7、SELECT 'abc'='abc' ; -- 1, 相当于 true 
    SHOW 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'

参考资料： [MySQL隐式转化整理][Link 1]

[Mysql 一个字段定义成int类型，查询时传入String，会截取字符串][Mysql _int_String]

[https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html][https_dev.mysql.com_doc_refman_5.7_en_type-conversion.html]

[https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html][https_dev.mysql.com_doc_refman_5.7_en_cast-functions.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70]: /images/20211223/5078295076d64544babd5081eac6b9aa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70 1]: /images/20211223/b250433812dc4cf7bf5da7b78772e0ac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70 2]: /images/20211223/21c06fb87e444daab9b1e7d40d558675.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70 3]: /images/20211223/22b09194534443d3acb746c56e62935b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hhSGFfU2ly_size_16_color_FFFFFF_t_70 4]: /images/20211223/1bc80a630e2b4bf3ab1683fd57e31aec.png
[Link 1]: https://www.cnblogs.com/rollenholt/p/5442825.html
[Mysql _int_String]: https://blog.csdn.net/xiaolyuh123/article/details/64441817
[https_dev.mysql.com_doc_refman_5.7_en_type-conversion.html]: https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html
[https_dev.mysql.com_doc_refman_5.7_en_cast-functions.html]: https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html