MySQL中的隐式转换造成的索引失效

雨点打透心脏的1/2处 2023-06-30 09:18 12阅读 0赞

在mysql查询中，当查询条件左右两侧类型不匹配的时候会发生隐式转换，可能导致查询无法使用索引。

[官方的隐试转][Link 1]换说明：

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

常见情况：

id为int

``SELECT * FROM `test` WHERE id= '10000';`` **左边为int类型**`10000`，转换为浮点数还是`10000`，右边字符串类型`'10000'`，转换为浮点数也是`10000`。两边的转换结果都是唯一确定的，没有破坏索引规则。所以不影响使用索引。

id为varchar

``SELECT * FROM `test` WHERE id = 10000;`` **左边是字符串类型**`'10000'`，转浮点数为10000是唯一的，右边`int`类型`10000`转换结果也是唯一的。但是，因为左边是检索条件，`'10000'`转到`10000`虽然是唯一，但是其他字符串也可以转换为`10000`，比如`'10000a'`，`'010000'`，`'10000'`等等都能转为浮点数`10000`，这样的情况下，索引规则被破坏，是不能用到索引的。

## 结论 ##

1.  当我们使用的字段是数值类型时，加引号或者不加引号（sql中单引号和双引号实现相同效果）都不影响索引的使用
2.  当我们的字段是字符串类型时，不加引号的查询无法使用索引，加引号的查询才可正常使用索引

综上所述，我认为以后写sql的时候注意最好都加上引号，避免这种字符串类型的不走索引的情况发生

[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html?spm=5176.100239.blogcont47339.5.1FTben