浅谈MySql整型索引和字符串索引失效或隐式转换问题⽬录
问题概述
问题重现
问题引申
结论
问题概述
今天在上班时，DBA突然出来⼀段sql，表⽰该sql存在隐式转换，不⾛索引。经过我们的查看后，发现是类型varchar的字段，我们使⽤条件传⼊了数值型的值，由于担⼼违反保密协议，在此就不贴图了，由我重现⼀下类似情况给⼤家看⼀下。
问题重现
⾸先我们先创建⼀张⽤户表test_user，其中USER_ID为了效果我们设置为varchar类型且加上唯⼀索引。
CREATE TABLE test_user (
ID int(11) NOT NULL AUTO_INCREMENT,
USER_ID varchar(11) DEFAULT NULL COMMENT '⽤户账号',
USER_NAME varchar(255) DEFAULT NULL COMMENT '⽤户名',
AGE int(5) DEFAULT NULL COMMENT '年龄',
COMMENT varchar(255) DEFAULT NULL COMMENT '简介',
PRIMARY KEY (ID)
UNIQUE KEY UNIQUE_USER_ID (USER_ID) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表格数据如下（嘻嘻数据依旧使⽤与上次Mysql的⽂章相同的数据，但是要注意表结构不同。）
ID USER_ID USER_NAME AGE COMMENT
1111开⼼菜鸟18今天很开⼼
2222悲伤菜鸟21今天很悲伤
3333认真菜鸟30今天很认真
4444⾼兴菜鸟18今天很⾼兴
5555严肃菜鸟21今天很严肃
接下来我们执⾏以下sql
EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111;
发现给出的解释结果如下：
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1SIMPLE test_user ALL5Using where
我们给条件加上引号后再解释以下：
EXPLAIN SELECT * FROM test_user WHERE USER_ID = '111';
这时候我们发现varchar类型的字段在作为字符串查询的时候使⽤了索引，在以数值类型进⾏查询时是不使⽤索引的。
问题引申
那么问题来了，如果字段是整型的且加上索引，以字符串查询时会不会也不⾛索引呢？实践出真知，让我们再接着往下测试⼀下。
-- 将USER_ID的类型修改为整型
CREATE TABLE test_user (
ID int(11) NOT NULL AUTO_INCREMENT,
USER_ID int(11) DEFAULT NULL COMMENT '⽤户账号',
USER_NAME varchar(255) DEFAULT NULL COMMENT '⽤户名',
AGE int(5) DEFAULT NULL COMMENT '年龄',
COMMENT varchar(255) DEFAULT NULL COMMENT '简介',
查看mysql索引PRIMARY KEY (ID),
UNIQUE KEY UNIQUE_USER_ID (USER_ID) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111;
EXPLAIN SELECT * FROM test_user WHERE USER_ID = '111';
在执⾏了上⾯两个语句后我们发现，int类型的字段⽆论是以字符串查询还是以数值型查询都会⾛索引。
结论
1. 当我们使⽤的字段是数值类型时，加引号或者不加引号（sql中单引号和双引号实现相同效果）都不影响索引的使⽤
2. 当我们的字段是字符串类型时，不加引号的查询⽆法使⽤索引，加引号的查询才可正常使⽤索引
综上所述，我认为以后写sql的时候注意最好都加上引号，避免这种字符串类型的不⾛索引的情况发⽣，更深层次的原理需要再挖掘⼀下，如果⼤家有什么意见可以探讨⼀下。
到此这篇关于浅谈MySql整型索引和字符串索引失效或隐式转换问题的⽂章就介绍到这了，更多相关MySql整型索引和字符串索引失效内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论