问题现象

我想关于索引失效的问题，大家应该都不陌生，本编文章分享的也是导致索引失效的其中一个场景：隐式转换，如果再分的详细一些，应该是隐式转换的其中一个场景：字段类型不匹配。

下面，我们来看一下具体的案例

假设有如下一张表：

CREATE TABLE t_user_demo (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
name VARCHAR(20) DEFAULT NULL COMMENT '姓名',
phone char(11) DEFAULT NULL COMMENT '手机号',
age TINYINT DEFAULT NULL COMMENT '年龄',
sex CHAR(1) DEFAULT NULL COMMENT '性别',
PRIMARY KEY (id),
UNIQUE KEY idx_name_phone (name, phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

看明白了吗？也就是说如果字段是数字类型，那么等号右边无论是带引号还是不带引号都是可以走索引的！

问题分析

要弄清楚这一点，我们必须要先知道MySQL的隐式转换规则。

我们可以执行如下SQL语句: SELECT 1+'1'; 得到的结果是2，也就是说MySQL应该是通过把字符'1'转换成数值1，然后再进行计算，得出结果。

最终，通过查阅MySQL官方文档，找到隐式转换的规则如下：

• 如果一个或两个参数为NULL，则比较结果为NULL，除非使用了NULL-safe的<=>等式比较操作符。对于NULL <=> NULL，结果为真。不需要转换。

• 如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较。

• 如果两个参数都是整数，则将它们作为整数进行比较。

• 如果不与数字进行比较，十六进制值将被视为二进制字符串。

• 如果其中一个参数是 a TIMESTAMP or DATETIME列而另一个参数是常量，则在执行比较之前，该常量将转换为时间戳。这样做是为了对ODBC更友好。这不是为的参数完成的 IN()。为安全起见，在进行比较时始终使用完整的日期时间、日期或时间字符串。例如，要在使用BETWEEN日期或时间值时获得最佳结果 ，请使用CAST()将值显式转换为所需的数据类型。 来自一个或多个表的单行子查询不被视为常量。例如，如果子查询返回要与DATETIME值进行比较的整数，则比较将作为两个整数进行。整数不会转换为时间值。要将操作数作为DATETIME值进行比较 ，请使用CAST()将子查询值显式转换为DATETIME。

• 如果其中一个参数是十进制值，则比较取决于另一个参数。如果另一个参数是十进制或整数值，则将参数作为十进制值进行比较，如果另一个参数是浮点值，则作为浮点值进行比较。

• 在所有其他情况下，参数将作为浮点（实数）数进行比较。例如，字符串和数字操作数的比较是作为浮点数的比较进行的。

对照规则，那么文章中一开始的案例，就对应上了最后一条规则项：字符串和数字操作数的比较是作为浮点数的比较进行的。

也就是说，当执行如下SQL语句时：

select * from t_user_demo where name = 'zz' and phone = 13933333333;

则会被解析成：

select * from t_user_demo where name = 'zz' and CAST(phone AS UNSIGNED) = 13933333333;

最终，还是由于索引列发上发生了函数操作，因此索引失效了。

而如果把phone改为数字类型，那么下面这段SQL语句中phone = '13933333333'显然也是不匹配的，但此时按照隐式转换的规则，转换函数会发生在'13933333333'上。

select * from t_user_demo where name = 'zz' and phone = '13933333333';

则会被解析成：

select * from t_user_demo where name = 'zz' and phone = CAST('13933333333' AS UNSIGNED) ;

索引列上并没有发生函数操作，因此还是可以正常走索引的。

解决方式

无论你是否了解MySQL关于隐式转换的这些规则，都建议你按照匹配字段类型来，其实在【线上踩坑分享】这篇专栏中，有不少坑都是因为对隐藏的规则不了解所导致的，所以，我们要做的是，按照标准规范来执行，尽量避免触发一些隐藏的规则，还是那句话：不要妄图在代码中“炫技（显摆）”