1. BETWEEN 基础语法与边界语义先把它看透再动手1.1 一行 SQL 背后的闭区间逻辑很多没踩过坑的人会下意识觉得BETWEEN就是范围过滤这么理解没错但它本质是一个闭区间包含的比较运算。所谓闭区间就是下界和上界本身也会被查出来。看一个最典型的例子以 Oracle 自带的emp表为例SELECT empno, ename, sal FROM emp WHERE sal BETWEEN 3000 AND 5000;这条语句等价于下面这段翻译版本SELECT empno, ename, sal FROM emp WHERE sal 3000 AND sal 5000;我刚开始写 SQL 的时候一直有个错觉以为BETWEEN是 大于等于下界小于上界也就是左闭右开。后来在生产环境排查一条统计报表数据对不上的问题才发现是这里理解错了。BETWEEN 3000 AND 5000会把sal 5000的员工也包含进去而如果用左闭右开逻辑写恰好漏掉了边界上那条记录。你说它是不是出错倒也不算它就是设计成闭区间只是用错了场景会悄悄少算或多数一条数据。再说NOT BETWEEN很多人认为它就是BETWEEN的取反但精确地说它的逻辑等价是WHERE sal 3000 OR sal 5000注意这里不能用AND因为sal不可能同时小于 3000 又大于 5000。这个细节看似废话但在拼接动态 SQL、写 ORM 条件的时候一旦搞错连接符结果会离谱到让人怀疑人生。还有一个隐藏很深的点BETWEEN的三个操作数字段、下界、上界里任何一个如果包含NULL整个条件的结果就不是TRUE也不是FALSE而是UNKNOWN。在WHERE子句里UNKNOWN的行会被直接过滤掉。举个例子SELECT * FROM emp WHERE sal BETWEEN NULL AND 5000;这条语句永远什么都不返回因为sal NULL的结果是UNKNOWN加上了AND之后整体还是UNKNOWN。这不是 Oracle 的 bug而是 SQL 三值逻辑的固有特性。所以如果下界和上界是从外部传入的参数一定要在进入 SQL 之前把空值处理掉。1.2 BETWEEN 与“不等于”排除别把边界写重了或漏了有时候业务要排除一个区间但边界不能动比如排除金额在 100 到 200 之间的记录。很多新手会写成WHERE sal NOT BETWEEN 100 AND 200这条语句会把sal 100和sal 200也排除掉如果你恰恰需要保留边界上的数据那就出事了。正确的写法应该拆成两个明确的不等式WHERE sal 100 OR sal 200反过来如果你需要只排除中间保留边界就这么写。这个业务含义的细微差别在金融、订单、绩效计算场景里很常见边界差一毛钱对不上账的时候回头查一下是不是这里写反了多半能救命。另外有个冷门但特别好用的玩法BETWEEN的下界可以大于上界吗语法上 Oracle 不会报错但它不会帮你反转区间实际执行的结果是空集。比如SELECT * FROM emp WHERE sal BETWEEN 5000 AND 3000;这条 SQL 能正常执行不报错但返回 0 行。因为 Oracle 拿到这个条件后还是按sal 5000 AND sal 3000去执行的一个数不可能同时满足这两个条件。这个特性能治一种毛病程序里拼接区间条件时如果外面已经判断过下界小于上界那里面就不需要再做一次防御。但如果没判断过你就要小心这种静默空结果的问题因为它不会报错业务方只会看到数据变少了不会看到 SQL 报错。2. 日期时间字段里的 BETWEENOracle 最容易踩坑的地方2.1 “查当天数据”为什么经常一条都查不出来我在线上帮人排查过很多次开发说我查这张表今天的记录明明有数据为什么 SQL 查出来是空的打开 SQL 一看多半长这样SELECT * FROM orders WHERE create_date BETWEEN 2024-01-15 AND 2024-01-15;问题出在create_date是DATE类型它存储的不仅仅是2024-01-15这个日期还包含时:分:秒。Oracle 在处理2024-01-15这个字符串时会隐式转换成2024-01-15 00:00:00。于是上面这条 SQL 真正的执行逻辑是WHERE create_date TO_DATE(2024-01-15 00:00:00, YYYY-MM-DD HH24:MI:SS) AND create_date TO_DATE(2024-01-15 00:00:00, YYYY-MM-DD HH24:MI:SS)所以它只能查到create_date精确等于2024-01-15 00:00:00这个瞬间的数据哪怕当天 23:59:59 的记录也会被过滤掉。我曾经在一个订单报表项目里吃过这个亏。当时业务方说今天订单怎么显示为空我看了一眼数据其实订单都在只是创建时间大多集中在09:30到18:45之间没有任何一条正好卡在零点整。后来我把条件改成了WHERE create_date TO_DATE(2024-01-15 00:00:00, YYYY-MM-DD HH24:MI:SS) AND create_date TO_DATE(2024-01-16 00:00:00, YYYY-MM-DD HH24:MI:SS)注意我特意用了而不是卡第二天的零点这样做的目的是避免把2024-01-16 00:00:00这一刻的数据也算进来。严格从数学意义上说当天的时间范围应该是[当天 00:00:00, 次日 00:00:00)也就是左闭右开。而BETWEEN偏偏是闭区间你用BETWEEN AND去包一天的 24 小时就必须精确写对秒的部分或者干脆拆成两个和我后面实战中几乎都用拆分写法就是不想去记那个边界秒数。2.2 用 TRUNC 和 SYSDATE 组合写出稳定的时间区间查询如果要查今天的数据最省心的写法不是手写TO_DATE而是直接用TRUNC(SYSDATE)SELECT * FROM orders WHERE create_date TRUNC(SYSDATE) AND create_date TRUNC(SYSDATE) 1;TRUNC(SYSDATE)会返回当天的零点整比如2024-01-15 00:00:00再加 1 就是次日零点。这个写法每天跑都不会出错也不用关心今天是几号。同理查近 7 天可以这样WHERE create_date TRUNC(SYSDATE) - 6 AND create_date TRUNC(SYSDATE) 1;有人可能会问为什么不用BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) 1我刚才说了如果严格用闭区间那就得在TRUNC(SYSDATE) 1上再减一个很小的数比如TRUNC(SYSDATE) 1 - 1/86400这样会把23:59:59包含进来但万一某些记录的时间戳是23:59:59.5呢DATE 类型精确到秒倒是没事可如果你用的字段是TIMESTAMP类型它支持小数秒减掉1/86400仍然会漏掉23:59:59.123这种记录。所以遇到时间段查询我的习惯是查某天/某范围统一用 [起点] AND [终点]只有明确查闭区间比如金额 100 到 200含 200才用BETWEEN避免在条件里写BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) 1这种看似省事实际边界模糊的写法从索引性能角度看拆成和两个条件也更友好。Oracle 的 CBO基于代价的优化器在处理范围条件时能更好地判断是否走BETWEEN对应的索引范围扫描。你写和和写BETWEEN执行计划往往是一样的但前者不会让人产生我是不是漏了边界的心理负担。还有个小技巧如果业务上经常按自然月查数据比如查 2024 年 1 月可以这样写WHERE create_date TO_DATE(2024-01-01, YYYY-MM-DD) AND create_date TO_DATE(2024-02-01, YYYY-MM-DD)如果表里数据量大而且月份查询很频繁建议在create_date上建一个普通的 B-Tree 索引这个范围条件就能走索引快速拿到数据。2.3 两个字段之间做范围判断BETWEEN 的“反向”妙用上面讲的是一个字段落在两个常量之间但实际业务里还有一种常见场景判断一个给定的时间点是否落在某条记录的起止时间段内。比如查当前有哪些活动正在进行表结构可能是start_time活动开始时间end_time活动结束时间要查询2024-01-15 10:00:00 这一刻有哪些活动在有效期内SQL 可以写成SELECT * FROM campaign WHERE TO_DATE(2024-01-15 10:00:00, YYYY-MM-DD HH24:MI:SS) BETWEEN start_time AND end_time;有没有发现BETWEEN前面的字段也可以是常量后面两个操作数是字段。这种写法非常直观翻译过来就是给定时间点是否在开始时间到结束时间之间。如果业务上要查当前时间点直接WHERE SYSDATE BETWEEN start_time AND end_time唯一的坑还是边界BETWEEN是闭区间会把start_time恰好等于当前时间、或者end_time恰好等于当前时间的活动算进来。如果业务要求正在进行的活动必须严格排除刚结束的那一刻就要改用WHERE SYSDATE start_time AND SYSDATE end_time这类需求在优惠券、限时活动、排班表里特别多。我见过一个优惠券系统结算时判断用户领的券是否过期用的就是SYSDATE BETWEEN start_time AND end_time后来运营反馈为什么券刚到过期时间还能用就是闭区间把到期那秒也算进去了。后来改成左闭右开就正常了。3. 字符串、分页与序列生成BETWEEN 的进阶玩法3.1 字符串比较BETWEEN 不是你想的“字典序”那么简单BETWEEN用在字符串上很多人会觉得不就是字母顺序吗比字母顺序更坑的是数字字符串。比如有一张用户表手机号或编号是 VARCHAR2 类型你要查编号在 100 到 200 之间的记录SELECT * FROM users WHERE user_no BETWEEN 100 AND 200;如果user_no是字符串类型Oracle 比较时是按字符的二进制值或语言排序规则逐个字符比较不是按数值大小比较。100和200都是三位数你可能碰巧能查到150但查不到99因为它小于100也查不到1000因为字符比较时1000的前三位是100按位比较它排在100后面但又小于200所以会出现在结果里但如果按数值比较1000 显然不应该在 100 到 200 之间。更极端的例子是SELECT 100 FROM dual WHERE 100 BETWEEN 10 AND 99;这条 SQL 返回一行。为什么因为字符串比较是逐位比对的100和99比较第一位1和91小于9所以100 99成立。你以为 100 不在 10 到 99 之间但 Oracle 认为它在。这种灵异现场十有八九是类型设计的问题——可参与范围比较的编号应该用 NUMBER 类型而不是 VARCHAR2。如果必须用字符串存数字又想按数值过滤有两条路在 SQL 里把字符串字段转成 NUMBER 再比较但这样会让字段上的索引失效除非建函数索引数据量大时会很痛苦。建一个TO_NUMBER(user_no)的函数索引或者干脆在应用层做转换。另外中文的BETWEEN排序受NLS_SORT参数影响。默认情况下如果数据库字符集是ZHS16GBKBETWEEN 张 AND 赵这类中文按拼音排序还是按二进制编码排序在不同字符集下结果会不一样。所以在做中文区间查询前先搞清楚你的排序规则别用一次换一个结果。3.2 连续序列生成用 BETWEEN 配合 CONNECT BY 做日期/序号列表BETWEEN除了用在WHERE里还能配合CONNECT BY生成连续序列。如果你想在 Oracle 里生成从 1 到 10 的连续数字不用 PL/SQL 循环SELECT LEVEL FROM dual CONNECT BY LEVEL BETWEEN 1 AND 10;LEVEL是CONNECT BY的伪列从 1 开始递增。这样就能得到 1 到 10 的列表。更常用的场景是生成一段连续日期比如生成 2024 年 1 月的每一天SELECT DATE 2024-01-01 LEVEL - 1 AS day FROM dual CONNECT BY LEVEL 31;或者写成SELECT DATE 2024-01-01 LEVEL - 1 AS day FROM dual CONNECT BY DATE 2024-01-01 LEVEL - 1 DATE 2024-01-31;这种写法在报表里特别有用。比如你要按天统计订单量但有些天没有订单直接用GROUP BY TO_CHAR(create_date, YYYY-MM-DD)会缺行。正确的姿势是先用CONNECT BY生成一个完整的日期序列再左连接订单表缺数据的天补 0。这个需求几乎每个月都会遇到一次可以收藏。如果你生成的不是天数而是小时序列比如统计一天 24 小时每个小时的订单分布SELECT TRUNC(SYSDATE) (LEVEL - 1) / 24 AS hour_point FROM dual CONNECT BY LEVEL 24;这里用LEVEL - 1除以 24得到一天的 0 点到 23 点整点TRUNC(SYSDATE)保证从当天零点开始。3.3 分页场景为什么 ROWNUM 不能配 BETWEENOracle 的分页很多人第一反应是ROWNUM。但你要小心ROWNUM是在结果集生成过程中分配的伪列不是表里的真实列所以你不能写SELECT * FROM emp WHERE ROWNUM BETWEEN 1 AND 10;这条 SQL 在 Oracle 里是能执行出结果的但它的逻辑会让人困惑ROWNUM从 1 开始分配第一行如果被过滤掉第二行还是 1永远无法推进到 2。所以ROWNUM BETWEEN 1 AND 10勉强能返回前 10 行但ROWNUM BETWEEN 2 AND 10一定会返回 0 行因为 ROWNUM1 的那行不满足条件下一行又变成 1陷入死循环式的过滤。分页的正解一般是两层嵌套 ROWNUMSELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT empno, ename, sal FROM emp ORDER BY sal DESC ) t WHERE ROWNUM 20 ) WHERE rn 10;Oracle 12c 及以上版本就简单多了SELECT empno, ename, sal FROM emp ORDER BY sal DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;这里我要单独强调BETWEEN不适合直接用在ROWNUM上做分页。它不是不能用于分页的绝对结论而是因为ROWNUM的分配机制导致BETWEEN在这种场景下会产生反直觉的结果。理解了这一点你就明白为什么老 Oracle DBA 看到WHERE ROWNUM BETWEEN 2 AND 10会眉头一皱。4. 性能考量为什么你的 BETWEEN 查询越来越慢4.1 索引生效条件BETWEEN 本身是友好还是“埋雷”BETWEEN在 B-Tree 索引上的表现其实相当不错。它本质上是两个等值条件的范围组合Oracle 会将其转换为索引范围扫描INDEX RANGE SCAN。比如SELECT * FROM orders WHERE order_date BETWEEN TO_DATE(2024-01-01, YYYY-MM-DD) AND TO_DATE(2024-01-31, YYYY-MM-DD);如果order_date上有索引这个查询可以高效地扫描索引从 1 月 1 日到 1 月 31 日这一段然后回表取数据。但有一个经典陷阱对字段做了函数处理后BETWEEN 的索引就失效了。比如SELECT * FROM orders WHERE TRUNC(order_date) BETWEEN TO_DATE(2024-01-01, YYYY-MM-DD) AND TO_DATE(2024-01-31, YYYY-MM-DD);TRUNC(order_date)把每一个order_date都先做了一次函数计算Oracle 没法直接用order_date上的索引会走全表扫描。数据量小的时候没什么感觉数据量上了千万级一次报表查询能把你数据库 CPU 打满。解决方式有两种改成范围条件让字段本身和常量比WHERE order_date TO_DATE(2024-01-01, YYYY-MM-DD) AND order_date TO_DATE(2024-02-01, YYYY-MM-DD)或者建一个函数索引CREATE INDEX idx_trunc_order_date ON orders(TRUNC(order_date));我个人的建议是优先改写法因为函数索引会增加 DML 开销而且对开发人员来说不够直观很多人压根不知道有这个索引存在。4.2 隐式类型转换与统计信息BETWEEN 慢的“隐形元凶”BETWEEN慢还有一种情况字段类型和比较值类型不一致导致 Oracle 做隐式类型转换。最常见的场景是字段是 VARCHAR2但传入的是数字SELECT * FROM orders WHERE order_no BETWEEN 100000 AND 200000;如果order_no是 VARCHAR2 类型Oracle 会把字段隐式转换成 NUMBER 再比较。这种转换会直接让order_no上的索引失效因为索引里存的是原始字符串不是转换后的数字。你可以在执行计划里看到to_number(ORDER_NO)这样的操作那基本就是隐式转换实锤了。判断 SQL 慢在哪我的习惯是拿到执行计划看有没有SYS_OP_C2C或TO_NUMBER/TO_DATE出现在谓词信息里。SQL*Plus里可以用EXPLAIN PLAN FOR SELECT * FROM orders WHERE order_no BETWEEN 100000 AND 200000; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);如果看到access(ORDER_NO 100000 AND ORDER_NO 200000)而没有用到INDEX RANGE SCAN基本就是从类型不匹配开始的。另一个容易被忽略的因素是统计信息过期。CBO 会根据表的统计信息估算BETWEEN范围扫描要返回多少行如果统计信息是几个月前收集的而表数据量翻了好几倍CBO 可能低估或高估选择率导致选错执行计划比如走全表扫描。特别是当范围条件的选择率低于 5% 时CBO 才会倾向于用索引这个比例是经验值不是绝对标准。所以定期收集统计信息EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ORDERS);这条命令在数据变化大的表上尤其值得定期执行。5. 常见问题与排查技巧实录5.1 问题速查表一张表解决 80% 的 BETWEEN 困惑我把这些年遇到的BETWEEN典型问题做了一个汇总方便你直接照方抓药症状可能原因排查方向日期查不到“当天”数据DATE 字段带时分秒字符串隐式转成了当天零点改用当天零点AND 次日零点区间边界数据多算/少算对闭区间理解偏差确认业务期望的是开区间还是闭区间字符串编号查询结果“乱”VARCHAR2 按字符逐位比较不是数值比较改用 NUMBER 类型或建函数索引查询很慢执行计划显示全表扫描对字段用了函数或隐式类型转换改写为字段裸比较或建函数索引ROWNUM 分页结果为空/错乱ROWNUM 的伪列分配机制不用 BETWEEN 包 ROWNUM改用嵌套查询下界或上界为 NULL 时结果为空SQL 三值逻辑UNKNOWN 被过滤进入 SQL 前处理空值或加 NVL 兜底把 BETWEEN 写在 JOIN 条件里导致结果膨胀ON 条件里同时存在等值和范围条件产生多对多匹配先在子查询里过滤再关联5.2 排查思路从“结果不对”到“定位 SQL 问题”的四步法如果你遇到BETWEEN相关的数据异常我建议按这四步走能省掉大量瞎试的时间第一步看数据。不要急着改 SQL先确认表中真实的边界值长什么样。用SELECT MIN(x), MAX(x)看一下目标字段的极值再抽样看一下边界值附近的数据可以快速判断是边界问题还是数据本身脏。第二步看执行计划。如果你怀疑性能问题EXPLAIN PLAN或者查看v$sql里的实际执行计划重点看Predicate Information部分有没有函数转换的痕迹有没有隐式转换。第三步看参数。包括NLS_DATE_FORMAT、NLS_SORT、NLS_COMP这些数据库参数会影响日期字符串的解析和字符串排序顺序。我之前遇到过开发机器上查出来结果和测试环境不一致最后发现是两个库的NLS_DATE_FORMAT不一样。第四步看统计信息。DBMS_STATS的LAST_ANALYZED字段能告诉我这个表多久没采过统计信息了。特别是那种每个月疯狂写入的表如果统计信息严重过期CBO 会基于错误的估算选择执行计划这时候BETWEEN范围条件可能是受害者而不是原因。5.3 独家防坑三个我自己的写 SQL 习惯第一个习惯能拆就拆尽量别写 BETWEEN。这不是说 BETWEEN 不好而是和的写法在边界语义上更透明代码评审时一眼就能看出来开闭。尤其日期时间左闭右开是统一标准省得每个开发都有一套自己的边界理解。第二个习惯写 BETWEEN 时永远先标出边界值。比如在注释里写明包含 1 号不包含 31 号或者包含 100 到 200 两个端点。注释花不了几个字节但能避免后来维护的人改错边界。很多遗留系统里的 SQL 全靠注释救命。第三个习惯给范围查询设计参数时默认用“起始值 结束值”而不是“起始值 数量”。比如查询2024-01-01 到 2024-01-31的订单如果入参是起始日期 天数 31你去算结束日期时很容易掉进时区、闰年之类的坑而且写出来的 SQL 在边界上更难控制。我见过不止一个统计系统因为起始 天数的入参设计在月底和年底出现数据重复或漏数。6. BETWEEN 之外的延伸遇到“区间”需求你还可以想想这些6.1 多个区间的“或”运算IN 和 OR 的组合有时候业务要查金额在 100 到 200 之间或者 500 到 600 之间这种多区间场景不能直接写一个BETWEEN需要组合SELECT * FROM orders WHERE (amount BETWEEN 100 AND 200) OR (amount BETWEEN 500 AND 600);但如果你有更多区间且区间数量不固定这种硬编码写法很快会变得难以维护。这种场景下更好的做法是把区间配置存到一张区间表里然后做关联查询SELECT o.* FROM orders o JOIN amount_range r ON o.amount BETWEEN r.min_amount AND r.max_amount;这里BETWEEN出现在 JOIN 条件里属于非等值连接。这种写法很灵活但要注意两个区间表之间没有等值条件执行计划可能会走嵌套循环或者哈希连接数据量大时很容易变成慢查询。实际项目里我会在amount_range表数据量很小的情况下比如几十行才这么用区间表一旦上百行性能就开始难受了。6.2 时间区间重叠判断BETWEEN 解不了的问题BETWEEN能判断一个点是否落在区间里但判断两个区间是否重叠比如会议室预订系统的时段冲突就不能直接用BETWEEN完成了。两个时间段[A1, A2)和[B1, B2)重叠的充要条件是WHERE A1 B2 AND A2 B1这个公式写成 SQL 就是两条普通的不等式不涉及BETWEEN。我之前帮一个朋友改过一个会议室预订模块他最初的想法是查A1 在 B1 和 B2 之间或者A2 在 B1 和 B2 之间结果漏掉了一个区间完全包含另一个区间的情况。后来改用上面的区间重叠判断才把问题彻底解决。这个例子说明BETWEEN只是区间判断的工具之一它擅长点与区间的包含关系但不擅长区间与区间的相交关系。遇到后者要果断换思路。7. 写到最后我踩过几次坑之后的个人体会回到开头那句话BETWEEN是 Oracle SQL 里看似最简单、实际最容易暗藏边界问题的语法。我从新手期到现在的习惯变化可以浓缩成三句话第一永远先确认业务要的是闭区间还是开区间然后选择对应的写法。BETWEEN是闭区间是左闭右开这两者不能凭感觉混用。第二日期时间字段千万别直接用字符串去 BETWEEN隐式转换会让你的当天变成一个点而不是一天。想省心就写 TRUNC(SYSDATE) AND TRUNC(SYSDATE) 1再配合索引性能也稳。第三遇到字符串类型的编号做范围过滤先停下来想想字段类型是不是设计错了。如果表已经上线很久、改不了类型那就建函数索引别硬扛。最后分享一个我一直在用的调试小技巧写任何带BETWEEN的 SQL先把它翻译成和两个条件想想边界值会不会出问题。如果翻译之后的逻辑能说服你再改成BETWEEN缩写或者保持原样都行。这个习惯帮我拦截了不少线上事故希望你也能用上。