有趣的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字段同时按照25、26这样的顺序进行排列。通过这种方式，我们就能获得我们想要的排序顺序了。

四、总结

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