mysql 索引 隐式转换 隐式转换索引失效

写博客的原因：最开始是在群里有人说自己sql不走索引了。

结果发现是字符型=右边没有加’'引号。mysql隐式转换导致索引失效

巧的是这个问题，在另一个群里也有人问过。

然后被个大佬秒回

我自己也没细研究过Mysql隐式转换。抽个时间，写了这篇博客。

 

 

 

准备工作
数据库版本：5.7.11

创建一个表，num_int （int类型）num_str（varchar类型）
两个字段各自加了普通索引

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `num_int` int(11) NULL DEFAULT NULL,
  `num_str` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `inx_num_int`(`num_int`) USING BTREE,
  INDEX `inx_num_str`(`num_str`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1001007 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

创建10w的数据量（所用时间约 3分钟多）创建数据量的目的是为了让数据走索引而不是全表扫描

DROP PROCEDURE IF EXISTS test_for; 
CREATE PROCEDURE `test_for`()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 100000 DO
				SET i = i + 1;
        INSERT INTO test (`id`, `num_int`, `num_str`)
        VALUES (null,123,'123');
        -- 5k数据提交一次事务
        IF i % 5000 = 0 THEN
            COMMIT;
        END IF;
	END WHILE;
END;
-- 执行存储过程
CALL test_for();

必要的测试数据

INSERT INTO `test`.`test` (`id`, `num_int`, `num_str`) VALUES (101, 123, '123');
INSERT INTO `test`.`test` (`id`, `num_int`, `num_str`) VALUES (102, 102, '123a');
INSERT INTO `test`.`test` (`id`, `num_int`, `num_str`) VALUES (103, 103, 'a1b2c3');
INSERT INTO `test`.`test` (`id`, `num_int`, `num_str`) VALUES (104, 104, '123abc');
INSERT INTO `test`.`test` (`id`, `num_int`, `num_str`) VALUES (105, 105, 'a123');
INSERT INTO `test`.`test` (`id`, `num_int`, `num_str`) VALUES (106, 106, '0123');

 
 
 
测试一下

EXPLAIN SELECT * FROM test WHERE num_int = 123;
EXPLAIN SELECT * FROM test WHERE num_int = '123';
EXPLAIN SELECT * FROM test WHERE num_str = 123;
EXPLAIN SELECT * FROM test WHERE num_str = '123';

以上4个sql，第二个和第三都是隐式转换，为什么只有第三没走索引。

先看了下mysql官方

 

 

mysql官方文档地址

https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html机翻：

原文：

根据官方：第二个sql（SELECT * FROM test WHERE num_int = ‘123’） 整数字段 = ‘整数’。隐式转换后，都变成浮点数计算，123转为浮点数还是123，转换后正常走索引。 第三个sql呢？

测试一下

SELECT * FROM test WHERE num_str = 123;

在这里发现的结果并不是如我们所想的，看下官方，我们得知。已本身数字开头并且连续的数字，不论前面是否头0，后面跟多少非数字内容。都已碰到非数字截断，并转换把数字内容成数字。机翻：

原文：

这也就解释了为什么索引失效了。