文章目录
- 1. 问题引入
- 2. 准备工作
- 3. 案例分析
- 3.1 正常情况
- 3.2 发生了隐式类型转换的情况
- 4. MySQL隐式类型转换的规则
- 4.1 案例引入
- 4.2 MySQL 中隐式类型转换的规则
- 4.3 验证 MySQL 隐式类型转换的规则
- 5. 总结
如果对 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 中
- 当字符串转换为数值时，如果字符串是合法数字开头的，会尝试将字符串转换为数值类型，如果字符串是合法数字开头，但整个字符串又不是完全合法的数字，则只会转换字符串开头的合法数字部分
- 如果字符串不是以合法数字开头，那么转换结果为 0
- 发生类型转换时，绝大多数情况是从字符串转换为数值，而不是从数值转换为字符串，（只有极少数情况是从数值转换为字符串，例如使用 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 时，会导致索引失效
