SQL 的BETWEEN看起来是个人人都觉得“太简单了”的语法无非就是WHERE 字段 BETWEEN 值1 AND 值2边界包含、闭区间判断。我早年在写业务报表时也是这么用的直到后来在日期时间字段上接连翻车才发现这玩意儿的水远比想象中深——边界值是不是包含DATETIME带毫秒怎么办为什么同一条 SQL 在 A 表走索引、在 B 表不走NOT BETWEEN为什么查出来的数据总少几条这些问题如果没真正踩过坑光靠背语法是永远搞不清楚的。这篇博客会把BETWEEN从语法本质、日期时间陷阱、索引优化、反向语义到替代方案完整讲一遍覆盖我在真实项目和面试题里遇到的各种变体场景。适合刚入门的 SQL 初学者建立正确认知也适合平时写查询但没细想过边界问题的开发同学做一次查漏补缺。1. BETWEEN 的语法本质闭区间判断先从一个最简单的例子说起。假设有一张员工表employee字段包括id、name、salary我想查询月薪在 8000 到 15000 之间的员工最自然的写法就是SELECT id, name, salary FROM employee WHERE salary BETWEEN 8000 AND 15000;这条语句的含义是salary 8000 AND salary 15000。很多人会忽略一个关键点——BETWEEN是包含两个端点的闭区间也就是常说的“左闭右闭”。这一点和大多数编程语言里的切片语义完全不同比如 Python 的range(1, 10)不包含 10但 SQL 的BETWEEN 1 AND 10一定会包含 10。1.1 边界值包含带来的真实影响很多业务逻辑里的细微 bug 都出在这里。比如统计某个时间段内的订单量SELECT COUNT(*) FROM orders WHERE order_time BETWEEN 2024-01-01 00:00:00 AND 2024-01-31 23:59:59;如果你写成BETWEEN 2024-01-01 AND 2024-01-31在 MySQL 里2024-01-31会被自动补成2024-01-31 00:00:00那么 1 月 31 日当天所有非零点时间的订单都会被漏掉。这类问题在月底最后一天尤为致命因为肉眼几乎无法从结果中发现少了数据。另一个容易踩的点是当边界值本身不是字段类型时数据库会做隐式转换。比如字段是INT你写BETWEEN 8000 AND 15000大多数数据库会帮你把字符串转成数字再比较结果通常没错。但反过来字段是VARCHAR里面存储的却是身份证号或手机号此时你写BETWEEN 13000000000 AND 13999999999数据库会先把数字转成字符串再按字典序逐个字符比较结果会完全出乎意料。所以我的建议很简单写 BETWEEN 时边界值的类型一定要和字段类型严格一致别依赖隐式转换。1.2 BETWEEN 与 AND 的语义等价性严格来说BETWEEN a AND b就是 a AND b的语法糖。理解这一点对后续调试和改 SQL 非常关键因为你随时可以把 BETWEEN 展开成比较运算符来排查问题。有一个经常被忽略的细节当a大于b时比如BETWEEN 100 AND 50这条 SQL 不会报错但查询结果一定是空。原因是它等价于 100 AND 50这个条件永远为假。不同数据库的行为一致没有任何一个版本会给你抛异常。如果你在存储过程里用变量拼 SQL变量值顺序被传反时你得到的就是静默的空结果集——这种 bug 极难排查因为语法没问题、执行也没报错就是没数据。另外BETWEEN对NULL的处理也值得单独说如果参与比较的字段值为NULL那么整个比较结果不是TRUE也不是FALSE而是UNKNOWN。在WHERE子句里UNKNOWN和FALSE都会导致该行被过滤掉。也就是说BETWEEN查出来的结果天然不包含字段值为 NULL 的行。这一点和直觉一致但如果你用NOT BETWEEN情况就会反转我后面专门讲。2. 日期时间场景动态边界与午夜陷阱日期时间字段是 BETWEEN 用得最多、也最容易出错的场景。很多初学者觉得日期就是“字符串比较”然后直接把2024-01-01这样的写法套进去结果月底数据对不上月初又多了重复数据。2.1 精确到秒还是毫秒结果天差地别先看一个典型需求查询 2024 年 3 月的所有订单。很多新手会写成SELECT * FROM orders WHERE order_time BETWEEN 2024-03-01 AND 2024-03-31;在 MySQL 中order_time如果是DATETIME类型上面的写法等价于WHERE order_time BETWEEN 2024-03-01 00:00:00 AND 2024-03-31 00:00:00也就是说3 月 31 日零点之后的每一笔订单都被吃掉了。正确写法有两种-- 写法一手工补全时间直观但容易漏掉毫秒 WHERE order_time BETWEEN 2024-03-01 00:00:00 AND 2024-03-31 23:59:59; -- 写法二借用“下月月初”作为右边界闭区间变半开区间最稳妥 WHERE order_time 2024-03-01 00:00:00 AND order_time 2024-04-01 00:00:00我强烈推荐第二种写法。原因有两个一是你不需要纠结月底是 28 号、30 号还是 31 号右边界永远按下个月的 1 号零点来写干净利落二是如果字段精度是DATETIME(3)或DATETIME(6)即带毫秒微秒那么23:59:59仍然会漏掉23:59:59.123这种记录而用 下月月初就完全不存在这个问题。2.2 动态边界别写死日期实际业务里很少把日期写死大部分场景要看“从今天往前推 30 天”“上个月整月”“最近 7 天”这类动态区间。不同数据库的写法有差异但思路一致先算出边界再带入区间。MySQL 里典型的动态区间写法-- 最近 7 天含今天 SELECT * FROM orders WHERE order_time CURDATE() - INTERVAL 6 DAY AND order_time CURDATE() INTERVAL 1 DAY; -- 上个月整月 SELECT * FROM orders WHERE order_time DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, %Y-%m-01) AND order_time DATE_FORMAT(CURDATE(), %Y-%m-01);注意这里依然用了半开区间思路右边界是“今天加一天”保证把今天的数据全部算进去不会因为时间是00:00:00还是23:59:59而出错。SQL Server 里对应的写法是SELECT * FROM orders WHERE order_time DATEADD(DAY, -6, CAST(GETDATE() AS DATE)) AND order_time DATEADD(DAY, 1, CAST(GETDATE() AS DATE));Oracle 则常用SYSDATE和TRUNC组合。不同数据库的日期函数名差异很大但核心模式永远是把日期边界先算出来再转成字段类型对应的精确时间。不要让数据库在比较时再去“猜”你的边界是什么精度。2.3 时区问题别让边界看起来对、算起来错如果你处理的是跨国业务时区问题就是躲不掉的坑。数据库服务器可能部署在 UTC 时区业务方却在东八区那么你写BETWEEN 2024-01-01 00:00:00 AND 2024-01-01 23:59:59在 UTC 库里实际匹配的是北京时间早上 8 点到次日早上 8 点之前的数据。这类问题的排查难度极高因为 SQL 本身没有语法错误返回的数据也有值就是不匹配业务预期。我的习惯是在写任何涉及日期时间的查询前先确认三件事——数据库时区是什么、连接会话时区是什么、业务需要的时区是什么。对于 MySQL可以执行SELECT global.time_zone, session.time_zone;快速查看。如果业务统一用东八区那么最稳妥的方式是在应用层把时间先转成东八区的绝对时间戳再传给 SQL让数据库只做纯粹的区间比较避免所有参与比较的值受到时区转换的二次影响。2.4 时间字段上的函数操作索引失效的隐患很多时候大家喜欢对字段做一层转换再比较比如WHERE DATE(order_time) BETWEEN 2024-03-01 AND 2024-03-31这种写法在逻辑上没毛病但DATE(order_time)属于对字段的函数操作会导致索引失效执行计划里大概率出现全表扫描。当表只有几千行时无所谓但当表里有几百万订单时这条 SQL 可能从几十毫秒拖到几秒甚至几十秒。正确做法是对字段本身做范围比较让查询条件落在裸字段上WHERE order_time 2024-03-01 00:00:00 AND order_time 2024-04-01 00:00:00这能直接命中order_time字段上的普通索引。如果你无法避免对字段做函数操作可以考虑建函数索引——MySQL 8.0 和 Oracle 都支持但 SQL Server 的方式是建计算列再索引相对繁琐。总而言之能让比较操作发生在“干净字段”上就绝不对字段套函数。3. 证明 BETWEEN 能走索引执行计划里的 range看到一个说法“BETWEEN 是范围查询走不了索引要用等值查询才行”。这个说法是错的。BETWEEN完全可以使用索引它在执行计划中通常体现为range类型效率介于等值查询ref和全表扫描all之间。3.1 为什么 BETWEEN 能走索引索引的本质是 B 树数据的物理顺序按键值排列。BETWEEN a AND b展开后是 a AND b这本质上是在 B 树上定位到第一个大于等于a的叶子节点然后沿着叶子节点的链表顺序扫描直到键值超过b为止。这正是索引支持的典型范围访问模式。用EXPLAIN看一个例子EXPLAIN SELECT * FROM orders WHERE order_time BETWEEN 2024-03-01 00:00:00 AND 2024-03-10 00:00:00;在 MySQL 中type列大概率显示rangekey列显示命中的索引名filtered表示过滤比例。如果type是all、key是NULL那就要回头检查字段上有没有建索引或者是不是对字段做了函数操作、隐式类型转换。3.2 索引失效的高频原因即使你写了裸字段的 BETWEEN下面的情况仍会让索引失效字段和边界值类型不一致导致隐式转换比如字符串字段和数字边界值比较。排序规则不一致utf8mb4_general_ci和utf8mb4_bin在特定字符上的比较结果可能不同。数据分布极度不均衡优化器认为全表扫描比走索引更快此时type反而会是all这属于正常优化选择不算失效。我见过最典型的案例是把订单号设计成VARCHAR且订单号本身是纯数字字符串。业务 SQL 写成了WHERE order_no BETWEEN 100001 AND 200000字段是字符串、边界是数字数据库把两者比较时做了隐式转换索引直接失效。改成BETWEEN 100001 AND 200000后执行计划从全表扫描变成 range 扫描查询耗时从 2 秒降到 0.03 秒。这类问题的排查思路很简单先DESC或看建表语句确认字段类型再决定边界值的写法。3.3 慢 SQL 优化BETWEEN 的覆盖索引技巧如果一张订单表有几十个字段而你的查询只需要id、order_time、amount三列那么可以建一个覆盖索引让 B 树直接提供查询所需的数据避免回表ALTER TABLE orders ADD INDEX idx_order_time_amount (order_time, amount);之后执行SELECT order_time, amount FROM orders WHERE order_time BETWEEN 2024-01-01 AND 2024-03-01MySQL 在执行计划中会显示Using index这意味着查询所需的列全部可以从索引页获得不需要回到聚簇索引取整行数据。对于大表来说回表是非常昂贵的操作覆盖索引能把随机 I/O 变成顺序 I/O性能提升非常明显。注意索引列的排列顺序等值条件写在前面范围条件写在后面这样索引利用率最高。如果你的查询里既有customer_id等值过滤又有order_time范围过滤那么索引应该设计成(customer_id, order_time)而不是反过来的顺序。4. NOT BETWEEN 与 NULL 的“消失的数据”NOT BETWEEN看起来就是 BETWEEN 取反很多新手认为“查不到的就是反的”。真实世界里这句话只对了一半因为 SQL 的三值逻辑TRUE / FALSE / UNKNOWN在这里会实实在在坑你一把。4.1 为什么 NOT BETWEEN 会丢掉 NULL 字段行考虑一个查询SELECT id, name, salary FROM employee WHERE salary NOT BETWEEN 8000 AND 15000;直觉上这是“查所有不在 8000~15000 区间内的员工”。但如果某员工salary是NULL这一行会被过滤掉。原因在于NULL NOT BETWEEN 8000 AND 15000的求值过程是先计算salary BETWEEN 8000 AND 15000。因为salary是 NULL整个表达式结果是 UNKNOWN。对 UNKNOWN 做 NOT 操作结果还是 UNKNOWN。WHERE 子句只保留结果为 TRUE 的行UNKNOWN 和 FALSE 都被丢弃。所以NOT BETWEEN丢掉了所有工资为 NULL 的员工。在很多业务里salary为空恰恰意味着“没填、待定、不知情”这批数据在统计分析时往往特别重要丢掉它们会让报表产生严重偏差。4.2 正确保留 NULL 的写法如果业务要求“不在区间内或者工资为空”的员工都要查出来你必须显式补上 IS NULL 条件SELECT id, name, salary FROM employee WHERE salary NOT BETWEEN 8000 AND 15000 OR salary IS NULL;这里要加个括号才能避免 AND OR 优先级混乱WHERE (salary NOT BETWEEN 8000 AND 15000 OR salary IS NULL);如果还想加其他过滤条件比如部门编号WHERE dept_id 10 AND (salary NOT BETWEEN 8000 AND 15000 OR salary IS NULL);同样的坑在NOT LIKE、NOT IN里也存在模式都是“NOT 操作不会让 UNKNOWN 变成 TRUE”。我的经验是只要你的字段允许 NULL用的又是 NOT 类操作就要条件反射地问一句“NULL 去哪了”。4.3 字符串排序规则对 NOT BETWEEN 的影响字符串字段的 BETWEEN 不是按“人类直觉的字母顺序”那样简单比较的它取决于数据库的排序规则。MySQL 里utf8mb4_general_ci不区分大小写所以B BETWEEN a AND c会返回 TRUE因为大写 B 被折叠成小写 b 参与排序。而utf8mb4_bin按二进制比较大写字母排在小写字母前面结果可能完全相反。因此如果你在字符串字段上写 BETWEEN 做类似“姓氏在 A 到 M 之间”的查询务必先确认排序规则。否则同一个 SQL 在测试库和正式库之间因为字符集不一致查出来的数据可能不同。排查方法很简单执行SHOW TABLE STATUS LIKE employee\G或查询information_schema.COLUMNS里的COLLATION_NAME字段。5. 什么时候不该用 BETWEEN边界之外的选择BETWEEN 虽好用但它不是万能的。有些场景用它会让 SQL 变得别扭甚至出错这里总结几类典型的“反模式”。5.1 开区间场景永远想清楚“是否包含端点”如果业务需求是“年龄大于 18 岁且小于 60 岁”表面看可以用BETWEEN 18 AND 60但仔细想BETWEEN包含 18 和 60 两个端点。18 岁和 60 岁本身到底算不算在需求内完全取决于产品定义。很多统计口径的差异就是这么来的。一旦需要开区间比如“大于 18 且小于 60不包含 18 和 60”就别套 BETWEEN 了WHERE age 18 AND age 60;这种写法语义清清楚楚也不会让后来维护的人去猜你的端点到底包不包含。写 SQL 的第一原则是可读性优先于简洁性别为了少写几个字符埋下一颗语义炸弹。5.2 模糊匹配场景LIKE 和 BETWEEN 的误区有人用 BETWEEN 做模糊匹配比如“查名字中带某个字母的所有用户”写成WHERE name BETWEEN 张 AND 赵然后以为能查出所有姓“张”的人。结果往往令人迷惑因为 BETWEEN 做的是范围匹配不是前缀匹配。如果你想查所有姓“张”的用户正确写法是WHERE name LIKE 张%;两者原理完全不同BETWEEN 是基于排序规则的区间定位LIKE 是基于模式匹配的扫描。用错之后不仅结果不对索引利用情况也完全不同。LIKE 张%在一定条件下能利用索引但BETWEEN的范围语义可能让优化器选择扫描更多的数据页。5.3 多区间集合UNION 还是 OR如果你要查“工资在 1000~2000 或 8000~10000 两个区间内的员工”BETWEEN 没法一步到位最常见的写法是WHERE (salary BETWEEN 1000 AND 2000) OR (salary BETWEEN 8000 AND 10000);这种写法本身没问题但如果区间数量很多比如 10 个以上OR 连接会让优化器的成本估算变得吃力执行计划可能出现奇怪的选择。可以考虑改用UNIONSELECT id, name, salary FROM employee WHERE salary BETWEEN 1000 AND 2000 UNION SELECT id, name, salary FROM employee WHERE salary BETWEEN 8000 AND 10000;UNION自带去重代价是多一次排序行数少时无所谓行数大时要掂量。另一种思路是用一行数据生成多个区间再通过内连接匹配这种写法适合区间非常多、且区间本身存放在配置表的场景CREATE TABLE salary_ranges ( min_salary DECIMAL(10,2), max_salary DECIMAL(10,2) ); SELECT e.id, e.name, e.salary FROM employee e INNER JOIN salary_ranges r ON e.salary BETWEEN r.min_salary AND r.max_salary;这种区间连接range join在数据分析场景里非常实用尤其是处理“用户积分落在哪个会员等级”“交易金额落在哪个手续费档位”这类需求时比一条条写 OR 高效得多。实际执行时要留意一点区间表的行数不能太大否则关联成本会上升。5.4 一次性抽样区间ROW_NUMBER 配合 BETWEEN还有一种场景也经常出现你想查第 100 到第 200 条记录作为分页或抽样。有人直接写SELECT * FROM employee WHERE id BETWEEN 100 AND 200;如果 id 是自增主键且记录没有删除这个写法勉强可用。但一旦中间有删除操作id 就不是连续的这个 SQL 查出来的根本不是“第 100 到第 200 条数据”。正确做法是用窗口函数先编号再过滤SELECT id, name, salary FROM ( SELECT id, name, salary, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM employee ) t WHERE rn BETWEEN 100 AND 200;这里 BETWEEN 用在了窗口函数的输出列上承担的是“取第几行到第几行”的职责语义非常明确。MySQL 8.0、SQL Server、Oracle、PostgreSQL 都支持这种写法。注意子查询别忘加别名。6. 真实项目里的 BETWEEN 使用规范与复盘这部分是我在实际开发中沉淀下来的一些习惯不一定适合所有团队但至少能帮你减少低级 bug。6.1 统一时间粒度全链路约定在一个涉及订单、支付、报表的中型项目里我发现大量 SQL 里的边界时间写得五花八门有人写23:59:59有人写23:59:59.999还有人直接写日期。后来我们定了两条规矩所有跨日统计必须用半开区间[start, end)也就是 开始时间 AND 结束时间。所有日期变量在进入 SQL 之前统一成DATETIME类型不在 SQL 内部做类型转换。执行这规矩后报表对账的出错率明显下降。边界时间处理看起来是小问题但统计口径一旦混乱后续所有依赖它的下游报表都会跟着错。与其靠人肉 review不如从写法上根治。6.2 参数化查询防止注入和安全问题BETWEEN 的边界值如果来自用户输入比如前端传一个价格区间那么 SQL 必须使用参数化查询不能直接拼接字符串。以 JDBC 为例String sql SELECT id, name, salary FROM employee WHERE salary BETWEEN ? AND ?; PreparedStatement ps conn.prepareStatement(sql); ps.setBigDecimal(1, minSalary); ps.setBigDecimal(2, maxSalary);参数化查询不仅是安全需要对数据库优化器也有好处相同的 SQL 文本可以复用执行计划减少硬解析开销。在 MyBatis 等框架里对应使用#{minSalary}而不是${minSalary}后者本质上是字符串拼接既危险又破坏执行计划缓存。6.3 边界自检清单现在我在写完一条带 BETWEEN 的 SQL 时会下意识做五个检查字段类型和边界值类型是否一致不一致就显式转换。右边界是否用写死的时间字符串如果是日期时间字段优先改成 结束日期的半开区间。查询条件的字段上有没有索引执行计划是否出现range是否涉及 NULL 字段的反向查询NOT BETWEEN会不会把 NULL 过滤掉业务语义上端点到底算不算包含端点用 BETWEEN不包含就换成和。这五条看起来简单但每条背后都有真实事故的教训。我见过因为23:59:59漏掉毫秒订单导致对账不平的也见过 NOT BETWEEN 把工资空值字段过滤掉导致人力成本统计偏低的还有过把 VARCHAR 字段和数字边界比较导致索引失效、整库慢查询的历史。全部复盘下来最大的领悟是不是 BETWEEN 本身多难而是用它的人往往忽略了它背后的比较语义和数据库底层机制。6.4 一个小技巧边界常量复用在报表脚本里如果多个 SQL 都要用到同一个“本月开始时间”不要在每个 SQL 里重复写日期计算表达式先定义成变量或临时表。MySQL 的存储过程或脚本里可以这样SET month_start DATE_FORMAT(CURDATE(), %Y-%m-01); SET month_end DATE_ADD(month_start, INTERVAL 1 MONTH);然后在各个查询里统一写WHERE order_time month_start AND order_time month_end;这样既保证一致性又便于维护。如果用的是 SQL Server可以用变量或 CTE 实现类似效果。根据我个人的使用经验BETWEEN 最理想的使用场景就是“两个静态边界、类型一致、字段可空性明确、端点包含合法”。一旦需求偏离这四条中的任何一条我都建议先停下来把这个语法换成更显式的比较运算。这不是嫌弃它而是 SQL 这门语言讲究的是自解释——三个月后的你和接手的同事都需要一眼看懂这条语句到底在算什么。