上周线上有个查询业务同学想拉“7月1日到7月5日注册的用户”SQL写的是register_time BETWEEN 2025-07-01 AND 2025-07-05。结果一跑7月5日当天的数据直接少了一大截前面几天的数据倒是全的。排查到最后原因特别简单BETWEEN AND是闭区间而register_time是 DATETIME 类型2025-07-05 12:00:00这种记录根本不会被包含进去。这种问题在社区里几乎每周都能看到一次。MySQL 的范围查询BETWEEN AND是最常用的语法但也是边界坑最多的语法之一。这篇就围绕它展开从基本语法、闭区间语义到日期时间陷阱、NULL 行为、隐式类型转换、索引优化再到什么时候不该用它一次说清楚。适合刚学 SQL 的新手也适合天天写报表、查订单、做数据分析的同学拿来对照自查。1. BETWEEN AND 的语法边界先从“闭区间”这个最常被忽略的规则说起1.1 最小可运行的 demoBETWEEN AND的语法结构很简单SELECT * FROM orders WHERE order_amount BETWEEN 100 AND 500;它的语义是字段值 100并且 500。注意BETWEEN后面的两个边界值都会被包含在结果里这一点和很多编程语言里的区间习惯不一样。比如SELECT 1 BETWEEN 1 AND 2; -- 1 SELECT 2 BETWEEN 1 AND 2; -- 1 SELECT 0 BETWEEN 1 AND 2; -- 0 SELECT 3 BETWEEN 1 AND 2; -- 0MySQL 里条件为真返回 1为假返回 0。从上面对照就能清楚地看到左边界 1 和右边界 2 都算命中。如果业务上要的是“大于等于 100 且小于等于 500”那BETWEEN AND完全等价于WHERE order_amount 100 AND order_amount 500;从优化器角度看这两种写法生成的执行计划几乎一样都会走 range 扫描。所以选择哪一种更多是代码可读性和团队规范的问题。1.2 边界值到底能不能被查到拿真实数据验证空谈闭区间没意思拿真实表看一眼。假设有一张测试表CREATE TABLE t_amount ( id INT PRIMARY KEY AUTO_INCREMENT, amount DECIMAL(10,2) ); INSERT INTO t_amount(amount) VALUES (100.00), (100.01), (499.99), (500.00), (500.01);然后执行SELECT id, amount FROM t_amount WHERE amount BETWEEN 100 AND 500 ORDER BY amount;结果会包含100.00和500.00这两条边界记录。如果业务规则是“满 100 减 50金额大于 100 才参与活动”这种开区间需求直接用BETWEEN AND就会多算。类似的坑还有“已支付金额大于 0 且小于等于 100”如果写成BETWEEN 0.01 AND 100边界通常不会出问题但一旦你习惯性认为“between 包含两端”写出来的过滤条件往往就和产品预期差了一步。1.3 两个边界写反了以及字符串比较的特殊性BETWEEN 100 AND 500是正常写法但如果写成BETWEEN 500 AND 100MySQL不会报错而是直接返回空结果集。因为优化器把它翻译成 500 AND 100这个条件本身就是矛盾的。这个问题在动态拼接 SQL 时尤其容易出现比如前端传了两个参数min和max没做大小校验就拼进 SQL结果线上查不出来数据第一反应还以为是系统 bug。另一个容易忽略的是字符串比较。BETWEEN a AND z比较用的是字符集的排序规则不是你以为的“字典序”或“ASCII 码序”。在 utf8mb4 默认排序规则下SELECT A BETWEEN a AND z; -- 结果和你预期可能不一样大小写、中文、特殊字符的排序规则在不同 collation 下完全不同。所以不建议拿 BETWEEN AND 做字符串范围过滤特别是业务上的编码范围比如BETWEEN A001 AND A100一旦编码长度不固定结果就会和想象对不上。老老实实用前缀匹配或正则至少逻辑是清晰的。2. 日期时间范围查询查“某一天”时最隐蔽的三个坑2.1 纯 DATE 列传日期字符串为什么没问题如果字段类型是DATE比如create_date DATE那么SELECT * FROM orders WHERE create_date BETWEEN 2025-07-01 AND 2025-07-05;能正确查出 7 月 1 日到 7 月 5 日的数据。因为 DATE 类型存储的就是“天”MySQL 会把字符串边界隐式转换成 DATE 再比较没有小时分钟秒的精度问题。这是很多新手最容易混淆的地方看到别人说“between 查日期会丢数据”就以为自己也会丢其实关键要看列的数据类型。但这里有个附加提醒不要为了格式化日期在列上套函数。比如WHERE DATE_FORMAT(create_date, %Y-%m-%d) BETWEEN 2025-07-01 AND 2025-07-05;虽然在 DATE 列上套DATE_FORMAT后结果看起来一样但这一层函数包裹基本就把索引废掉了。稍大点的表就是全表扫描后面会专门讲索引问题。2.2 DATETIME 列传 ‘YYYY-MM-DD’ 会吃掉“当天”这是整篇文章里最常见、也最坑的一个点。当列是DATETIME或TIMESTAMP比如register_time DATETIME你写SELECT * FROM user WHERE register_time BETWEEN 2025-07-01 AND 2025-07-05;这条 SQL 的实际过滤范围是2025-07-01 00:00:00到2025-07-05 00:00:00。注意右边界是“7 月 5 日零点整”也就是说2025-07-05 00:00:01及之后的所有数据全都不在结果里。7 月 5 日当天 99.99% 的数据都被漏掉了。正确做法有三种-- 写法一右边界加时间 WHERE register_time BETWEEN 2025-07-01 00:00:00 AND 2025-07-05 23:59:59; -- 写法二推荐半开区间 WHERE register_time 2025-07-01 00:00:00 AND register_time 2025-07-06 00:00:00; -- 写法三动态生成次日零点 WHERE register_time 2025-07-01 AND register_time DATE_ADD(2025-07-05, INTERVAL 1 DAY);我强烈推荐第二种或第三种。原因很简单不用去记“23:59:59”这种带秒的边界也不怕以后字段精度升级成 DATETIME(3) 带毫秒。万一哪天业务把列改成DATETIME(3)2025-07-05 23:59:59就会漏掉23:59:59.500这种记录而半开区间永远不受影响。2.3 TIMESTAMP 与时区你以为查的是这个时间数据库以为是另一个时间TIMESTAMP和DATETIME有很多区别其中和范围查询最相关的是时区行为。TIMESTAMP在存储时会把会话时区转成 UTC读取时再转回当前会话时区。如果应用服务器和数据库的time_zone设置不一致同一个BETWEEN AND边界在不同连接里查出来的结果集可能不一样。举个例子你有一条记录业务时间是2025-07-05 10:00:00。会话 A 的时区是08:00会话 B 设成了00:00。在会话 B 里用WHERE register_time BETWEEN 2025-07-05 09:00:00 AND 2025-07-05 11:00:00;这条记录能不能查到取决于数据库如何解释register_time的存储值和时区转换逻辑。排查这种诡异问题时第一件事就是看两边会话的time_zone变量SELECT global.time_zone, session.time_zone;如果是互联网应用建议统一让上下游都用 UTC 或者统一用 Asia/Shanghai并且连接串里显式指定。别指望“数据库默认就是对的”时区问题在范围查询里是典型的“玄学 bug”一旦出现很难靠肉眼 review 发现。3. NULL 与类型转换BETWEEN AND 不会告诉你的两个“静默行为”3.1 NULL 值永远不在范围内先看一段 SQLSELECT * FROM t_score WHERE score BETWEEN 60 AND 90;假设这张表里有几行score IS NULL结果会是什么很多人以为“NULL 不在 60 到 90 之间所以不会出现”这个判断对了一半。真正的问题是NULL参与BETWEEN AND比较时结果是NULL而 WHERE 子句只接受条件为真的行NULL 会被当成不成立过滤掉。所以本质上NULL不会出现在BETWEEN AND的结果里这看起来没什么问题。真正的坑在NOT BETWEEN。比如SELECT * FROM t_score WHERE score NOT BETWEEN 60 AND 90;这条语句的语义是“成绩不及格或超过 90 分的人”但实际结果里不会包含任何score IS NULL的记录。因为在 SQL 的三值逻辑里NOT NULL依然是NULL而WHERE只保留为真的行。如果业务上确实要把“缺考”也归入“不达标”必须显式补上 NULL 条件SELECT * FROM t_score WHERE score NOT BETWEEN 60 AND 90 OR score IS NULL;这个细节在统计报表、绩效考核、数据清洗的 SQL 里非常容易踩。面试题也爱问“BETWEEN会不会返回NULL为什么”答案就是不会NULL比较结果是NULL行会被过滤。条件表达式score 值结果是否出现在 BETWEEN 结果中score BETWEEN 60 AND 90751是score BETWEEN 60 AND 90950否score BETWEEN 60 AND 90NULLNULL否score NOT BETWEEN 60 AND 90951是score NOT BETWEEN 60 AND 90NULLNULL否3.2 隐式类型转换列类型是 VARCHAR条件却写数字另一个隐蔽问题是类型转换。假设表里有个字段coupon_code VARCHAR(32)里面存的是券码比如100001、200001这种你为了省事直接写SELECT * FROM coupon WHERE coupon_code BETWEEN 100001 AND 200000;表面上看MySQL 会帮你把字段值和数字比较时做隐式转换。但问题是当字符串列和数字常量比较时MySQL 通常会尝试把列的字符串值转换成数字转换过程相当于在列上加了隐式函数索引基本失效。用EXPLAIN看会发现type ALL也就是全表扫描。表小没感觉表一大几百 G这种 SQL 能把数据库 CPU 打满。验证方法很简单EXPLAIN SELECT * FROM coupon WHERE coupon_code BETWEEN 100001 AND 200000;解决方式也很直接把 SQL 改成字符串边界SELECT * FROM coupon WHERE coupon_code BETWEEN 100001 AND 200000;并确保coupon_code字段本身有索引。这里要额外注意如果券码是变长的比如9和100001字符串比较和数字比较的结果会完全不同所以编码规范上最好统一固定长度或者直接用数字类型存储纯数字编码。3.3 当边界本身就是 NULLBETWEEN 直接变空还有一种边界情况WHERE score BETWEEN NULL AND 90。因为左边界的值就是NULL整个比较表达式的结果是NULL最终返回空集。这种情况在动态 SQL 里比较常见后端把参数为空的min拼成了NULL而不是直接去掉条件。比如 Java 里minScore为空时拼出了BETWEEN NULL AND 90不会报错但结果就是空。排查时别光看 SQL 是否符合预期还要看参数是不是“伪装成 0 的 NULL”。如果框架支持这类条件最好在应用层做空值判断不要依赖数据库的隐式行为。4. 优化器视角BETWEEN AND 到底怎么走索引4.1 EXPLAIN 里看到 range 就知道没写错先说结论只要列上有普通索引BETWEEN AND在大多数情况下都会被优化器转成range扫描和 是等价的。假设表结构CREATE TABLE t_order ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_amount DECIMAL(10,2) NOT NULL, order_time DATETIME NOT NULL, KEY idx_amount (order_amount), KEY idx_order_time (order_time) ) ENGINEInnoDB;执行EXPLAIN SELECT * FROM t_order WHERE order_amount BETWEEN 100 AND 500;EXPLAIN结果里type那一列显示rangekey显示idx_amount说明范围扫描生效。如果你看到type ALL就要检查两件事一是列上有没有索引二是是否在列上写了函数或发生了隐式转换。用key_len还能判断边界条件是否完整。比如idx_order_time是二级索引走BETWEEN 2025-07-01 AND 2025-07-05时key_len会覆盖完整 5 字节左右DATETIME 在 MySQL 5.6 是 5 字节如果某些情况下key_len变短说明优化器只用了部分条件过滤不够彻底。4.2 联合索引里 BETWEEN 的位置决定“生死”联合索引的范围查询比单列索引复杂得多。假如有联合索引(a, b)索引顺序是先 a 后 b那么WHERE a 1 AND b BETWEEN 10 AND 20这个查询可以完美利用联合索引a 用来定位等值b 用来范围扫描。但写成WHERE a BETWEEN 10 AND 20 AND b 5情况就不一样了。联合索引最左原则下a 已经走了范围b 列就无法再用于索引精确定位。也就是说b 5 这个条件虽然在索引里存在但优化器只能用它做“索引条件下推”(ICP)在二级索引内部过滤一部分数据减少回表次数却不能像等值条件下那样把索引树直接定位到最小扫描区间。实际影响是b BETWEEN放在联合索引前面还是放在后面性能差异可能是一个走索引范围扫描一个是扫描大量索引叶节点。所以我写 SQL 的时候有个习惯能先等值再范围的绝对先等值。比如用户 订单时间这种组合永远先写user_id xxx再写order_time BETWEEN ...。4.3 大范围查询优化器可能放弃索引另一个容易被忽略的点是优化器不是见了 BETWEEN 就一定走索引。如果范围特别大比如一张千万级订单表你查WHERE order_time BETWEEN 2020-01-01 AND 2024-12-31MySQL 计算后发现这个范围覆盖了表中超过一定比例的行直接走全表扫描可能比回表更快。这时候EXPLAIN里type会出现ALL但这不是 SQL 写错了是优化器的成本决策。遇到超大范围查询想要真正提升性能常用的手段有三个拆成多个小的范围查询比如按年、按月分批应用层并发或循环处理避免一次锁定大量行。建覆盖索引如果只需要查询部分列让索引完全包含查询字段可以避免回表。配合分页限制比如BETWEEN AND LIMIT 1000至少控制单次返回行数避免内存和网络被打满。顺便说一句如果你是在大表上做离线统计任务建议错峰执行并且尽量缩小时间窗口或者走只读从库不然一个大的 range 扫描就能把主库的 IO 拉满。5. 何时不该用 BETWEEN AND替代方案与真实业务选择5.1 枚举型范围IN 比 BETWEEN 更精确BETWEEN AND处理的是“连续区间”。但业务里经常遇到“离散枚举”比如订单状态查询状态值只有1、3、7有人为了图方便写成WHERE status BETWEEN 1 AND 7这会把2、4、5、6这些不存在的状态也放进扫描范围。虽然结果可能恰好没有这些状态的数据但语义上已经错了。如果哪天有人把状态2加回来这条 SQL 就会静默地把错误数据带进报表。正确的写法是WHERE status IN (1, 3, 7)IN在优化器里会转成多个等值条件的组合对索引同样友好。所以判断标准很简单只要业务数据的取值范围不是连续满的就别用 BETWEEN。5.2 开区间、半开区间只能用 和 自己搭BETWEEN AND天生就是闭区间它做不了“大于等于 100 且小于 500”这种右开区间。所有需要排除右边界的场景都必须显式写WHERE amount 100 AND amount 500另外还有一种常见需求“查询最近 30 天”。如果直接用BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()边界会命中今天零点导致把今天凌晨的数据算进去又可能漏掉今晚 23:59 之后还没发生的数据。正确姿势是WHERE create_time DATE_SUB(NOW(), INTERVAL 30 DAY) AND create_time NOW()半开区间基本能覆盖绝大多数业务左闭右开、左开右闭都可以通过调整边界实现。所以我个人写范围查询的默认习惯是能用半开区间就少用 BETWEEN只看明确需要闭区间时才用。这样字段精度一升级代码风险也最小。5.3 区间重叠判断BETWEEN 最该出现的经典场景BETWEEN AND真正的强项是判断“一个值是否落在给定的连续区间内”。比如查询某个订单是否落在活动期间SELECT * FROM activity_orders WHERE order_time BETWEEN activity_start_time AND activity_end_time;但如果你要判断的是“两个区间是否有重叠”就不能直接套 BETWEEN 了。比如排课系统里课程时间是[start1, end1]现在要找出所有和[start2, end2]有重叠的课程SELECT * FROM course WHERE start1 end2 AND start2 end1;这个公式就是区间重叠的经典判断很多新人第一次接触会觉得“怎么这么绕”。原因在于两个区间不重叠只有两种情况要么end1 start2要么end2 start1。取反就是重叠条件start1 end2 AND start2 end1。别看它简单优惠券有效期、会议室预订、活动排期、订单预约这类需求的 SQL 核心全是这一条。会了这个比背一百个 BETWEEN 语法都管用。最后分享一个我实际养成的小习惯每次写范围查询先看字段类型再写边界条件写完之后立刻跑一下 EXPLAIN确认 type 不是 ALL涉及日期时间默认用加次日零点的方式。这套流程帮我挡掉了不少线上事故。如果你以前只知道 BETWEEN 的快乐写法下回遇到边界情况时希望能想起今天说的这些坑。