MySQL隐式转换，导致索引失效

前言

MySQL索引失效的情况有很多，下面主要介绍数据隐式转换导致索引失效。

这一点在我们平常书写SQL语句的时候可能是最容易忽略的但是又是经常容易犯的错误。

开门见山，下面我们看一下官方文档描述。

官方文档描述

1. 两个参数至少有一个是NULL时，比较的结果也是NULL，特殊的情况是使用<=>对两个NULL做比较时会返回1，这两种情况都不需要做类型转换
2. 两个参数都是字符串，会按照字符串来比较，不做类型转换
3. 两个参数都是整数，按照整数来比较，不做类型转换
4. 十六进制的值和非数字做比较时，会被当做二进制串
5. 有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp
6. 有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较，如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较
7. 所有其他情况下，两个参数都会被转换为浮点数再进行比较

准备数据

准备以下字段的表，并且为num1建立索引。

CREATE TABLE `type` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `num1` varchar(255) DEFAULT NULL,
  `num2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_num1` (`num1`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码

在数据库中插入以下数据

查询数据

方式一

SELECT * FROM type WHERE num1 = 1000
复制代码

注意：这是num1是varchar类型，而查询的时候给的where条件是1000的整形。

这时查询得到的数据时包含其他脏数据的。

explain分析查询语句，结果走了全表扫描。

方式二

在对num1字段的where语句拼接上单引号后查询的结果和我们预期一样。

SELECT * FROM type WHERE num1 = '1000'
复制代码

explain分析查询语句，结果走了索引，索引生效了。

那么这个字符串隐式转换的规则是什么呢？为什么num1='1000a'、'01000'和'1000'这三种情形都能匹配上呢？查阅相关资料发现规则如下：

1. 不以数字开头的字符串都将转换为0。如'abc'、'a123bc'、'abc123'都会转化为0；
2. 以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止。比如'123abc'会转换为123，'012abc'会转换为012也就是12，'5.3a66b78c'会转换为5.3，其他同理。

验证结果

结论

1. 当操作符左右两边的数据类型不一致时，会发生隐式转换。
2. 当 where 查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
3. 当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
4. 字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

所以，我们在写 SQL 时一定要养成良好的习惯，查询的字段是什么类型，等号右边的条件就写成对应的类型。特别当查询的字段是字符串时，等号右边的条件一定要用引号引起来标明这是一个字符串，否则会造成索引失效触发全表扫描。