MySQL數據類型隱式轉換規則
現象
今天遇到一個慢查詢，查詢日志找到慢查詢語句是這樣的：
- select * from convert_test where areacode=0001 and period>='20170511' and period<='20170511';
convert_test表結構如下：
- CREATE TABLE `convert_test` (
- `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `areacode` char(12) NOT NULL DEFAULT '',
- `period` int(6) unsigned NOT NULL DEFAULT 0,
- `mid_price` int(10) unsigned NOT NULL DEFAULT 0,
- `mid_change` float NOT NULL DEFAULT 0,
- `updated_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (`id`),
- UNIQUE KEY `idx_areacode_period` (`areacode`,`period`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='隱式轉換測試表';
表中數據42W以上。
乍一看，明明創建了一個唯一索引，正常來說，上面的查詢語句應該正好***idx_areacode_period這個索引的，不應該是慢查詢的。
為了查看這個語句是怎么查詢的，我們在測試庫中explain一下：
- mysql> explain select * from convert_test where areacode=0001 and period>='20170511' and period<='20170511';
結果如下：
可以看到，這里是沒有用到索引的。
原因
定義表的時候，areacode字段是字符串類型的，查詢的時候傳入的是0001，這里0001被Mysql當做了整數處理為1，Mysql檢測到areacode這個字段的查詢類型是整型，就會全表掃描，將所有行的areacode轉換成整型，然后在做查詢處理。
找原因了，就很好解決了，上面的sql語句修改如下：
- mysql> explain select * from convert_test where areacode='0001' and period>='20170511' and period<='20170511';
結果如下：
可以看到完全***了idx_areacode_period 這個索引。
擴展
上面的period定義的時候是整型，但是查詢傳入的是字符串類型，那為什么會***索引的呢？
看一下官方的隱試轉換說明：
-
兩個參數至少有一個是 NULL 時，比較的結果也是 NULL，例外是使用 <=> 對兩個 NULL 做比較時會返回 1，這兩種情況都不需要做類型轉換
-
兩個參數都是字符串，會按照字符串來比較，不做類型轉換
-
兩個參數都是整數，按照整數來比較，不做類型轉換
-
十六進制的值和非數字做比較時，會被當做二進制串
-
有一個參數是 TIMESTAMP 或 DATETIME，并且另外一個參數是常量，常量會被轉換為 timestamp
-
有一個參數是 decimal 類型，如果另外一個參數是 decimal 或者整數，會將整數轉換為 decimal 后進行比較，如果另外一個參數是浮點數，則會把 decimal 轉換為浮點數進行比較
-
所有其他情況下，兩個參數都會被轉換為浮點數再進行比較
所以,下面的幾個sql語句有相同的效果：
- select * from convert_test where areacode=0001 and period>='20170511' and period<='20170511';
- select * from convert_test where areacode=1 and period>='20170511' and period<='20170511';
- select * from convert_test where areacode=0001.0 and period>='20170511' and period<='20170511';
- select * from convert_test where areacode=1.0 and period>='20170511' and period<='20170511';
mysql 在查詢的時候，會將areacode轉換成浮點型進行比較
