一、首先创建一张测试表,随便插入一点测试数据:
CREATE TABLE `test_index` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`no` int(11) NOT NULL DEFAULT '0' COMMENT '订单号,int型',
`no_str` varchar(64) NOT NULL DEFAULT '' COMMENT '订单号,字符串',
PRIMARY KEY (`id`),
KEY `no` (`no`) USING BTREE,
KEY `no_str` (`no_str`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
为了方便对比,我建了两个字段,都建立了索引,no (整型)和 no_str (字符串)。
那么查询时就会出现以下四种情况:
1、字段是int型,但是查询时使用字符串类型
EXPLAIN select * from test_index where no = '1005';
可以看到能正常使用索引。而且查询结果也似乎正常。no 等于 1005 的数据都能查询出来。其实这时候MySQL已经进行了隐式类型转换,将字符串'1005'转成了1005浮点型处理,索引才能正常使用。
需要特别注意的是,这种查询可能会出现问题。
比如将查询条件由 1005 变成 1005aa,发现也同样能查询到 1005 的数据,这是因为MySQL在进行字段值比较的时候,发生了隐式类型转换,将 1005aa 转换成浮点型 1005 进行比较,所以能查询出 1005 的记录。
2、字段是int型,查询也用int型
这个没什么说的,不管是查询结果和使用索引都达到预期效果。
3、字段是字符串类型,查询使用int型
首先发现 no_str 索引失效了,并且结果也不是预期结果,会将额外的数据查询出来,比如查询 1005 会把 1005aa 的记录也查出来,这是因为MySQL进行了隐式类型转换,将表中字段数据换成了浮点型再和 1005 进行比较。
4、字段是字符串类型,查询使用字符串
正常使用索引,结果也正常。
总结:
在MySQL的官方文档中,对于表达式计算中的类型转换章节给出的解释是:当比较不同类型的操作数时,会隐式发生类型转换,自动地把字符串转成数字。
所以在实际开发中,需要特别注意这种隐式的类型转换导致的问题,可能会导致索引失效,查询结果也出现问题。