类型隐式转换导致的？No，并不是

╰+攻爆jí腚メ 2023-07-15 02:48 33阅读 0赞

> 疑似类型隐式转换一例

有群友提了下面这样的问题

请教个隐式转换的问题：
    SELECT count(*) FROM test WHERE time >= 2019-05-17;
    time列是datetime类型，
    这条SQL的执行结果是相当于 where 1, 这个是什么原因呢？
    
    
    SQL执行有个warnings：
    Warning | 1292 | Incorrect datetime value: '1997' for column 'time' at row
    
    
    从告警信息来看，是把 2019-05-17 做了数学减法运算，得到常量 1997, 
    再把常量1997转换为 datetime 类型，再跟time字段做比较。
    
    
    但用函数 cast(1997 as datetime)，也是同样的告警信息，但结果是 NULL
    
    
    那么该SQL是否可以等价为：
    SELECT count(*) FROM test WHERE time >= NULL
    
    
    这个SQL结果会是0，因为跟NULL值比较的结果是NULL。
    
    
    虽然WHERE条件错写成一个算式，但执行时没有报错，只有一个告警信息，
    
    
    感觉还是因为发生了类型隐式转换，用不到索引，否则不会是全表扫描。

这是我的第一次回复内容

事实上，条件 WHERE time >= 2019-05-17，
    的意思是：time >= 1997，这是表达式 2019-05-17 的结算结果。
    这个不是类型隐式转换，是你SQL没写对。

我们看到SQL的执行计划是这样的

![format_png][]

对于第二个疑问：为什么会走**全表扫描**计划呢？

我的看法是这样的：首先，上面的SQL条件相当于 WHERE time >= 1997。其次，MySQL认为"1997"不是合法的日期时间类型数据，看到执行计划中有告警

Incorrect datetime value: '1997' for column

因此，time >= 1997 这个条件，就会被当做一个逻辑表达式，因为不是 0（FALSE），也不是 NULL，所以就会被认为是永远为真（TRUE）。也就是说，time列中所有不是FALSE或NULL的值都符合条件。

我们可以测试确认这个说法：

# 表中dt列是datetime类型，但允许为NULL
    [root@yejr.me]> show create table t1\G
    *************************** 1. row ***************************
           Table: t1
    Create Table: CREATE TABLE `t1` (
      `c1` int NOT NULL,
    ...
      `dt` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`c1`),
      KEY `k2` (`dt`)
    ) ENGINE=InnoDB;
    
    
    
    
    # 查看所有数据
    [root@yejr.me]> select * from t1;
    +-----+-----+---------------------+
    | c1  | ... | dt                  |
    +-----+-----+---------------------+
    |   2 | ... | NULL                |
    |   3 | ... | 2017-11-01 15:44:27 |
    |   5 | ... | 2020-02-13 16:02:55 |
    +-----+-----+---------------------+
    3 rows in set (0.00 sec)
    
    
    
    
    # c1=2的记录中，dt列值为NULL，不符合条件
    [root@yejr.me]> select * from t1 where dt >= 1997;
    +-----+-----+---------------------+
    | c1  | ... | dt                  |
    +-----+-----+---------------------+
    |   3 | ... | 2017-11-01 15:44:27 |
    |   5 | ... | 2020-02-13 16:02:55 |
    +-----+-----+---------------------+
    2 rows in set (0.00 sec)

很明显，只要表中dt列值**不为NULL、不为0**（符合日期时间格式的数据也肯定不会是0）的数据都会被读取到。

这种情况下，即便dt列有索引，也会**因为需要扫描的数据太多**，从而优化器认为直接走**全表扫描的效率要更好**，所以也无法使用索引。

还有个疑问，WHERE条件写成 **time >= cast(1997 as datetime)** 时会怎样？这种情况下，因为 **cast(1997 as datetime)** 的结果是 NULL， 所以WHERE条件等同于 **time>= NULL**，对NULL的运算是不能这么写的， 而应该写成 **dt IS NULL** 或 **t IS NOT NULL**才对。所以，这么写的话，这个查询是不会有任何结果的，包括列值为NULL的数据。

最后的小结：

1.  写SQL时，WHERE条件值记得总是**带上引号**，避免发生意想不到的情况。
2.  对NULL值的判断，必须是用 **IS NULL **或 **IS NOT NULL**，不能是大小值的判断。另外，WHERE条件中的NULL其实是**可以用到索引**的，例如：
    
        [root@yejr.me]> desc select * from t1 where dt is NULL\G
        ************************ 1. row ************************
                   id: 1
          select_type: SIMPLE
                table: t1
           partitions: NULL
                 type: ref
        possible_keys: k2
                  key: k2
              key_len: 5
                  ref: const
                 rows: 1
             filtered: 100.00
                Extra: Using where
3.  除了防范类型隐式转换，还要注意防范字符集隐式转换，具体参考MySQL手册**12.2 Type Conversion in Expression Evaluation**。

**全文完**

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

由我主讲的知数堂「MySQL优化课」第17期已发车，我们的课程从第15期就升级成MySQL 8.0版本了，现在上车刚刚好，一起开启MySQL 8.0的修行之旅吧。

扫码入群了解「MySQL优化」课吧，有助教妹子在等着哟

![format_png 1][]

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

另外，我在腾讯课堂的短课程《**MySQL性能优化**》已开课，本课程讲解读几个MySQL性能优化的核心要素：**合理利用索引，降低锁影响，提高事务并发度**。

下面是报名小程序码

![format_png 2][]

点“在看”给我一朵小黄花![format_png 3][]

![format_png 4][]

[format_png]: /images/20230529/b791b3f9ce2f41feb21a9809167ea696.png
[format_png 1]: /images/20230529/a0b502a963f249a9970b87db6d7d9b4a.png
[format_png 2]: /images/20230529/af3126588877456184c1361ded321050.png
[format_png 3]: /images/20230529/0da7bc343cad4da5ab2dddf7b2a36424.png
[format_png 4]: /images/20230529/32df05c7e3d447d7b645c1918a0942fb.png