大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说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
|
CREATETABLE`gyl` (
`id` int(11) NOTNULLAUTO_INCREMENT,
`str` varchar(255) NOTNULL,
`num` int(11) NOTNULLDEFAULT"0",
`obj` varchar(255) DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`str_x` (`str`),
KEY`num_x` (`num`)
) ENGINE=InnoDB DEFAULTCHARSET=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* fromgyl wherestr=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 inset
mysql> explain select* fromgyl wherestr="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 inset
mysql> explain select* fromgyl wherenum="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 inset
1065 - Query was empty
mysql> explain select* fromgyl wherenum=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 inset
|
字段类型不同造成的隐式转换，导致索引失效
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/9450.html
