mysqlvarcharint123走索引吗？
结论：
当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
字段类型不同造成的隐式转换，导致索引失效
以上是 mysqlvarcharint123走索引吗？ 的全部内容， 来源链接： utcz.com/z/532742.html
