写 SQL 查数据范围查询是躲不开的场景而 between and 大概是范围查询里最容易被低估的一个操作符。很多人觉得它简单无非就是 between 100 and 500 嘛但真到线上日期边界漏数据、字符串边界查错、NULL 记录凭空消失这些坑我几乎每个月都能在开发群里看到有人踩一遍。这篇文章不打算讲高深理论就把它当成一份 between and 的实操笔记来写基础语法、边界逻辑、不同数据类型的表现、常见问题排查以及一些我实际用下来的经验。适合刚入门的开发同学按顺序读也适合查问题的时候当速查表翻。1. between and 的基础语法与执行逻辑1.1 最基础的一条 SQL 长什么样先看语法MySQL 里 between and 的完整格式是expr BETWEEN min_value AND max_value注意这里的AND不是逻辑运算符它只是 between 表达式的一部分。整句话翻译过来就是expr的值在min_value到max_value之间。我习惯把它读成“在某某和某某之间”这样写 SQL 的时候不容易把AND的位置搞错。举个最常见的例子订单表orders里有个金额字段amount想查金额在 100 到 500 之间的订单SELECT * FROM orders WHERE amount BETWEEN 100 AND 500;这条 SQL 等价于SELECT * FROM orders WHERE amount 100 AND amount 500;两者返回的结果集完全相同。注意这里的AND是真正的逻辑运算符语义是“并且”。我见过不少初学者把BETWEEN ... AND ...和 ... AND ...搞混其实它们只是写法不同执行计划基本一样这一点后面会展开说。1.2 闭区间边界到底包不包含边界值这是 between and 最核心、也最容易被忽视的特性——它是闭区间。用生活里的场景类比一下衣柜里有一根挂衣杆杆子两端各有一面墙我们说“衣服在这两面墙之间”其实包含了两面墙本身的位置。between and 就是这个逻辑min_value和max_value这两个边界值都会被包含在结果里。所以amount BETWEEN 100 AND 500查询出来的结果包含amount 100和amount 500的记录。这在很多时候是符合直觉的但也正是因为“包含”才引发了不少边界重复统计的问题这个我放到后面“常见问题”章节细讲。与闭区间对应的反向写法是NOT BETWEEN AND它的语义是开区间外的补集等价于expr min_value OR expr max_value也就是说NOT BETWEEN 100 AND 500选中的是小于 100 或大于 500 的记录恰好不含 100 和 500 本身。为了让你一眼看清两套写法我整理了一张对比表写法等价写法是否包含边界典型结果col BETWEEN 100 AND 500col 100 AND col 500包含 100 和 500100、250、500col NOT BETWEEN 100 AND 500col 100 OR col 500不含 100 和 50099、501col 100 AND col 500左闭右开只含 100不含 500100、250如果对边界条件要求比较苛刻比如分页、分桶统计请优先用最后一种“左闭右开”的写法它能有效避免边界值被重复计算。1.3 between and 与大于等于/小于等于有没有性能差异先说结论在 MySQL 里BETWEEN ... AND ...和 ... AND ...在绝大多数情况下生成执行计划是等价的优化器会把 between and 转换成范围访问条件range来处理所以性能上没有差异。但有一个点要注意如果两个边界值顺序写反了比如写成BETWEEN 500 AND 100MySQL 不会报语法错误但查询结果一定是空集因为没有任何数能同时满足“大于等于 500”且“小于等于 100”。这种错误在参数拼接时会偶尔出现排查起来尤其费劲因为 SQL 能跑结果就是不对。从可读性角度说我更喜欢BETWEEN ... AND ...它比一长串的比较运算符更容易扫一眼看懂。但在需要后续扩展“只含上限不含下限”之类的语义时我会直接改用比较运算符避免临时还要把 between 拆开重写。2. 不同数据类型的范围查询实操2.1 数值类型边界与精度的双重考验数值类型是和 between and 配合最自然的场景。整数、小数、金额都能直接用。比如查单价在 0.01 到 99.99 之间的商品SELECT * FROM products WHERE price BETWEEN 0.01 AND 99.99;这里需要特别提醒一个浮点精度问题。如果字段是FLOAT或DOUBLE做范围查询时容易因为二进制浮点数的精度误差把本该命中的边界值漏掉。举个真实例子0.1在二进制里无法被精确表示存进去之后可能变成0.100000000000000005这时候用BETWEEN 0.10 AND 0.20反而可能查不到刚好等于 0.10 的那条记录。所以我强烈建议涉及金额、数量这类对精度敏感的数据字段一律用DECIMAL而不该用FLOAT。decimal 是精确保存的十进制数边界比较可靠得多。如果你的历史表里已经用了FLOAT在写范围查询前最好先确认一下实际存储值到底长什么样必要时用ROUND()函数做一层预处理但这样做会让索引失效所以最稳妥还是改字段类型。2.2 日期时间类型90% 的人在这里踩坑日期时间范围查询是 between and 使用频率最高的场景也是最容易出问题的场景。核心原因在于日期时间和日期不是一回事。看这条 SQLSELECT * FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31;直觉上你想查的是 1 月一整月的订单但这里的2024-01-31实际上会被 MySQL 隐式转换成2024-01-31 00:00:00。也就是说这条 SQL 只包含 1 月 31 日凌晨 0 点整那一刻的订单整个 1 月 31 日白天和晚上的单子全部被漏掉了。这种 bug 线上特别常见而且非常隐蔽因为结果集看起来是“大概”正确的只有仔细对比总数才会发现少了最后一天的数据。推荐的写法是左闭右开把上界加一天用和组合SELECT * FROM orders WHERE create_time 2024-01-01 AND create_time 2024-02-01;这里 2024-02-01就完整覆盖了 1 月 31 日 23:59:59.999 之前的所有时间点一条都不会漏。MySQL 8.0 里 datetime 可以精确到微秒6 位小数如果上界用 2024-01-31 23:59:59还是会漏掉23:59:59.5这样的记录。左闭右开是目前唯一能做到滴水不漏的写法。我把这条规则当成自己写时间范围查询的铁律也建议你直接抄过去用。2.3 字符串范围查询字符集排序规则是幕后黑手字符串也能用 between and但它的比较规则和数值完全不同。字符串是按照字符集排序规则collation来比较大小的。比如utf8mb4_general_ci排序规则下字母大小写不敏感a和A被视为相同而在utf8mb4_bin排序规则下它们就是完全不同的字符。举个例子查姓氏在 A 到 D 之间的用户SELECT * FROM users WHERE last_name BETWEEN A AND D;如果表的排序规则是utf8mb4_general_ciDoe会被查到但如果排序规则是utf8mb4_binapple小写开头可能不会被包含在A和D之间因为小写a的二进制值和大写A不同。另外要注意D只代表字符D本身字符串Da或David的开头也是D会被纳入范围但Dz大于D就会被排除在外。如果你真想查“以 A 到 D 开头”的字符串BETWEEN A AND D是不够严谨的应该用A到D 无穷大的思路比如BETWEEN A AND D\uffff或者干脆用正则表达式。这类需求我建议别硬刚 between直接用LEFT(col, 1) IN (A,B,C,D)更直白也更容易让后续维护的人看懂。2.4 当遇到 DATETIME 与 TIMESTAMP 混用的情况很多老项目里同一张表有的字段是DATETIME有的是TIMESTAMP它们各自能表示的时间范围不一样。TIMESTAMP范围是 1970 年到 2038 年DATETIME范围是 1000 年到 9999 年。在 between and 查询里这两者谁更好并不绝对但有一点必须牢记不要拿字符串直接跟 TIMESTAMP 字段比较除非你确保字符串格式能被 MySQL 正确识别为合法时间。曾经有朋友在排查接口慢查询时发现某条统计 SQL 把TIMESTAMP字段和2024-01-01 00:00:00这样的字符串比较结果索引完全没走。原因是字段类型和值类型不完全匹配优化器判断需要做隐式转换干脆放弃了索引。解决方案很粗暴先把入参转成标准的时间对象或时间字符串格式保证类型一致再套上 between 条件。这个细节对小数据量无所谓一旦数据量上千万直接影响接口响应时间。3. 常见问题与排查实录3.1 NULL 值永远不在范围里between and 和比较运算符一样对 NULL 值返回的是NULL而WHERE子句只认TRUE所以包含 NULL 的记录永远不会被 between and 选中。这个特性在统计时最容易踩雷。比如你想统计“没有设置积分余额的用户”直觉上可能会写SELECT * FROM users WHERE points BETWEEN 0 AND 100;结果发现一批points IS NULL的用户没有出现在结果里。他们不是 0 积分而是“从未被设置过积分”这在业务上完全是另一码事。如果你需要把 NULL 也纳入统计范围必须显式补充条件SELECT * FROM users WHERE points BETWEEN 0 AND 100 OR points IS NULL;注意这里的OR两侧要加括号否则会跟其他条件产生优先级冲突导致结果集失控。NULL 处理这件事在写范围查询前就要想清楚别把“缺失”和“零值”混为一谈。3.2 边界重复统计切分区间时最烦人的问题用 between and 做分桶统计比如按金额区间统计订单数SELECT CASE WHEN amount BETWEEN 0 AND 100 THEN 0-100 WHEN amount BETWEEN 100 AND 200 THEN 100-200 WHEN amount BETWEEN 200 AND 300 THEN 200-300 END AS bucket, COUNT(*) FROM orders GROUP BY bucket;看这条 SQL金额正好等于 100 的订单会同时被0-100和100-200两个桶统计等于重复计了一次。这就是闭区间在切分场景下的天然缺陷。如果你翻看过一些 BI 报表的底层 SQL大概率见过这种数据漂移。解决办法有两个一是把所有区间改成左闭右开比如amount 0 AND amount 100、amount 100 AND amount 200二是用FLOOR(amount / 100)这种方式做整数桶计算但这只适合等宽分桶不规则区间还得靠第一种方案。3.3 隐式类型转换范围查询的隐形杀手MySQL 在比较不同数据类型时会发生隐式类型转换。举例来说某个字段code是VARCHAR类型里面存的是纯数字字符串你写SELECT * FROM products WHERE code BETWEEN 100 AND 999;MySQL 会把code字段从字符串转成数字再比较。一旦对字段做转换索引就基本失效了全表扫描随之而来。等你发现线上这个查询越来越慢explain 一看 type 是ALL就明白怎么回事了。反过来也一样字段是整数你非传100这样的字符串给它MySQL 会把常量转成数字这种一般还好不至于伤索引。最稳妥的做法是保持字段类型和值的类型严格一致。代码里写参数时数字就是数字字符串就是字符串不要图省事直接拼一个中间加引号的值进去。3.4 动态范围条件的参数拼接在报表系统、后台管理系统里范围查询经常是用户在前端输入起止值后端拼 SQL。这里最要命的不是 between 本身而是拼接出畸形 SQL。比如用户只填了开始时间你没做空值判断就拼出WHERE create_time BETWEEN 2024-01-01 AND NULL这条 SQL 在 MySQL 里会返回空集因为NULL作为上界没有意义。所以动态拼接时一定要先校验参数的完整性要么两个边界都传要么用独立的和条件分别拼接互不干扰。我更推荐后者因为它天然支持“只查起始时间”和“只查结束时间”这种常见需求且语义清晰。另外如果用的是 JDBC 的预处理语句或者 MyBatis 的参数占位符只要规范参数化就不会有 SQL 注入风险。真正危险的是用字符串把用户输入硬拼进 SQL那不只是 between 的问题整条语句都暴露在外面了。3.5 大数据量下 between and 的性能表现BETWEEN ... AND ...在索引上的表现比较稳定它会被优化器转换成范围扫描走索引时 type 通常是range效率远高于全表扫描。但要注意一点如果范围过大比如查近十年的订单优化器算一下发现可能需要扫描表中超过百分之二三十的数据它可能认为走索引反而更慢直接改走全表扫描。这个行为不只在 between 上所有范围查询都有。想验证到底走没走索引最简单的方式是执行EXPLAIN SELECT * FROM orders WHERE create_time BETWEEN 2020-01-01 AND 2020-12-31;看type列是不是rangekey列有没有显示索引名。如果key是 NULL说明当前没有可用的索引那就得考虑加一个复合索引。比如你的查询条件是status create_time amount那就按同样的字段顺序建一个复合索引让最常用的范围字段能参与索引扫描。4. 进阶用法与替代写法4.1 NOT BETWEEN AND 的准确语义与 NULL 影响NOT BETWEEN ... AND ...写起来顺手但容易误判。它等价于col min_value OR col max_value注意这里用的是OR而不是AND。所以当你看到一条 SQL 写成SELECT * FROM users WHERE age NOT BETWEEN 18 AND 60;它选中的是年龄小于 18 或大于 60 的用户恰好把 18 和 60 本身排除掉了。如果你的业务想“包含 18 到 60 但排除中间”那这个写法就是对的如果想“包含边界”但排除中间那得改成age 18 OR age 60并且在条件里显式处理 NULL。这里再次提醒有 NULL 值时NOT BETWEEN同样不会选中那条记录。因为 NULL 比较的结果还是 NULLNOT NULL依然是 NULL。所以任何范围条件之前都要想清楚 NULL 值在业务里代表什么。4.2 连续区间用 between离散集合用 in有时候我们会搞混IN和BETWEEN。BETWEEN处理的是连续区间IN处理的是离散集合。比如-- 连续区间单价 100 到 500 WHERE price BETWEEN 100 AND 500; -- 离散集合单价恰好是 100、200、500 WHERE price IN (100, 200, 500);这两种语义完全不同。BETWEEN 100 AND 500会包含 100.01、299.99 之类的任意中间值IN (100, 200, 500)只会精确匹配三个值。所以看到某条 SQL 用IN接了一段连续的范围参数比如IN (100, 101, 102, 103, ...)那大概率是有人误用了应该改成 between。性能上也有差异。如果IN列表很小用索引效率很好列表大到几百上千项执行计划会变得很复杂有时候甚至会拆分成多个等值条件这种场景反而不如一条 between 干净利落。反过来如果区间范围内大部分数据都不想要只用几个精确值那就别用之间。4.3 分页场景下的范围查询优化列表页按时间倒序分页是最常见的业务需求。很多同学会这样写SELECT * FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31 ORDER BY create_time DESC LIMIT 20 OFFSET 40;这条 SQL 能用但OFFSET越大越慢。因为数据库要先把前面的满足条件的行全部读出来丢掉再取你需要的 20 行。数据量一上来翻到第十页就已经有明显延迟。常见的优化思路是“键集分页”keyset pagination也就是带上次查询最后一条记录的游标SELECT * FROM orders WHERE create_time 2024-01-01 AND create_time 2024-02-01 AND (create_time, id) (2024-01-15 10:30:00, 200123) ORDER BY create_time DESC, id DESC LIMIT 20;这种写法能稳定利用(create_time, id)复合索引让每一页的查询成本都差不多而不是越翻越慢。配合左闭右开的时间边界还能避免因边界值导致的页间重复或遗漏。4.4 在存储过程与 ORM 动态 SQL 中安全使用最后说一下工程中的写法。在 MySQL 存储过程中between 可以直接写在动态 SQL 里但要注意把边界值作为变量传入时同样要保证类型和长度一致SET start_date 2024-01-01; SET end_date 2024-02-01; SELECT * FROM orders WHERE create_time start_date AND create_time end_date;在 MyBatis 这类 ORM 框架里我更推荐用if标签分别拼起始和结束条件而不是整体拼一个 betweenwhere if teststartDate ! null AND create_time gt; #{startDate} /if if testendDate ! null AND create_time lt; #{endDate} /if /where这样既能复用查询片段又能天然支持只查单边范围的场景。有人问我为什么不用![CDATA[ BETWEEN ... ]]整体拼我的回答是整体拼需要在业务层额外判断两个参数同时非空否则容易出现“只传了开始时间却带上 BETWEEN 条件导致结果为空”的尴尬。拆成两个独立条件反而边界情况最少。我个人在实际开发里还有个习惯不管字段本身是DATE还是DATETIME只要语义上是在“查某一天”或“查某一月”一律把时间范围写成一个左闭右开的区间上界永远是“下一天的零点”或者“下一月的第一天”。这个习惯帮我避免过太多次因为时间边界导致的线上数据对不上问题。你可以在自己的项目里试试尤其是接手那些动不动就漏一天数据的报表任务时把BETWEEN 2024-01-01 AND 2024-01-31改成 2024-01-01 AND 2024-02-01逻辑瞬间就踏实了。