疑似类型隐式转换一例
有群友提了下面这样的问题
请教个隐式转换的问题：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 NULL 或 t IS NOT NULL才对。所以，这么写的话，这个查询是不会有任何结果的，包括列值为NULL的数据。
最后的小结：
-
写SQL时，WHERE条件值记得总是带上引号，避免发生意想不到的情况。
-
对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
-
除了防范类型隐式转换，还要注意防范字符集隐式转换，具体参考MySQL手册12.2 Type Conversion in Expression Evaluation。
全文完
本文首发 https://mp.weixin.qq.com/s/xSahHXxHkEZW1bD6RPl1zw
