mysql对字段进行操作导致索引失效场景分析

建表语句

• a为主键索引，b、c为int类型，d为varchar类型

CREATE TABLE `test` (
  `a` int NOT NULL,
  `b` int DEFAULT NULL,
  `c` int DEFAULT NULL,
  `d` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`a`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

- 插入测试数据

INSERT INTO `test`.`test`(`a`, `b`, `c`, `d`) VALUES (1, 1, 1, 'a');
INSERT INTO `test`.`test`(`a`, `b`, `c`, `d`) VALUES (2, 2, 2, 'b');
INSERT INTO `test`.`test`(`a`, `b`, `c`, `d`) VALUES (3, 3, 3, 'c');
INSERT INTO `test`.`test`(`a`, `b`, `c`, `d`) VALUES (4, 4, 4, 'e');

• 添加d字段为普通索引 ALTER TABLE test.test ADD INDEX idx_d(d) USING BTREE;

测试语句

语句一：select * from test where a=1; -- 是否利用索引：主键索引

语句二：select * from test where a='1'; -- 是否利用索引：主键索引

语句三：select * from test where d='1'; -- 是否利用索引：普通索引

语句四：select * from test where d=1; -- 是否利用索引：未利用索引

select 'a'=0; 结果为：1

select 'a'=1; 结果为：0

select 'b'=0; 结果为：1

select 'b'=0; 结果为：0

select '1'=1; 结果为：1

语句一：select * from test where a=1;

语句二：select * from test where a='1';

语句三：select * from test where d='1';

语句四：select * from test where d=1;

- 思考问题

select * from test where a='1'; a字段为int类型，‘1’为字符类型，为什么可以利用主键索引。因为‘1’最终被转为1（字符=》整形）

select * from test where d=1; d字段为varchar类型，1为int类型，为什么无法利用普通索引。因为d(varchar类型)字段对应的最终值被转为0 ，也就是select * from test where 0=1

select 'a'=0; 结果为：1，因为字符类型在转为整数类型时，统统是转为0

select 'a'=1; 结果为：0， 因为字符类型在转为整数类型时，统统是转为0

select '1'=1; 结果为：1， 因为字符类型在转为整数类型时，统统是转为0

结论

在mysql中对于InnoDB引擎

1. 在需要进行字段类型转换对比时。（如：where a（varchar）=1，或者 where id（int）=‘1’），会将字符类型=》转为转为整数类型
2. 所有的字符类型在转为整数类型时，统统是转为0，例如：where a（varchar）=1实际上a被转为0最终转为为wehere 0=1，所以等式不成立
3. 在索引字段中，只要是有对索引字段进行操作的，不管是类型转换，加减操作、截取等都会导致索引失效