mysql隐式转换导致查询语句不能走索引分析

        如下两条SQL语句，一个不走索引，一个走索引。

        在这里，为什么第一条语句未加单引号就不走索引，而第二条加单引号的就走索引呢？

原因是第一条语句由于类型不匹配，MySQL会做隐式的类型转换，都将其转换为浮点数在比较；而第二条语句因为类型一致，不会转浮点数，就是字符串之间的比较，所以就能正常走索引。

一.进一步了解隐式转换

对于第一种情况：

        比如where string = 1；需要将索引中的字符串转换成浮点数，但是由于'1','1','1a'都会比转化成1,故MySQL无法使用索引只能进行全表扫描，故造成了慢查询的产生。

        这里并不是因为字符串字段值转换成了浮点数？而'1',' 1','1a'转换成1，是整数，类型不匹配导致只能进行全表扫描，而是因为都转换浮点数了，'1','1','1a'本来三个不同的值都是相同的值了，还是打破了有序的规则。

mysql> SELECT CAST('  1' AS SIGNED)=1;
+-------------------------+
| CAST('  1' AS SIGNED)=1 |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT CAST('  1a' AS SIGNED)=1;
+--------------------------+
| CAST('  1a' AS SIGNED)=1 |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set, 1 warning (0.00 sec)
 
mysql> SELECT CAST('1' AS SIGNED)=1;
+-----------------------+
| CAST('1' AS SIGNED)=1 |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

        同时需要注意一点，由于都会转换成浮点数进行比较，而浮点数只有53bit，故当超过最大值的时候，比较会出现问题。

对于第二种情况：

        由于索引建立在int的基础上，而将纯数字的字符串可以百分百转换成数字，故可以使用到索引，虽然也会进行一定的转换，消耗一定的资源，但是最终仍然使用了索引，不会产生慢查询。

mysql> select CAST( '30' as SIGNED) = 30;
+----------------------------+
| CAST( '30' as SIGNED) = 30 |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

 

二.官方解释

        为什么一个小小的''为什么会有这么大的影响呢？根本原因是因为MySQL在对文本类型和数字类型进行比较的时候会进行隐式的类型转换。以下是5.5官方手册的说明：

If both arguments in a comparison operation are strings, they are compared as strings.
两个参数都是字符串，会按照字符串来比较，不做类型转换。
If both arguments are integers, they are compared as integers.
两个参数都是整数，按照整数来比较，不做类型转换。
Hexadecimal values are treated as binary strings if not compared to a number.
十六进制的值和非数字做比较时，会被当做二进制串。
If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
In all other cases, the arguments are compared as floating-point (real) numbers.
所有其他情况下，两个参数都会被转换为浮点数再进行比较

        根据以上的说明，当where条件之后的值的类型和表结构不一致的时候，MySQL会做隐式的类型转换，都将其转换为浮点数在比较。