MySQL 隱式類型轉換導致索引失效問題

一、首先創建一張測試表，隨便插入一點測試數據：

CREATE TABLE `test_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no` int(11) NOT NULL DEFAULT '0' COMMENT '訂單號，int型',
  `no_str` varchar(64) NOT NULL DEFAULT '' COMMENT '訂單號，字元串',
  PRIMARY KEY (`id`),
  KEY `no` (`no`) USING BTREE,
  KEY `no_str` (`no_str`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

為了方便對比，我建了兩個欄位，都建立了索引，no （整型）和 no_str （字元串）。

那麼查詢時就會出現以下四種情況：

1、欄位是int型，但是查詢時使用字元串類型

EXPLAIN select * from test_index where no = '1005';

可以看到能正常使用索引。而且查詢結果也似乎正常。no 等於 1005 的數據都能查詢出來。其實這時候MySQL已經進行了隱式類型轉換，轉成了浮點型處理，索引才能正常使用。

需要特別註意的是，這種查詢可能會出現問題。

比如將查詢條件由 1005 變成 1005aa，發現也同樣能查詢到 1005 的數據，這是因為MySQL在進行欄位值比較的時候，發生了隱式類型轉換，將 1005aa 轉換成浮點型 1005 進行比較，所以能查詢出 1005 的記錄。

2、欄位是int型，查詢也用int型

 這個沒什麼說的，不管是查詢結果和使用索引都達到預期效果。

3、欄位是字元串類型，查詢使用int型

首先發現 no_str 索引失效了，並且結果也不是預期結果，會將額外的數據查詢出來，比如查詢 1005 會把 1005aa 的記錄也查出來，這是因為MySQL進行了隱式類型轉換，換成了浮點型進行比較。

4、欄位是字元串類型，查詢使用字元串

正常使用索引，結果也正常。

所以在實際開發中，需要特別註意這種隱式的類型轉換導致的問題，可能會導致索引失效，查詢結果也出現問題。