類型隐式轉換導緻的？No，并不是

有群友提了下面這樣的問題

請教個隐式轉換的問題：
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
[[email protected]]> 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;


# 檢視所有資料
[[email protected]]> 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，不符合條件
[[email protected]]> 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其實是可以用到索引的，例如：

[[email protected]]> 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      

1. 除了防範類型隐式轉換，還要注意防範字元集隐式轉換，具體參考MySQL手冊12.2 Type Conversion in Expression Evaluation。