MySQL发生隐式类型转换一定会导致索引失效？你先回去等通知吧，今天面试就到这里了

大家好，我是程序员牛肉。

关于MySQL的各种问题一直都是面试的热点，在这其中，作为MySQL提速利器的索引更是重中之重。而我们今天就聊一聊关于索引失效的一个场景。

先看一道SQL题：请判断以下SQL语句会不会走索引？

表结构：

create table test.users
(
    id     int                              not null
        primary key,
    name   varchar(100)                     not null,
    gender enum ('male', 'female', 'other') not null));

SQL语句：

select * from users where id = '1';

我们输入的参数是一个字符串，而对应的主键索引 id 类型是int。也就是说这两个值在比较的时候，会发生隐式的类型转换。

而按照我们背的八股来讲，有些糊涂的同学一想到“发生隐式类型转换”就立马认为这条SQL语句的索引失效了。可结果真的是这样吗？

我们看一看执行结果：

坏了，这怎么和我们之前背的八股不一样，执行结果怎么显示这条SQL语句走索引了？说好的发生隐式类型转换会导致索引失效呢？

为什么这条SQL语句的执行结果显示还可以走id这个主键索引呢?

要想搞懂这个，就首先要明白为什么我们之前背的八股会说发生隐式类型转换会导致索引失效。

索引通常存储的是列的值，这些值是按照特定的数据类型存储的。例如，如果一个字段是VARCHAR类型，索引中存储的就是字符串。当执行查询时，数据库系统会使用索引来快速定位到符合条件的行。如果查询条件的数据类型与索引列的数据类型不一致，数据库系统需要逐行进行类型转换。

而因为你要逐行进行类型转换，就会导致索引失效。这其实也是在索引列上使用函数以及对索引列进行其他操作导致索引失效的原因。

[说人话就是索引列存储的是原值，1和‘1’本质上不是一个东西。你用数字1怎么可能搜索到对应的‘1’。]

比如下面这个SQL语句就会导致索引失效：

select * from users where id+1 = '2'

执行结果：

因为索引保存的是索引字段的原始值，而不是id+1表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

嗷！现在我们知道隐式类型转换导致索引失效的前提是：“隐式类型转换需要在索引列上进行”。

那我们回头看一看那条SQL语句：

select * from users where id = '1';

在这条语句中，id是int类型，参数是字符串。我们虽然说会发生隐式类型转换。但是谁告诉你被转换的就一定是id这个索引列呢？你怎么知道不是输入的参数‘1’被转为数字1了？

所以又有一个新的问题：MySQL究竟是把字符串转为数字，还是把数字转为字符串？

我们用一个很简单的SQL语句就可以得出结论：

select 1+'1';

如果MySQL的默认转换规则是将字符串转为数字，那么这条语句的执行结果应该是1+1=2。如果转换规则是将数字转为字符串，那么这条语句的执行结果应该是‘11’。

而这条语句的执行结果为：

通过这条语句，我们已经知道MySQL的默认转换规则是将字符串转为数字。那么我们一开始提到的那条SQL语句：

select * from users where id = '1';

它之所以没有发生索引失效的原因是在发生隐式类型转换的时候，其实是把字符串‘1’转换为了数字1。而索引列上没有进行任何操作。

select * 
from users 
where id = （字符串强制转为数字）'1'

而我们前面已经说过了，只有在索引列上发生类型转换才会导致索引失效。

那如果我们把这个表的id设置为字符串类型，把入参设置为数字。那么这条SQL语句在执行的时候，就会导致失效。

表结构：

create table test.users
(
    id     varchar(31)                      not null
        primary key,
    name   varchar(100)                     not null,
    gender enum ('male', 'female', 'other') not null
);

SQL语句：

EXPLAIN SELECT * FROM users WHERE id=1

这条语句的执行结果为：

这条语句就会出现索引失效的情况。回顾我们之前分析的内容，这条SQL语句就可以被看作：

select * 
from users 
where （字符串强制转为数字） id = 1;

这也符合我们之前说的：“在索引列上发生隐式类型转换会导致索引失效”。

今天关于索引失效的场景就介绍到这里了。相信通过我的文章，你已经对“隐式类型转换导致索引失效”有了更加深入的理解。希望我的文章可以帮到你。

关于索引失效，你有遇到过什么比较坑的问题吗？欢迎在评论区留言。

关注我，带你了解更多计算机干货。

end