sql怎样用BETWEEN…AND选取指定范围的数据 sql范围查询的基础语句用法

<p>between…and是sql中用于查询指定范围（包含边界）数据的语法糖，适用于数字、日期和字符串类型，基本语法为select column from table where column between value1 and value2；处理日期时需注意时间精度问题，推荐使用where date_column >= ‘start_date’ and date_column =, </p> <p><img src=”https://img.php.cn/upload/article/001/503/042/175522890715806.jpeg” alt=”sql怎样用BETWEEN…AND选取指定范围的数据 sql范围查询的基础语句用法”></p> <p>在SQL里，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN…AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 就是一个特别直接、也很好用的语法糖，它能帮你轻松选取某个字段在指定范围之内的数据，而且这个范围是包含起始值和结束值的。简单来说，如果你想找某个数字、日期或者字符串在一个连续区间内的数据，用它就对了。</p> <h3>解决方案</h3> <p>要使用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN…AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>进行范围查询，基本语法结构是这样的：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT column1, column2, … FROM your_table WHERE your_column BETWEEN value1 AND value2;</pre><div class=”contentsignin”></div></div><p>这里，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>your_table</pre><div class=”contentsignin”></div></div> 是你要查询的表名，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>your_column</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 是你希望进行范围筛选的列，而 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>value1</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 和 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>value2</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 分别代表范围的起始值和结束值。需要注意的是，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>value1</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 通常应该小于或等于 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>value2</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，否则查询结果可能为空或者不符合预期。</p> <p>举个例子，假设我们有一个 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>products</pre><div class=”contentsignin”></div></div> 表，里面有 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>price</pre><div class=”contentsignin”></div></div> 字段，我们想找出价格在100到500之间的所有产品：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT product_name, price FROM products WHERE price BETWEEN 100 AND 500;</pre><div class=”contentsignin”></div></div><p>这会返回所有价格等于100、大于100但小于500、以及等于500的产品。它处理数字类型数据时，直观且高效。</p> <h3>日期和时间数据类型在使用 BETWEEN 时需要注意什么？</h3> <p>处理日期和时间数据时，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN…AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 确实有一些细节需要特别留心，不然很容易出现“差一天”或者“少几秒”的查询结果。核心问题在于，日期字段往往还包含了时间信息，哪怕你只存了 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>YYYY-MM-DD</pre><div class=”contentsignin”></div></div> 这种格式，数据库内部也可能默认补上了 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>00:00:00</pre><div class=”contentsignin”></div></div>。</p> <p>比如，你想查询2023年1月1日到2023年1月31日之间的订单：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>– 看起来没问题，但可能不包含1月31日当天所有时间的数据 SELECT * FROM orders WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-01-31’;</pre><div class=”contentsignin”></div></div><p>这里的问题是，如果 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>order_date</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 字段的实际值是 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>2023-01-31 10:30:00</pre><div class=”contentsignin”></div></div>，那么这个查询就可能不会包含它。因为 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN ‘2023-01-01′</pre><div class=”contentsignin”></div></div> 实际是 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>2023-01-01 00:00:00</pre><div class=”contentsignin”></div></div>，而 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>AND ‘2023-01-31′</pre><div class=”contentsignin”></div></div> 实际是 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>2023-01-31 00:00:00</pre><div class=”contentsignin”></div></div>。所以，任何在1月31日零点之后的订单都会被漏掉。</p> <p>正确的做法通常有两种：</p> <ol> <li> <p><strong>扩展结束日期到下一天的开始：</strong></p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT * FROM orders WHERE order_date BETWEEN ‘2023-01-01 00:00:00’ AND ‘2023-02-01 00:00:00′; — 注意是2月1日0点</pre><div class=”contentsignin”></div></div><p>或者更简洁地，对于只包含日期的字段，可以将结束日期加一天：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT * FROM orders WHERE order_date >= ‘2023-01-01’ AND order_date < ‘2023-02-01′;</pre><div class=”contentsignin”></div></div><p>这种 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>A >= X AND A < Y</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 的写法在处理日期范围时非常稳健，它明确地包含了起始日期当天所有时间点，并排除了结束日期当天所有时间点，从而实现了“到某天结束”的效果。</p> </li> <li> <p><strong>使用日期函数处理：</strong> 在某些数据库中，你可能需要使用特定的日期函数来提取日期部分进行比较，或者构造时间范围。例如，在MySQL中：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT * FROM orders WHERE DATE(order_date) BETWEEN ‘2023-01-01’ AND ‘2023-01-31′;</pre><div class=”contentsignin”></div></div><p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>DATE()</pre><div class=”contentsignin”></div></div> 函数会提取日期部分，忽略时间，这样比较就只基于日期了。但这种做法可能会导致无法利用 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>order_date</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 上的索引，影响查询性能。所以，第一种方法通常更推荐，因为它能更好地利用索引。</p> </li> </ol> <h3>BETWEEN 和 AND 以外的范围查询方式有哪些，各有什么优缺点？</h3> <p>除了 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN…AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，我们当然还有其他方式进行范围查询，最常见也最基础的就是使用比较运算符 (<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>>=</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>, <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”><=</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>, <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>></pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>, <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”><</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>) 结合 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>。</p> <ol> <li> <p><strong>使用比较运算符 (<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>>=</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>, <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”><=</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>, <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>></pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>, <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”><</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>) 和 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>：</strong> 这是最灵活、最底层的范围查询方式。</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT product_name, price FROM products WHERE price >= 100 AND price <= 500;</pre><div class=”contentsignin”></div></div><p><strong>优点：</strong></p> <ul> <li> <strong>灵活性极高：</strong> 你可以精确控制范围的开闭，比如 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>price > 100 AND price < 500</pre><div class=”contentsignin”></div></div> （不包含100和500），这是 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 做不到的。</li> <li> <strong>语义明确：</strong> 这种写法直接表达了“大于等于A并且小于等于B”的逻辑，对于初学者来说可能更容易理解其包含关系。</li> <li> <strong>性能等同：</strong> 大多数现代数据库的查询优化器都会将 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN…AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 内部转换为这种形式，所以性能上通常没有差异。</li> </ul> <p><strong>缺点：</strong></p> <ul><li> <strong>稍显冗长：</strong> 相比 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，需要写两次列名和比较运算符，代码量略多。</li></ul> </li> <li> <p><strong>使用 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>NOT BETWEEN…AND</pre><div class=”contentsignin”></div></div>：</strong> 如果你想查询某个范围之外的数据，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>NOT BETWEEN</pre><div class=”contentsignin”></div></div> 是 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 的反义词。</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SELECT product_name, price FROM products WHERE price NOT BETWEEN 100 AND 500;</pre><div class=”contentsignin”></div></div><p>这会返回所有价格小于100或大于500的产品。</p> <p><strong>优点：</strong></p> <ul><li> <strong>简洁明了：</strong> 直接表达了“不在某个范围之内”的意图。</li></ul> <p><strong>缺点：</strong></p> <ul><li> <strong>不如 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 组合灵活：</strong> 如果你想要更复杂的排除逻辑，比如不在某个范围 <em>和</em> 另一个范围，可能需要结合 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 和 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>NOT</pre><div class=”contentsignin”></div></div> 运算符。</li></ul> </li> </ol> <p>总的来说，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN…AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 更像是一个语法糖，它让代码更简洁易读，特别是在你需要包含边界值时。而使用 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 结合比较运算符则提供了更细粒度的控制，尤其是在处理开区间或半开半闭区间时，它才是唯一选择。在实际工作中，我会根据具体需求和个人偏好灵活选择，但对于日期查询，我个人更倾向于 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>A >= X AND A < Y</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 的写法，它能避免很多时间精度上的坑。</p> <h3>在实际应用中，BETWEEN 查询的性能表现如何？</h3> <p>关于 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 查询的性能，这其实是一个老生常谈的话题了，但它确实很重要。我的看法是，在绝大多数情况下，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN…AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 的性能表现是相当不错的，而且通常和使用 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>column >= value1 AND column <= value2</pre><div class=”contentsignin”></div></div> 的写法性能持平。这主要是因为现代数据库的查询优化器非常智能。</p> <p>当你写下 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE your_column BETWEEN value1 AND value2</pre><div class=”contentsignin”></div></div> 时，数据库的优化器在内部处理时，很可能会将其重写为 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE your_column >= value1 AND your_column <= value2</pre><div class=”contentsignin”></div></div>。所以，从执行计划的角度来看，两者往往是等效的。</p> <p>性能的关键点在于：</p> <ol> <li><p><strong>是否使用了索引：</strong> 这是影响范围查询性能的决定性因素。如果 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>your_column</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 上有合适的索引（B-tree索引最适合范围查询），那么无论是 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 还是 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>AND</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 组合，数据库都可以利用索引快速定位到数据范围的起点和终点，然后高效地扫描这个范围内的索引叶子节点，大大减少需要读取的数据量。没有索引的话，数据库就不得不进行全表扫描，这在数据量大时会非常慢。</p></li> <li><p><strong>数据选择性：</strong> 如果你查询的范围非常大，几乎包含了表中的大部分数据，那么即使有索引，数据库也可能选择进行全表扫描，因为扫描索引再回表取数据可能比直接全表扫描更慢。但这种情况比较少见，通常我们进行范围查询是为了缩小结果集。</p></li> <li><p><strong>数据类型：</strong> <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 对于数字和日期类型的数据处理非常高效。对于字符串，它也有效，但字符串的“范围”概念可能不如数字和日期那么直观（例如，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>’A'</pre><div class=”contentsignin”></div></div> 到 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>’C'</pre><div class=”contentsignin”></div></div> 会包含 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>’B'</pre><div class=”contentsignin”></div></div> 和 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>’BETA'</pre><div class=”contentsignin”></div></div>，但不会包含 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>’CA'</pre><div class=”contentsignin”></div></div>）。</p></li> <li><p><strong>复合索引：</strong> 如果你的查询条件涉及多个列的范围，例如 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE col1 BETWEEN val1 AND val2 AND col2 BETWEEN val3 AND val4</pre><div class=”contentsignin”></div></div>，那么一个覆盖了这些列的复合索引（如 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>(col1, col2)</pre><div class=”contentsignin”></div></div>）会非常有帮助。数据库可以利用这个复合索引来过滤数据，进一步提升性能。</p></li> </ol> <p>所以，如果你在实际项目中发现 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 查询很慢，通常问题不在于 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 本身，而在于：</p> <ul> <li>你查询的列没有合适的索引。</li> <li>查询的范围过大，导致索引失效或优化器选择全表扫描。</li> <li>查询条件过于复杂，导致优化器难以有效利用索引。</li> </ul> <p>最佳实践是，始终确保你的范围查询列上有索引，并定期检查查询的执行计划，看看数据库是如何处理你的 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 语句的。这样才能真正做到心中有数，优化有据。</p>