MySQL查询时类型转换(隐形数据类型转换)

梦里梦外; 2023-05-21 08:59 26阅读 0赞

可能被你忽略的MySQL隐形数据类型转换

1.创建一张表并插入数据

SET FOREIGN_KEY_CHECKS=0;
    
    DROP TABLE IF EXISTS `mytest`;
    CREATE TABLE `mytest` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `param` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    
    # 插入数据
    INSERT INTO `mytest` VALUES ('1', 'a');
    INSERT INTO `mytest` VALUES ('2', '0');
    INSERT INTO `mytest` VALUES ('3', 'a123');
    INSERT INTO `mytest` VALUES ('4', '123');

2.查询数据，发现问题的现象

# 1）查询param不等于0的数据:varchar类型传入varchar类型
    SELECT * FROM mytest WHERE param!='0';

SQL执行结果截图见下图(结果是正确的)：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NTI3NjU3MTM4_size_16_color_FFFFFF_t_70][]

# 2）查询param不等于0的数据:varchar类型传入int类型
    SELECT * FROM mytest WHERE param!=0;

SQL执行结果截图见下图（结果是不准确的）：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NTI3NjU3MTM4_size_16_color_FFFFFF_t_70 1][]

3.什么问题导致的呢？

（1）原因：当MySQL字段类型和传入的数据类型不一致的时候，MySQL会进行隐形的数据类型转换

（2）当列字段类型为varchar类型，传入数据类型与字段类型不一致（例如传入int类型数据）时，若字符串是以数字开头并且全部为数字，则转换为整个字符串（例如：“123”转换为123）；若字符串是以数字开头并且并不全部为数字，则只转换数字部分（例如“211abc”转换为211）。

（3）若字符串不是一数字开头，则会被转换为0（例如：“abcd”或“abc123” 都将会转换为0）

关于隐形数据类型转换，还有很多，这只是其中的一丢丢。

--------------------

有位朋友在***面试***时曾被***面试官***问到这样的问题；原题是这样描述的，一张表myparam，其中有一个varchar类型的字段param，向表中插入10条数据，5条param字段为a的数据，5条param字段为0的数据,编写sql语句:select \* from myparam where param 不等于0;(问题描述时,并没有指出是param!="0"还是param!=0具体的哪种情况),为什么就是查不出来数据来。下面看一下表结构数据和查询结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NTI3NjU3MTM4_size_16_color_FFFFFF_t_70 2][]

通过截图，很显然，面试官的所说的sql语句应该是：SELECT \* FROM myparam WHERE param !=0;（传入的数据时int类型的0，数据类型不一致）

分析：

1、param类型为varchar类型

2、传入的参数数据为int类型

3、因为param字段varchar类型与传入数据的int类型不一致，所以会有一个隐形的数据类型转换

4、param字段为a的数据，因为开头不是以数字开头的，所以在转换的时候就会被转换为0；param字段为0的数据，是数字，所以在转换的时候会被转换为数字0；因此，表中现存的10条数据，就会转10条param为0的数据了，所以在执行SELECT \* FROM myparam WHERE param !=0时，是查不出来任何数据的。

考点：隐形数据类型转换问题

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NTI3NjU3MTM4_size_16_color_FFFFFF_t_70]: /images/20230521/aa0f0a35f50044b18f56a8f4dc853a9c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NTI3NjU3MTM4_size_16_color_FFFFFF_t_70 1]: /images/20230521/1c575f7ac51c4791b186b9d7f9c8a6cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NTI3NjU3MTM4_size_16_color_FFFFFF_t_70 2]: /images/20230521/286c25e22c6d4f8c895c5e3b7c124c9f.png