首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySql整型索引和字符串索引失效或隐式转换问题

MySql整型索引和字符串索引失效或隐式转换问题

作者头像
一个会写诗的程序员
发布2019-12-11 10:09:35
1.9K0
发布2019-12-11 10:09:35
举报

问题概述

写代码的时候,有一段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的文章MySQL使用UNION连接两个查询排序失效相同的数据,但是要注意表结构不同。)

ID

USER_ID

USER_NAME

AGE

COMMENT

1

111

开心菜鸟

18

今天很开心

2

222

悲伤菜鸟

21

今天很悲伤

3

333

认真菜鸟

30

今天很认真

4

444

高兴菜鸟

18

今天很高兴

5

555

严肃菜鸟

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

1

SIMPLE

test_user

ALL

5

Using 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 '简介',
  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. 当我们的字段是字符串类型时,不加引号的查询无法使用索引,加引号的查询才可正常使用索引

参考资料:

https://www.cnblogs.com/runningRookie/p/11108768.html

本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 问题概述
  • 问题重现
  • 问题引申
  • 结论
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档