准備數據:
CREATE TABLE `test_idx` (
`i` int(10) NOT NULL,
`s` varchar(10) NOT NULL,
KEY `i` (`i`),
KEY `s` (`s`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into test_idx(i, s) values (1,'1'),(2,'2'),(3,'3'),(4,'4'),(5,'5'),(6,'6'),(7,'7'),(8,'8'),(9,'9');
測試1:
explain select * from test_idx where i = 1;
explain select * from test_idx where i = '1';
結果:
數字索引,帶上引號仍然能夠使用索引。
測試2:
explain select * from test_idx where s = 1;
explain select * from test_idx where s = '1';
結果:
字符串索引,不帶引號,索引失效。
測試3:
explain select * from test_idx where i in(1,2);
explain select * from test_idx where i in(1,'2');
結果:
數字索引中,復合類型查詢索引失效。
測試4:
explain select * from test_idx where s in('1','2');
explain select * from test_idx where s in(1,'2');
結果:
字符串索引中,復合類型查詢索引失效。