mysql varchar int 123 走索引吗？
结论：
当MySQL中字段为int类型时，搜索条件where num='111' 与where num=111都可以使用该字段的索引。
当MySQL中字段为varchar类型时，搜索条件where num='111' 可以使用索引，where num=111 不可以使用索引
验证过程：
建表语句：
| 1 2 3 4 5 6 7 8 9 | CREATE TABLE `gyl` ( `id` int (11) NOT NULL AUTO_INCREMENT, `str` varchar (255) NOT NULL , `num` int (11) NOT NULL DEFAULT '0' , `obj` varchar (255) DEFAULT NULL , PRIMARY KEY (`id`), KEY `str_x` (`str`), KEY `num_x` (`num`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
向表中使用自复制语句插入数据
insert into gyl (`str`,`num`)values(123123,'12313');
insert into gyl (`str`,`num`) select `str`,`num` from gyl;
更改数据 update gyl set num=id,str=id
结果：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | mysql> explain select * from gyl where str=123123 limit 1; + ----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | gyl | ALL | str_x | NULL | NULL | NULL | 262756 | Using where | + ----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ 1 row in set mysql> explain select * from gyl where str= '123123' limit 1; + ----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+ | 1 | SIMPLE | gyl | ref | str_x | str_x | 257 | const | 131378 | Using where | + ----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+ 1 row in set mysql> explain select * from gyl where num= '12313' limit 1;; + ----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+ | 1 | SIMPLE | gyl | ref | num_x | num_x | 4 | const | 131378 | | + ----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+ 1 row in set 1065 - Query was empty mysql> explain select * from gyl where num=12313 limit 1; + ----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+ | 1 | SIMPLE | gyl | ref | num_x | num_x | 4 | const | 131378 | | + ----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+ 1 row in set |
字段类型不同造成的隐式转换，导致索引失效
更多精彩
