MySQL數(shù)據(jù)類型隱式轉(zhuǎn)換規(guī)則
現(xiàn)象
今天遇到一個(gè)慢查詢，查詢?nèi)罩菊业铰樵冋Z句是這樣的：
- select * from convert_test where areacode=0001 and period>='20170511' and period<='20170511';
convert_test表結(jié)構(gòu)如下：
- 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='隱式轉(zhuǎn)換測試表';
表中數(shù)據(jù)42W以上。
乍一看，明明創(chuàng)建了一個(gè)唯一索引，正常來說，上面的查詢語句應(yīng)該正好***idx_areacode_period這個(gè)索引的，不應(yīng)該是慢查詢的。
為了查看這個(gè)語句是怎么查詢的，我們?cè)跍y試庫中explain一下：
- mysql> explain select * from convert_test where areacode=0001 and period>='20170511' and period<='20170511';
結(jié)果如下：
可以看到，這里是沒有用到索引的。
原因
定義表的時(shí)候，areacode字段是字符串類型的，查詢的時(shí)候傳入的是0001，這里0001被Mysql當(dāng)做了整數(shù)處理為1，Mysql檢測到areacode這個(gè)字段的查詢類型是整型，就會(huì)全表掃描，將所有行的areacode轉(zhuǎn)換成整型，然后在做查詢處理。
找原因了，就很好解決了，上面的sql語句修改如下：
- mysql> explain select * from convert_test where areacode='0001' and period>='20170511' and period<='20170511';
結(jié)果如下：
可以看到完全***了idx_areacode_period 這個(gè)索引。
擴(kuò)展
上面的period定義的時(shí)候是整型，但是查詢傳入的是字符串類型，那為什么會(huì)***索引的呢？
看一下官方的隱試轉(zhuǎn)換說明：
-
兩個(gè)參數(shù)至少有一個(gè)是 NULL 時(shí)，比較的結(jié)果也是 NULL，例外是使用 <=> 對(duì)兩個(gè) NULL 做比較時(shí)會(huì)返回 1，這兩種情況都不需要做類型轉(zhuǎn)換
-
兩個(gè)參數(shù)都是字符串，會(huì)按照字符串來比較，不做類型轉(zhuǎn)換
-
兩個(gè)參數(shù)都是整數(shù)，按照整數(shù)來比較，不做類型轉(zhuǎn)換
-
十六進(jìn)制的值和非數(shù)字做比較時(shí)，會(huì)被當(dāng)做二進(jìn)制串
-
有一個(gè)參數(shù)是 TIMESTAMP 或 DATETIME，并且另外一個(gè)參數(shù)是常量，常量會(huì)被轉(zhuǎn)換為 timestamp
-
有一個(gè)參數(shù)是 decimal 類型，如果另外一個(gè)參數(shù)是 decimal 或者整數(shù)，會(huì)將整數(shù)轉(zhuǎn)換為 decimal 后進(jìn)行比較，如果另外一個(gè)參數(shù)是浮點(diǎn)數(shù)，則會(huì)把 decimal 轉(zhuǎn)換為浮點(diǎn)數(shù)進(jìn)行比較
-
所有其他情況下，兩個(gè)參數(shù)都會(huì)被轉(zhuǎn)換為浮點(diǎn)數(shù)再進(jìn)行比較
所以,下面的幾個(gè)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 在查詢的時(shí)候，會(huì)將areacode轉(zhuǎn)換成浮點(diǎn)型進(jìn)行比較
