Python社区  »  DATABASE

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

brucebat • 1 周前 • 11 次点击  
阅读 14

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

人生苦短,不如养狗

  • 背景
  • “无序”的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字段同时按照2526这样的顺序进行排列。通过这种方式,我们就能获得我们想要的排序顺序了。

四、总结

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

本文使用 mdnice 排版

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/70796
 
11 次点击  
分享到微博