结论：
当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 setmysql> 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 setmysql> 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 set1065 - Query was emptymysql> 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 |