mysql varchar int 123 走索引吗？
程序员文章站
2022-03-26 12:25:42
结论： 当MySQL中字段为int类型时，搜索条件where num='111' 与where num=111都可以使用该字段的索引。当MySQL中字段为varchar类型时，搜索条件where num='111' 可以使用索引，where num=111 不可以使用索引 验证过程： 建表语句： 1 ......
结论：
当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 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
|
字段类型不同造成的隐式转换，导致索引失效
上一篇： 购物车之上线版思路