准备数据:
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');
结果:
字符串索引中,复合类型查询索引失效。