MySQL中什么情况下类型转换会导致索引失效

如果对 MySQL 索引不了解，可以看一下我的另一篇博文： MySQL-进阶篇-索引（索引概述、索引的结构、索引的分类、索引的语法、性能分析工具、索引的使用规则、索引的设计原则）

1. 问题引入

我们知道，在 MySQL 中，如果发生了隐式类型转换，有可能会导致索引失效，那什么情况下隐式类型转换会导致索引失效呢

2. 准备工作

我们创建一张名为 test 的表，探究什么情况下隐式类型转换会导致索引失效

• a 字段是 int 类型，b 字段是 varchar 类型
• a 字段和 b 字段都建立了索引

DROP TABLE IF EXISTS `test`;CREATE TABLE `test`
(`a` int                                                           NULL DEFAULT NULL,`b` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,INDEX `inx_a` (`a` ASC) USING BTREE,INDEX `idx_b` (`b` ASC) USING BTREE
) ENGINE = InnoDBCHARACTER SET = utf8mb4COLLATE = utf8mb4_0900_ai_ciROW_FORMAT = Dynamic;

3. 案例分析

3.1 正常情况

我们先来看正常的情况下，也就是没有发生隐式类型转换的情况下，索引是否生效

explain
select *
from test
where a = 1;explain
select *
from test
where b = '1';

第一条 SQL 语句的执行结果

第二条 SQL 语句的执行结果

可以看到，正常情况下 idx_a 索引和 idx_b 索引都生效了

3.2 发生了隐式类型转换的情况

接下来我们看一下发生了隐式类型转换的情况下索引是否生效

explain
select *
from test
where a = '1';explain
select *
from test
where b = 1;

第一条 SQL 语句的执行结果

第二条 SQL 语句的执行结果

可以看到，第一条 SQL 语句走了索引，但是第二条 SQL 语句却没有走索引

为什么呢，同样是发生了隐式类型转换，为什么 a 字段对应的索引生效了，但是 b 字段对应的索引却失效了

要解答这个问题，我们需要知道 MySQL 隐式类型转换的规则

4. MySQL隐式类型转换的规则

4.1 案例引入

我们通过几个例子来理解 MySQL 隐式类型转换的规则

select 1 = 'a';select 0 = 'a';select 2 = '2';select 0 = '聂可以';select 1 = '+1';select -1 = '-1';select 10 = '+10a';select 10 = '10a';select 1 = 'a1';select 10 = '1a0';

以上 select 语句的输出结果（结果返回 1 表示条件成立，返回 0 表示条件不成立）

select 1 = 'a'; # 返回0select 0 = 'a'; # 返回1select 2 = '2'; # 返回1select 0 = '聂可以'; # 返回1select 1 = '+1'; # 返回1select -1 = '-1'; # 返回1select 10 = '+10a'; # 返回1select 10 = '10a'; # 返回1select 1 = 'a1'; # 返回0select 10 = '1a0'; # 返回0

以下几个 SQL 语句返回 1 不难理解，因为字符串转换为数字后确实与要比较的数字相等

• select 2 = ‘2’; # 返回1

• select 1 = ‘+1’; # 返回1

• select -1 = ‘-1’; # 返回1

但是以下几个 SQL 语句的结果就有点费解了

• select 0 = ‘聂可以’; # 返回1
• select 10 = ‘10a’; # 返回1

4.2 MySQL 中隐式类型转换的规则

要搞明白上述几个例子，我们需要知道 MySQL 中隐式类型转换的规则，在 MySQL 中

1. 当字符串转换为数值时，如果字符串是合法数字开头的，会尝试将字符串转换为数值类型，如果字符串是合法数字开头，但整个字符串又不是完全合法的数字，则只会转换字符串开头的合法数字部分
2. 如果字符串不是以合法数字开头，那么转换结果为 0
3. 发生类型转换时，绝大多数情况是从字符串转换为数值，而不是从数值转换为字符串，（只有极少数情况是从数值转换为字符串，例如使用 concat 函数将数值和字符串拼接成一个新的字符串时）

select 123 + '456';

select concat(123, '456');

---

知道 MySQL 中隐式类型转换的规则后，再次理解上述例子，就比较容易了

• ‘a’ 是非数值类型的字符串，转换为数值后的值为 0
• ‘聂可以’ 是非数值类型的字符串，转换为数值后的值为 0
• ‘10a’ 字符串的前面部分合法，转换为数值后的值为 10
• ‘1a0’ 字符串的前面部分合法，转换为数值后的值为 1（转换过程中如果遇到非法字符就会停止转换过程）

---

我们再次分析最初的例子

explain
select *
from test
where a = '1';explain
select *
from test
where b = 1;

a 字段是 int 类型， ‘1’ 是字符串类型，类型不匹配，发生隐式类型转换，也就是将字符串 ‘1’ 转换成数值，再与数值 1 比较，这个转换过程并没有破坏索引树的结构，索引会生效

b 字段是 varchar 类型， 1 是数值类型，类型不匹配，发生隐式类型转换，也就是将每一行记录中的 b 字段都转换为数值，再与数值 1 进行比较，这个转换过程破坏了索引树的结构，索引不会生效

4.3 验证 MySQL 隐式类型转换的规则

我们往表中插入两条数据

insert into test
values (1, 'NieKeYi');insert into test
values (2, '2024年10月13日');

执行以下 select 语句，验证 MySQL 隐式类型转换的规则

---

select *
from test
where b = 0;

查询结果如下

---

select *
from test
where b = 2024;

查询结果如下

---

select *
from test
where a = '2a';

查询结果如下

5. 总结

当数据库表的字段为 varchar 类型，而传入数据的类型为 int 时，会导致索引失效