做Oracle相关工作有些年头了经常看到群里有人问“BETWEEN到底是包含还是不包含边界”、“我查某个月的订单月末那几天怎么老是缺数据”、“为什么加了BETWEEN条件之后反而慢成龟速”。说真的BETWEEN在Oracle里看起来是个再简单不过的操作符但因为它涉及边界值、隐式转换、字符排序规则、NULL语义这些隐藏问题一旦没搞清楚线上出问题排查半天都找不到根因。这期是Oracle语句系列的第16期我就把BETWEEN的用法彻底拆开从语法本质到执行计划再到各种实战坑一次讲透适合正在学Oracle SQL的初学者也适合写过几年SQL但偶尔被BETWEEN绊一下的同学。1. BETWEEN到底是什么先把这个操作符的老底摸清楚1.1 它并不是一个独立的三目运算而是两个比较条件的语法糖很多初学者会把BETWEEN理解成某种特殊的“区间判断函数”其实Oracle里它就是一个条件表达式的简写。你写SELECT * FROM emp WHERE salary BETWEEN 5000 AND 10000;Oracle在内部解析时把它等价转换成SELECT * FROM emp WHERE salary 5000 AND salary 10000;看到没有它本质上就是“大于等于下限且小于等于上限”的合写。这个认知非常关键因为后续所有边界问题、NULL问题都可以从这个等价关系推导出来而不是靠死记乱猜。我在带新人时经常让他们做一个小测试把BETWEEN换成和之后结果是否完全一致执行计划是否完全一致结论是绝大多数情况下完全一致。Oracle优化器对这两种写法的解析路径基本相同不会因为你用BETWEEN就更高级也不会更慢。所以它是个纯粹的语法糖没有魔法。搞清楚这一点还有一个实际好处当你需要把BETWEEN区间改成半开区间比如日期范围或者需要在边界上加条件你可以很自然地展开成和而不是被BETWEEN的固定写法绑住。1.2 两个边界都包含别拿其他语言的区间习惯来套这是BETWEEN被问得最多的问题没有之一。SQL里的BETWEEN是闭区间也就是col BETWEEN low AND high等价于col low AND col high两个边界都算数。很多人写代码习惯了下标0开始、substring右边界不包含之类到了SQL里想当然地以为是[low, high)然后统计结果差了边界值怎么对都对数不上账。举个具体例子。查询薪资在5000到10000区间的员工SELECT emp_name, salary FROM emp WHERE salary BETWEEN 5000 AND 10000;如果某个员工的薪资正好是5000或正好是10000他一定会被查出来。如果你要的是“5000以上10000以下不含两端”那BETWEEN就不适用得写成SELECT emp_name, salary FROM emp WHERE salary 5000 AND salary 10000;这个差异在数值型字段上还算好发现一旦换到日期、字符串很多人就开始晕了。我建议在团队规范里直接写明BETWEEN两边均包含如果业务上要求“左侧包含、右侧不包含”不要用BETWEEN直接用和。把这条写进规范比在代码评审时反复纠正效率高得多。1.3 从执行计划看BETWEEN它是怎么被索引优化的你可能会想BETWEEN和两个比较条件等价那索引利用情况如何答案是同样优秀。假设emp表的salary字段上建了索引执行EXPLAIN PLAN FOR SELECT * FROM emp WHERE salary BETWEEN 5000 AND 10000; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);执行计划里会出现类似这样的列操作类型INDEX RANGE SCAN索引范围扫描访问谓词salary 5000 AND salary 10000也就是说BETWEEN条件被拆成了两个范围谓词数据库可以利用B树索引的有序性直接定位到5000这个位置然后一路扫到10000不需要全表扫描。这个特性决定了在查询频繁使用的数字、日期区间时给列建索引能带来非常直接的性能提升。但反过来说如果你在BETWEEN的列上套了函数比如WHERE TRUNC(create_time) BETWEEN ...索引基本就废了优化器只能全表扫。这个坑后面单独讲这里先记住一个核心原则BETWEEN本身不伤索引伤索引的是你在比较列上做的操作。2. 最容易翻车的边界值场景日期、字符串、非数字2.1 日期字段用字符串边界月末最后一天为什么查不到这是我在实际项目里见过最多、也最经典的BETWEEN翻车现场。很多刚从MySQL或者其他数据库转过来的同学习惯性写SELECT * FROM order_info WHERE create_time BETWEEN 2023-01-01 AND 2023-01-31;在Oracle里如果create_time是DATE类型这个写法表面上看是“查一月份”实际上问题大了。DATE类型是带时分秒的Oracle会把字符串2023-01-31隐式转换成日期2023-01-31 00:00:00。那么这个条件的含义是WHERE create_time 2023-01-01 00:00:00 AND create_time 2023-01-31 00:00:00也就是说1月31日零点之前的数据能查到零点之后的数据全被排除。如果业务上大部分订单发生在白天和下午那你查出来的“一月订单”直接少了一整天报表对账时出现负数差额都不知道去哪儿找。正确做法是把它改成半开区间利用“下一个月1号”作为上界SELECT * FROM order_info WHERE create_time DATE 2023-01-01 AND create_time DATE 2023-02-01;这样写一月份的所有时间点都被包含进来而2月1日零点整的记录会被排除掉业务语义完全正确。我个人的习惯是所有日期区间需求一律用和永远不碰BETWEEN不是因为它不能用而是因为一般人很难记住“BETWEEN的右边界是当天零点”这个隐含事实出一次事故成本太高。2.2 字符串BETWEEN的排序规则你以为的字母顺序不一定是数据库的字母顺序字符串比较可能是最容易让人掉坑的地方。有人写SELECT * FROM user_info WHERE user_name BETWEEN A AND Z;本意是查所有以字母A到Z开头的用户名。但结果可能就是没有以a、b这类小写字母开头的用户。原因在于Oracle的字符串比较规则取决于字符集和排序规则。如果用二进制排序那么ASCII码里大写字母(65~90)和小写字母(97~122)是分开的Z作为上边界所有小写字母开头的字符串都不会被包含。如果数据库使用语言排序Linguistic Sort那排序规则又可能按字母发音、重音等来排甚至中文环境下还可能涉及拼音排序、笔画排序。换句话说你用中文环境配置的Oracle实例执行BETWEEN A AND Z结果可能和你本地的英文测试库不一样。这是个非常隐蔽的“环境差异”问题。对应策略是字符串范围比较前先确认你依赖的排序规则。可以通过以下SQL查看当前会话的NLS相关参数SELECT * FROM nls_session_parameters WHERE parameter IN (NLS_SORT, NLS_COMP, NLS_LANGUAGE);如果要强制按二进制码点排序可以在会话级别设置ALTER SESSION SET NLS_COMP BINARY; ALTER SESSION SET NLS_SORT BINARY;如果你的业务场景真的需要“按字典序筛字符区间”务必在SQL里明确指定NLSSORT或者预先确认排序规则否则同一个SQL在开发库和预发库结果不一致上线前测试通不过排查起来一头雾水。2.3 VARCHAR2列里混了非数字BETWEEN直接报ORA-01722还有一个高频问题就是“过滤不可转为数字的字符串”。有些遗留系统把“金额”存在VARCHAR2字段里比如1200、89.5、12a、N/A。当你写SELECT * FROM payment_record WHERE amount BETWEEN 100 AND 2000;Oracle会尝试把amount列里的字符串隐式转换成数字去和100、2000比较。一旦遇到12a、N/A这种转不了的值直接抛 ORA-01722: invalid number整个查询挂掉而且没有哪一行能正常返回因为Oracle在扫描到坏数据的瞬间就放弃治疗了。我之前处理过一个跑批任务就是被这个错误困扰。当时的临时解决办法是用正则表达式先做一轮清洗把脏数据挡在外面SELECT * FROM payment_record WHERE REGEXP_LIKE(amount, ^[[:digit:]](\.[[:digit:]])?$) AND CAST(amount AS NUMBER) BETWEEN 100 AND 2000;注意如果金额字段很长、数据量很大这种写法会有性能问题因为正则过滤没法用普通索引。更推荐的做法是从源头治理把字段类型改成NUMBER或者专门建一个转换后的数值列跑批时统一清洗。SQL里能用正则兜底但不要指望它能撑住生产环境的千万级数据量。3. 常见实战场景区间统计与分页里的那些事儿3.1 金额区间统计配合聚合函数实现多分段汇总实际工作里BETWEEN最常用的场景之一就是统计报表。比如电商运营要看不同价格段的订单分布这时候可以用BETWEEN配合SUM(CASE WHEN ...)实现一套干净的分段汇总SELECT COUNT(*) AS total_orders, SUM(CASE WHEN order_amount BETWEEN 0 AND 50 THEN 1 ELSE 0 END) AS segment_0_50, SUM(CASE WHEN order_amount BETWEEN 50 AND 100 THEN 1 ELSE 0 END) AS segment_50_100, SUM(CASE WHEN order_amount BETWEEN 100 AND 500 THEN 1 ELSE 0 END) AS segment_100_500 FROM orders WHERE pay_status PAID;这种写法好处是只要扫一遍表就能同时统计多个区间比分别执行好几条SQL然后手工拼接结果要高效得多也不容易对不上口径。但这里有个业务的坑分段边界如果定义成BETWEEN 0 AND 50和BETWEEN 50 AND 100那么金额正好等于50的订单会被同时算进两个分段里导致各段占比加起来超过100%。这本身不是SQL错而是业务口径没定义清楚。建议在设计分段时统一使用“左闭右开”的口径在SQL里手动写成和SUM(CASE WHEN order_amount 0 AND order_amount 50 THEN 1 ELSE 0 END) AS segment_0_50 SUM(CASE WHEN order_amount 50 AND order_amount 100 THEN 1 ELSE 0 END) AS segment_50_100这样每个订单只会落到一个桶里报表合计永远等于总数。几十个字段的大报表最后挪数据的时候你就能体会到这个决定有多值了。3.2 分页查询中别拿ROWNUM配BETWEEN这是经典的查不到数据案例很多网上资料在讲Oracle分页时会提到ROWNUM但新手很容易写出下面这种完全不对的SQLSELECT * FROM emp WHERE ROWNUM BETWEEN 5 AND 10;看起来像是“取第5行到第10行”实际上一行都查不到。这是Oracle新手最容易踩的暗坑之一。ROWNUM伪列有个特点它在结果集返回一行时就立刻分配一个序号序号从1开始而且只有满足其他WHERE条件、被ROWNUM接受的行才会继续产生下一行序号。也就是说ROWNUM 5 这个条件在数值还等于1的时候就已经不成立了这一行被直接丢弃下一行依然从1开始排队永远到不了5。正确的分页方式要么是三层子查询加ROWNUMSELECT * FROM ( SELECT a.*, ROWNUM AS rn FROM ( SELECT emp_id, emp_name, salary FROM emp ORDER BY emp_id ) a WHERE ROWNUM 10 ) WHERE rn 5;要么用12c以后提供的OFFSET ... FETCH NEXT ... ROWS ONLYSELECT emp_id, emp_name, salary FROM emp ORDER BY emp_id OFFSET 4 ROWS FETCH NEXT 6 ROWS ONLY;这里强行用BETWEEN去替代ROWNUM只会给自己挖坑。BETWEEN适合表达“某个业务字段的取值范围”而不是“结果集中的行号范围”两者的语义完全不同。3.3 NOT BETWEEN也不是简单的区间取反NULL语义要注意再来一个容易出错的点NOT BETWEEN。它的等价写法是col low OR col high注意是OR不是AND。很多人把这个和“不属于某个区间”划等号理解没问题但在有NULL值的情况下就会出岔子。看这个例子SELECT * FROM emp WHERE salary NOT BETWEEN 1000 AND 5000;如果某个员工的salary是NULL这条记录会不会被查出来答案是不会。原因还是BETWEEN展开后的比较逻辑salary NOT BETWEEN 1000 AND 5000等价于salary 1000 OR salary 5000而不管是什么值与NULL做比较结果都是UNKNOWN最终被WHERE过滤掉。也就是说工资为空的员工不会出现在“不在1000到5000区间”的结果里。业务上如果你希望“工资为空的员工也视为不在区间内”就必须显式补上IS NULL条件SELECT * FROM emp WHERE salary NOT BETWEEN 1000 AND 5000 OR salary IS NULL;很多报表线上数量对不上查到最后都是这种NULL边界造成的。排查思路很简单把SQL拆开逐段用COUNT(*)验证看到底是丢了NULL行还是丢了边界行。一旦定位到NULL别慌按业务口径补条件就行。4. 常见问题与排查技巧实录4.1 现象一加了BETWEEN之后查询慢得离谱多半是索引被函数“吃掉”了【排查思路】先用EXPLAIN PLAN看执行计划确认是不是走了FULL TABLE SCAN。如果是往WHERE条件里看BETWEEN列是不是被TRUNC、TO_CHAR、TO_DATE包了一层-- 慢因为TRUNC(create_time)导致无法使用create_time列上的索引 SELECT * FROM order_info WHERE TRUNC(create_time) BETWEEN DATE 2023-01-01 AND DATE 2023-01-31; -- 快create_time作为整体参与区间比较索引可用 SELECT * FROM order_info WHERE create_time DATE 2023-01-01 AND create_time DATE 2023-02-01;【解决方案】不要在索引列上套函数。需要按天统计时优先把条件改成半开区间。如果确实需要TRUNC也可以建函数索引但要付出的维护成本更高。我一般能绕开就绕开。4.2 现象二CLIENT端和服务器端查同一个BETWEEN结果不一样【排查思路】同一套数据你在PL/SQL Developer里查得结果和DBeaver查得结果不一致很大概率是会话级的NLS参数不一致。字符串比较、日期格式都受这些参数影响。具体看nls_session_parameters里的NLS_SORT、NLS_COMP、NLS_DATE_FORMAT。【解决方案】对于有严格边界要求的SQL建议在应用层统一设置会话参数或者在存储过程开头ALTER SESSION SET NLS_COMP BINARY; ALTER SESSION SET NLS_SORT BINARY; ALTER SESSION SET NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS;这样在不同客户端执行时边界行为才可控。比起依赖客户端默认配置显式设置是最稳的做法。4.3 现象三BETWEEN字段是VARCHAR2条件里写了数字结果报错或漏数据【排查思路】检查隐式转换。Oracle在比较时会把字符串转数字或把数字转字符串具体方向取决于优先级和内容。如果列里有非数字就会报ORA-01722如果列里全是数字虽然能跑但可能存在索引失效风险。【解决方案】条件里使用与列类型一致的写法列是VARCHAR2就写成BETWEEN 100 AND 200但这会引入字符串排序问题不推荐。列是NUMBER就写BETWEEN 100 AND 200。如果列数据类型本身不合理优先改表结构别在SQL里硬扛。另外提一句遇到“过滤不可转为数字的字符串”这种需求我最常用的办法是先通过REGEXP_LIKE把合法数字筛出来再做区间比较但仅限于数据清洗和临时查询生产环境不推荐依赖它做高频过滤。4.4 问题速查表现象可能原因排查方向查某月数据少了月末最后一天DATE字段含时分秒BETWEEN右边界落在月末零点改用 月初 AND 下月1号字符串范围查不到小写字母字符集或NLS_SORT按二进制排序大小写分区确认NLS参数按需设置BINARY排序VARCHAR2数值列报ORA-01722隐式转换遇到非数字字符过滤脏数据或改用NUMBER类型加了BETWEEN后全表扫描比较列上套了TRUNC/TO_CHAR等函数改写为半开区间或建函数索引同一SQL不同客户端结果不同会话级NLS参数不一致在SQL执行前固定NLS参数分页查不到第5行以后的数据ROWNUM与BETWEEN混用使用子查询ROWNUM或OFFSET FETCH这套速查表基本覆盖了我这些年见到的BETWEEN相关线上问题。很多时候问题并不复杂就是边界语义和隐式转换这一层窗户纸没捅破排查起来才会像是被什么东西绊住了一样。5. 再补几个实际运维中积累的习惯5.1 我自己的几条BETWEEN使用铁律先说结论。这些东西是我踩过几次坑之后沉淀成个人习惯的也成了我Review别人SQL时的默认检查项第一业务区间需求默认写成和的半开区间只在边界明确包含时才用BETWEEN。比如金额分段统计这种明确要包含两个边界值的场景用BETWEEN没问题但涉及日期和时间戳一律半开区间。第二字符串范围比较前先确认字符集和排序规则。上线前拿几组代表性数据在目标环境跑一遍别在本地开发库测完就自信满满地发布。第三看到“列套函数”的条件潜意识就要警惕索引失效。BETWEEN左边的列保持原样任何让条件列变形的写法都要重新评估性能。第四NULL语义单独考虑。无论BETWEEN还是NOT BETWEEN都无法处理NULL值需要额外补IS NULL或IS NOT NULL条件。这一点在写报表SQL时尤其重要。这些规则不是从哪本教科书抄来的而是从一个个“线上对不上账”的深夜排查里换来的。谁踩过谁知道SQL看起来越简单埋坑越深。5.2 一个小技巧把所有BETWEEN展开成比较条件再逐段验证如果你接手了一个复杂报表SQL里面有好几个BETWEEN条件整体结果怎么都对不上我的习惯是“先展开再二分定位”。举个例子把原来的一长串SQL里的每个BETWEEN都手动改成对应的、或、然后逐个条件注释掉用SELECT COUNT(*)跑一遍确认每个条件各自影响的行数。这样几轮下来你很快就能锁定是哪一段条件在作怪。是边界问题还是NULL问题还是字符集问题基本一目了然。这个方法比盯着SQL空想要快得多。尤其是日切、月结这种时间敏感的对账任务早一个小时定位到问题就少一个小时加班。BETWEEN这个操作符本质不复杂但正因为大家都觉得它简单反而容易在细节上翻船。希望这期内容能帮你少踩几个坑。下期我们继续聊Oracle语句里的其他细节到时候见。