mysql隐式转换会造成索引失效的原因-海口c网

mysql隐式转换会造成索引失效的原因

article/2025/7/14 23:57:57

现在我们看一个例子

比如现在我有一张表叫做test

涉及的字段有id code name age address

id 是int数值类型

code 是varchar字符串类型

name 是varchar字符串类型

age是int 数值类型

address是varchar 字符串类型

创建语句：

CREATE TABLE test (

id INT PRIMARY KEY,

code VARCHAR(255),

name VARCHAR(255),

age INT,

address VARCHAR(255) );

id是主键索引，这个就不必多说了，现在我们给code也建立一个普通索引，后续就是根据这个索引来阐述隐式转化导致索引失效的问题

创建索引语句

CREATE INDEX idx_code ON test (code);

id	code	name	age	address
1	111zs	张三	28	广东汕头
2	111ls	李四	29	广东揭阳
3	111ww	王五	29	广东潮州

现在我执行一条语句：

select * from test where code = 111;

大家觉得这条语句会有什么结果呢？

code是一个字符串类型，但是查询的值是一个数值类型，那么现在查询的结果是

张三李四王五都会被查出来，这是为什么呢？

其实这就是Mysql的隐式转化造成的结果

当查询的字段与等号的值的类型不一致时，Mysql则会根据需要自动进行隐式转化，那么到底是如何一种隐式转化呢？我们看这7点，

第一点不满足，我们没有为null；

第二点不满足，一个是code是字符串类型，等号右边的值是数值类型；

第三点不满足，一个是code是字符串类型，等号右边的值是数值类型；

第四点不满足，第五点不满足，第六点不满足；

第七点满足，所以会转化会共同转化为浮点类型。

code = 111，将code字符串类型转化为浮点类型，那么转化为浮点类型也是有一定说法的：

不以数字开头的字符串都将转换为0。如'xxx'、'xx123dd'、'dd1222'都会转化为0；
以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止。比如'123abc'会转换为123，'012abc'会转换为012也就是12，'5.3a66b78c'会转换为5.3，其他同理。

那我们看这个转化规则，也就是数据表里面的code字符串能转化为111浮点的有3行记录

也就是张三、李四、王五的code分别是111zs,111ls,111ww

这就是隐式转化带来的问题。

我们用explain执行计划，看看此时code索引是否失效了。

那我们来看看如果我们正常的select，看看结果

select * from test where code = '111zs'；

发现查询出来的结果就是张三一个人

看到结果，确实是失效了，走了全表扫描。rows也是3。

好，现在我们换一个方向，就是重新新建一个字段叫做score，分数，然后类型是int类型，同时也为它创建索引

CREATE INDEX idx_score ON test (score);

我们再查询的时候，等号右边的值用字符串

ALTER TABLE test
ADD COLUMN score INT AFTER age;

执行语句：

-- 更新张三的分数
UPDATE test SET score = 88 WHERE name = '张三';-- 更新李四的分数
UPDATE test SET score = 89 WHERE name = '李四';-- 更新王五的分数
UPDATE test SET score = 80 WHERE name = '王五';

执行这条语句： SELECT * FROM test WHERE score = '88'

查询返回结果：