类型隐式转换导致的?No,并不是

原创
2020/04/06 22:00
阅读数 4.7K

疑似类型隐式转换一例

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

请教个隐式转换的问题: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的执行计划是这样的

对于第二个疑问:为什么会走全表扫描计划呢?

我的看法是这样的:首先,上面的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: t1Create 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 NULLt 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: refpossible_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

全文完

本文首发 https://mp.weixin.qq.com/s/xSahHXxHkEZW1bD6RPl1zw

展开阅读全文
打赏
0
2 收藏
分享
加载中
更多评论
打赏
0 评论
2 收藏
0
分享
返回顶部
顶部