BruceBat's Blog
BruceBat's Blog

竹杖芒鞋轻胜马,谁怕?一蓑烟雨任平生


  • 首页

  • 归档

  • 分类

  • 标签

  • 搜索
NIO I/O 计算机科学 操作系统 设计模式 随记 WebSocket 计算机网络 注册中心 经典电影 xxl-job 分布式 分布式任务调度 MySQL DevOps Docker 多线程 有趣的问题 Mybatis-Plus Mybatis Java 数据结构

有趣的MySQL(一):“无序”的in运算符查询结果

发表于 2020-04-25 | 分类于 数据库 | 0 | 阅读次数 780

人生苦短,不如养狗

  • 背景
  • “无序”的in子句查询结果
  • 如何使“无序”变得“有序”

一、背景

  想必各位开发同学对于MySQL中的in运算符一定不陌生,今天闲鱼就想和大家来聊一聊in运算符。
  分享in运算符的原因其实很简单,主要是前段时间在项目中使用的时候出现一个小小的问题,具体问题参考下图的执行结果:



  可以看到最终的执行结果是“无序”的,竟然没有按照in运算符后面指定顺序的值进行排序。看到这样的结果,不禁想让人问一句:


  莫慌,下面让我们来仔细分析一下in运算符是如何执行的。


二、 “无序”的in运算符查询结果

  在刚刚的查询语句中,id字段是主键字段。仔细观察一下刚刚的查询结果,除了没有按照指定的顺序进行查询结果返回,好像并不能算是无序,反而像是按照id字段的自然顺序进行排列的。为了验证一下猜想,我们来看一下上面SQL的执行计划:


  可以看到,这里查询使用了主键,同时type显示的是range,也就是使用索引范围查询,而不是system和const或是eq_ref。这就说明,使用in运算符来进行查询的时候,其效果和>、<、=等比较运算符是一样的(p.s. in运算符也是比较运算符)。也就是说,使用in运算符之后,如果没有使用order by来指定其他的排序顺序,那么最终的结果集将会按照索引的自然顺序进行排序。
  那么,in运算符是如何执行的呢?其实很简单,in运算符既然也是比较运算符中一个,那么它的执行过程其实和<或>这种比较运算符是一样的,比如下面的这个SQL语句:

select * from user where age > 0 and age < 25;

  此时进行<运算符的执行是在(0, 25)范围内遍历对应的索引进行查询操作,将符合条件的索引全部查出,然后再通过主键索引查询到具体的值。in运算符其实也是类似,唯一不同的是in运算符的查询范围,比如下面这个例子:

select * from user where age in ('25','26');

  在这个例子中,in运算符的查询范围是按照[25, 25]和[26, 26]这两个范围进行查询,在这两个范围中遍历索引查询出所有满足条件的值,然后按照索引的自然顺序返回结果集。看到这里大家是不是想到了另一种等价的SQL写法,就是or加上=运算符:

select * from user where age = '25' or age = '26';

  看到这里,大家应该明白了为什么in运算符执行出来的结果集会是”无序”的了,因为其本质就相当于or加上=运算符。所以无论怎么变更in运算符后值的顺序,其查询出来的结果集都是一样的。

三、如何使“无序”变得“有序”

  弄明白了in运算符的执行过程,那么难道就真的没有办法按照指定值的顺序进行查询了吗?难道发明MySQL的大佬就这么粗糙的吗?当然不是,我们可以下面的方法进行指定顺序查询:

select * from user where age in ('25','26') order by field(`age`, ’25’,’26’);

  上面的方法其实是显式地指明了最终结果集的排序顺序,按照age字段同时按照25、26这样的顺序进行排列。通过这种方式,我们就能获得我们想要的排序顺序了。

四、总结

  其实一开始会犯这种常识性错误,主要是因为一般在项目中更多的只会在update操作或者一些不需要指定顺序的查询中使用in运算符,所以对于in运算符的执行原理就会有一些忽视。所以大家还是要好好学习,天天向上,早日变秃变强,成为琦玉老师那样的强者。

本文使用 mdnice 排版

brucebat wechat
一个闲鱼程序猿的微信公众号
  • 本文作者: brucebat
  • 本文链接: https://www.swzgeek.com/archives/2020-04-25-22-09-15
  • 版权声明: 本博客所有文章除特别声明外,均采用CC BY-NC-SA 3.0 许可协议。转载请注明出处!
# NIO # I/O # 计算机科学 # 操作系统 # 设计模式 # 随记 # WebSocket # 计算机网络 # 注册中心 # 经典电影 # xxl-job # 分布式 # 分布式任务调度 # MySQL # DevOps # Docker # 多线程 # 有趣的问题 # Mybatis-Plus # Mybatis # Java # 数据结构
闲鱼Coder学Docker(二):初识Docker
闲鱼Coder学Docker(三):Docker镜像中分层结构
  • 文章目录
  • 站点概览
brucebat

brucebat

一个有梦想的咸鱼程序猿

46 日志
8 分类
22 标签
RSS
Github E-mail
Creative Commons
© 2020 — 2023 brucebat
苏ICP备20002207号-1

苏公网安备 32011302320859号

0%