结论：
当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
|
字段类型不同造成的隐式转换，导致索引失效
原文：https://www.cnblogs.com/yangzailu/p/12526431.html
如果您也喜欢它,动动您的小指点个赞吧
602392714
清零编程群