Mysql中的隐式转换

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

首先建立一张数据表，并插入一些数据

DROP TABLE IF EXISTS data_test1;
CREATE TABLE `data_test1`(
`id` int(11) NOT NULL,
`num1` int(11) NOT NULL DEFAULT '0',
`num2` varchar(11) NOT NULL DEFAULT '',
`type1` int(4) NOT NULL DEFAULT '0',
`type2` int(4) NOT NULL DEFAULT '0',
`str1` varchar(100) NOT NULL DEFAULT '',
`str2` varchar(100) DEFAULT NULL,
PRIMARY KEY(`id`),
KEY `num1`(`num1`),
KEY `num2`(`num2`),
KEY `type1`(`type1`),
KEY `str1`(`str1`),
KEY `str2`(`str2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
DROP PROCEDURE IF EXISTS pre_test1;
 
DELIMITER //
CREATE PROCEDURE `pre_test1`()
BEGIN
 
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
WHILE i<500000 DO
SET i = i+1;
SET @str1=SUBSTRING(MD5(RAND()),1,20);
 
IF i%100 = 0 THEN
SET @str2 = NULL;
ELSE
SET @str2 = @str1;
END IF;
 
INSERT INTO data_test1(`id`, `num1`, `num2`, `type1`, `type2`, `str1`, `str2`)
VALUES(CONCAT('',i), CONCAT('', i), CONCAT('', i), i%5, i%5, @str1, @str2);
 
IF i%10000 = 0 THEN
COMMIT;
END IF;
 
END WHILE;
END;
// DELIMITER;
 
CALL pre_test1();

写4条查询语句：
select * from data_test1 where num1 = 10000;
select * from data_test1 where num1 = ‘10000’;
select * from data_test1 where num2 = 10000;
select * from data_test1 where num2 = ‘10000’;

按顺序称之为查询1,2,3,4
这其中只有查询3相对比较慢

对sql进行explain

查询1,2,4相同:

都使用了索引

查询3为全表查询:

列num1为int类型，num2为varchar类型
四条查询中，都分别对num1,num2进行了整型和字符串的条件查询
但是num1的整型和字符串条件查询，速度相当
而num2的整型要比字符串条件查询，慢了很多，进行了全表查询，没有使用到索引

Mysql官方文档中的隐式转换规则:

The following rules describe how conversion occurs for comparison operations:
1.If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For
NULL <=> NULL, the result is true. No conversion is needed.
2.If both arguments in a comparison operation are strings, they are compared as strings.
3.If both arguments are integers, they are compared as integers. Hexadecimal values are treated as binary strings if not compared to a
number.
4.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. 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. A single-row subquery from a table or tables
is not considered a constant. For example, if a subquery returns an
integer to be compared to a DATETIME value, the comparison is done as
two integers. The integer is not converted to a temporal value. To
compare the operands as DATETIME values, use CAST() to explicitly
convert the subquery value to DATETIME.
5.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.
6.In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric
operands takes place as a comparison of floating-point numbers.
来自 https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html?spm=5176.100239.blogcont47339.5.1FTben

那么基于以上规则，查询2,3都会触发隐式转换，且根据最后一条规则，比较符两边的参数都会转化为浮点进行比较。
查询2:
select * from data_test1 where num1 = ‘10000’;

num1由int转化为浮点数，字符串’10000’转化为浮点, num1转换后是可以唯一确定的，不影响索引的使用

查询3:
select * from data_test1 where num2 = 10000;

num2由varchar转化为浮点数， 整型10000转化为浮点，num2转后是不能唯一确定的，很多字符串在进行浮点转化时都能转化为10000，所以，无法使用索引。
例如，
‘10000a’，‘010000’，'10000’等等都能转为浮点数10000

官方文档中有说明：

The following examples illustrate conversion of strings to numbers for comparison operations: mysql> SELECT 1 > ‘6x’;
-> 0 mysql> SELECT 7 > ‘6x’;
-> 1 mysql> SELECT 0 > ‘x6’;
-> 0 mysql> SELECT 0 = ‘x6’;
-> 1 For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If
str_col is an indexed string column, the index cannot be used when
performing the lookup in the following statement: SELECT * FROM
tbl_name WHERE str_col=1; The reason for this is that there are many
different strings that may convert to the value 1, such as ‘1’, ’ 1’, or ‘1a’.
来自 https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html?spm=5176.100239.blogcont47339.5.1FTben

通过查阅资料发现，字符串转浮点有如下规则:
1.不以数字开头的字符串，都将转化为0，如"abc",“a12bc”,"a123"都换转化为0
2.以数字开头的字符串转换时，会进行截取，从第一个字符截取至第一个非数字的内容为止，如:'123abc’会转换为123，'012abc’会转换为12，‘53141592sax22’会转换为’53141592’