<section id="nice" data-tool="mdnice编辑器" data-website="https://www.mdnice.com" style="font-size: 16px; color: black; padding: 0 10px; line-height: 1.6; word-spacing: 0px; letter-spacing: 0px; word-break: break-word; word-wrap: break-word; text-align: left; font-family: Optima-Regular, Optima, PingFangSC-light, PingFangTC-light, 'PingFang SC', Cambria, Cochin, Georgia, Times, 'Times New Roman', serif; margin-top: -10px;"><blockquote data-tool="mdnice编辑器" style="display: block; font-size: 0.9em; overflow: auto; overflow-scrolling: touch; border-left: 3px solid rgba(0, 0, 0, 0.4); color: #6a737d; padding-top: 10px; padding-bottom: 10px; padding-left: 20px; padding-right: 10px; margin-bottom: 20px; margin-top: 20px; border-left-color: rgb(239, 112, 96); background: #fff9f9;"> <p style="font-size: 16px; padding-top: 8px; padding-bottom: 8px; margin: 0px; color: black; line-height: 26px;">人生苦短，不如养狗</p> </blockquote> <ul data-tool="mdnice编辑器" style="margin-top: 8px; margin-bottom: 8px; padding-left: 25px; color: black; list-style-type: disc;"> <li><section style="margin-top: 5px; margin-bottom: 5px; line-height: 26px; text-align: left; color: rgb(1,1,1); font-weight: 500;">背景</section></li><li><section style="margin-top: 5px; margin-bottom: 5px; line-height: 26px; text-align: left; color: rgb(1,1,1); font-weight: 500;">“无序”的in子句查询结果</section></li><li><section style="margin-top: 5px; margin-bottom: 5px; line-height: 26px; text-align: left; color: rgb(1,1,1); font-weight: 500;">如何使“无序”变得“有序”</section></li></ul> <img src="https://bruce-test-store.oss-cn-hangzhou.aliyuncs.com/study/img/mysql.jpg" width="450" height="350" data-tool="mdnice编辑器" style="display: block; margin: 0 auto; max-width: 100%;"> <h2 data-tool="mdnice编辑器" style="margin-top: 30px; margin-bottom: 15px; padding: 0px; font-weight: bold; color: black; border-bottom: 2px solid rgb(239, 112, 96); font-size: 1.3em;"><span class="prefix" style="display: none;"></span><span class="content" style="display: inline-block; font-weight: bold; background: rgb(239, 112, 96); color: #ffffff; padding: 3px 10px 1px; border-top-right-radius: 3px; border-top-left-radius: 3px; margin-right: 3px;">一、背景</span><span class="suffix"></span><span style="display: inline-block; vertical-align: bottom; border-bottom: 36px solid #efebe9; border-right: 20px solid transparent;"> </span></h2> <p data-tool="mdnice编辑器" style="font-size: 16px; padding-top: 8px; padding-bottom: 8px; margin: 0; line-height: 26px; color: black;">  想必各位开发同学对于MySQL中的in运算符一定不陌生，今天闲鱼就想和大家来聊一聊in运算符。<br>   分享in运算符的原因其实很简单，主要是前段时间在项目中使用的时候出现一个小小的问题，具体问题参考下图的执行结果：</p><br> <img src="https://bruce-test-store.oss-cn-hangzhou.aliyuncs.com/study/img/in运算符查询.jpg" width="450" height="350" data-tool="mdnice编辑器" style="display: block; margin: 0 auto; max-width: 100%;"><br>   可以看到最终的执行结果是“无序”的，竟然没有按照in运算符后面指定顺序的值进行排序。看到这样的结果，不禁想让人问一句： <br><br><img src="https://bruce-test-store.oss-cn-hangzhou.aliyuncs.com/study/img/这是为什么.gif" width="450" height="350" data-tool="mdnice编辑器" style="display: block; margin: 0 auto; max-width: 100%;"><br>   莫慌，下面让我们来仔细分析一下in运算符是如何执行的。<br><br> <img src="https://bruce-test-store.oss-cn-hangzhou.aliyuncs.com/study/img/真相只有一个.jpeg" width="450" height="350" data-tool="mdnice编辑器" style="display: block; margin: 0 auto; max-width: 100%;"><br> <h2 data-tool="mdnice编辑器" style="margin-top: 30px; margin-bottom: 15px; padding: 0px; font-weight: bold; color: black; border-bottom: 2px solid rgb(239, 112, 96); font-size: 1.3em;"><span class="prefix" style="display: none;"></span><span class="content" style="display: inline-block; font-weight: bold; background: rgb(239, 112, 96); color: #ffffff; padding: 3px 10px 1px; border-top-right-radius: 3px; border-top-left-radius: 3px; margin-right: 3px;">二、 “无序”的in运算符查询结果</span><span class="suffix"></span><span style="display: inline-block; vertical-align: bottom; border-bottom: 36px solid #efebe9; border-right: 20px solid transparent;"> </span></h2> <p data-tool="mdnice编辑器" style="font-size: 16px; padding-top: 8px; padding-bottom: 8px; margin: 0; line-height: 26px; color: black;">  在刚刚的查询语句中，<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">id</code>字段是主键字段。仔细观察一下刚刚的查询结果，除了没有按照指定的顺序进行查询结果返回，好像并不能算是无序，反而像是按照<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">id</code>字段的自然顺序进行排列的。为了验证一下猜想，我们来看一下上面SQL的执行计划：<br><br> <img src="https://bruce-test-store.oss-cn-hangzhou.aliyuncs.com/study/img/in运算符执行计划.jpg" style="display: block; margin: 0 auto; max-width: 100%;"><br>   可以看到，这里查询使用了主键，同时type显示的是<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">range</code>，也就是使用索引范围查询，而不是<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">system</code>和<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">const</code>或是<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">eq_ref</code>。这就说明，使用in运算符来进行查询的时候，其效果和<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">></code>、<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);"><</code>、<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">=</code>等比较运算符是一样的（p.s. in运算符也是比较运算符）。也就是说，使用in运算符之后，如果没有使用<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">order by</code>来指定其他的排序顺序，那么最终的结果集将会按照索引的自然顺序进行排序。<br>   那么，in运算符是如何执行的呢？其实很简单，in运算符既然也是比较运算符中一个，那么它的执行过程其实和<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);"><</code>或<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">></code>这种比较运算符是一样的，比如下面的这个SQL语句：</p> <pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px;"><code class="hljs" style="overflow-x: auto; padding: 16px; color: #abb2bf; background: #282c34; display: block; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; border-radius: 0px; font-size: 12px; -webkit-overflow-scrolling: touch;"><span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">select</span> * <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">from</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">user</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">where</span> age > <span class="hljs-number" style="color: #d19a66; line-height: 26px;">0</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">and</span> age < <span class="hljs-number" style="color: #d19a66; line-height: 26px;">25</span>; <span/></code></pre> <p data-tool="mdnice编辑器" style="font-size: 16px; padding-top: 8px; padding-bottom: 8px; margin: 0; line-height: 26px; color: black;">  此时进行<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);"><</code>运算符的执行是在<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">(0, 25)</code>范围内遍历对应的索引进行查询操作，将符合条件的索引全部查出，然后再通过主键索引查询到具体的值。in运算符其实也是类似，唯一不同的是in运算符的查询范围，比如下面这个例子：</p> <pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px;"><code class="hljs" style="overflow-x: auto; padding: 16px; color: #abb2bf; background: #282c34; display: block; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; border-radius: 0px; font-size: 12px; -webkit-overflow-scrolling: touch;"><span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">select</span> * <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">from</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">user</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">where</span> age <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">in</span> (<span class="hljs-string" style="color: #98c379; line-height: 26px;">'25'</span>,<span class="hljs-string" style="color: #98c379; line-height: 26px;">'26'</span>); <span/></code></pre> <p data-tool="mdnice编辑器" style="font-size: 16px; padding-top: 8px; padding-bottom: 8px; margin: 0; line-height: 26px; color: black;">  在这个例子中，in运算符的查询范围是按照<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">[25, 25]</code>和<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">[26, 26]</code>这两个范围进行查询，在这两个范围中遍历索引查询出所有满足条件的值，然后按照索引的自然顺序返回结果集。看到这里大家是不是想到了另一种等价的SQL写法，就是<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">or</code>加上<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">=</code>运算符：</p> <pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px;"><code class="hljs" style="overflow-x: auto; padding: 16px; color: #abb2bf; background: #282c34; display: block; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; border-radius: 0px; font-size: 12px; -webkit-overflow-scrolling: touch;"><span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">select</span> * <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">from</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">user</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">where</span> age = <span class="hljs-string" style="color: #98c379; line-height: 26px;">'25'</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">or</span> age = <span class="hljs-string" style="color: #98c379; line-height: 26px;">'26'</span>; <span/></code></pre> <p data-tool="mdnice编辑器" style="font-size: 16px; padding-top: 8px; padding-bottom: 8px; margin: 0; line-height: 26px; color: black;">  看到这里，大家应该明白了为什么in运算符执行出来的结果集会是”无序”的了，因为其本质就相当于<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">or</code>加上<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">=</code>运算符。所以无论怎么变更in运算符后值的顺序，其查询出来的结果集都是一样的。</p> <h2 data-tool="mdnice编辑器" style="margin-top: 30px; margin-bottom: 15px; padding: 0px; font-weight: bold; color: black; border-bottom: 2px solid rgb(239, 112, 96); font-size: 1.3em;"><span class="prefix" style="display: none;"></span><span class="content" style="display: inline-block; font-weight: bold; background: rgb(239, 112, 96); color: #ffffff; padding: 3px 10px 1px; border-top-right-radius: 3px; border-top-left-radius: 3px; margin-right: 3px;">三、如何使“无序”变得“有序”</span><span class="suffix"></span><span style="display: inline-block; vertical-align: bottom; border-bottom: 36px solid #efebe9; border-right: 20px solid transparent;"> </span></h2> <p data-tool="mdnice编辑器" style="font-size: 16px; padding-top: 8px; padding-bottom: 8px; margin: 0; line-height: 26px; color: black;">  弄明白了in运算符的执行过程，那么难道就真的没有办法按照指定值的顺序进行查询了吗？难道发明MySQL的大佬就这么粗糙的吗？当然不是，我们可以下面的方法进行指定顺序查询：</p> <pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px;"><code class="hljs" style="overflow-x: auto; padding: 16px; color: #abb2bf; background: #282c34; display: block; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; border-radius: 0px; font-size: 12px; -webkit-overflow-scrolling: touch;"><span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">select</span> * <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">from</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">user</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">where</span> age <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">in</span> (<span class="hljs-string" style="color: #98c379; line-height: 26px;">'25'</span>,<span class="hljs-string" style="color: #98c379; line-height: 26px;">'26'</span>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">order</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">by</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">field</span>(<span class="hljs-string" style="color: #98c379; line-height: 26px;">`age`</span>, ’<span class="hljs-number" style="color: #d19a66; line-height: 26px;">25</span>’,’<span class="hljs-number" style="color: #d19a66; line-height: 26px;">26</span>’); <span/></code></pre> <p data-tool="mdnice编辑器" style="font-size: 16px; padding-top: 8px; padding-bottom: 8px; margin: 0; line-height: 26px; color: black;">  上面的方法其实是显式地指明了最终结果集的排序顺序，按照<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">age</code>字段同时按照<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">25</code>、<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">26</code>这样的顺序进行排列。通过这种方式，我们就能获得我们想要的排序顺序了。</p> <h2 data-tool="mdnice编辑器" style="margin-top: 30px; margin-bottom: 15px; padding: 0px; font-weight: bold; color: black; border-bottom: 2px solid rgb(239, 112, 96); font-size: 1.3em;"><span class="prefix" style="display: none;"></span><span class="content" style="display: inline-block; font-weight: bold; background: rgb(239, 112, 96); color: #ffffff; padding: 3px 10px 1px; border-top-right-radius: 3px; border-top-left-radius: 3px; margin-right: 3px;">四、总结</span><span class="suffix"></span><span style="display: inline-block; vertical-align: bottom; border-bottom: 36px solid #efebe9; border-right: 20px solid transparent;"> </span></h2> <p data-tool="mdnice编辑器" style="font-size: 16px; padding-top: 8px; padding-bottom: 8px; margin: 0; line-height: 26px; color: black;">  其实一开始会犯这种常识性错误，主要是因为一般在项目中更多的只会在<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">update</code>操作或者一些不需要指定顺序的查询中使用in运算符，所以对于in运算符的执行原理就会有一些忽视。所以大家还是要好好学习，天天向上，早日变秃变强，成为琦玉老师那样的强者。<br><br> <img src="https://bruce-test-store.oss-cn-hangzhou.aliyuncs.com/study/img/菜的安详.jpeg" width="350" height="350" style="display: block; margin: 0 auto; max-width: 100%;"></p> <p id="nice-suffix-juejin-container" class="nice-suffix-juejin-container" data-tool="mdnice编辑器" style="font-size: 16px; padding-top: 8px; padding-bottom: 8px; margin: 0; line-height: 26px; color: black; margin-top: 20px !important;">本文使用 <a href="https://mdnice.com" style="text-decoration: none; word-wrap: break-word; font-weight: bold; color: rgb(239, 112, 96); border-bottom: 1px solid rgb(239, 112, 96);">mdnice</a> 排版</p></section>