mysql字符串等于失效_MySql整型索引和字符串索引失效或隐式转换问题

问题概述

写代码的时候,有一段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类型的字段无论是以字符串查询还是以数值型查询都会走索引。

结论

当我们使用的字段是数值类型时，加引号或者不加引号(sql中单引号和双引号实现相同效果)都不影响索引的使用

当我们的字段是字符串类型时，不加引号的查询无法使用索引，加引号的查询才可正常使用索引

参考资料:

Kotlin 开发者社区

国内第一Kotlin 开发者社区公众号，主要分享、交流 Kotlin 编程语言、Spring Boot、Android、React.js/Node.js、函数式编程、编程思想等相关主题。

Kotlin 开发者社区

本文分享 CSDN - 东海陈光剑。

如有侵权，请联系 support@oschina.cn 删除。

本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。