MySQL BETWEEN AND 避坑指南：日期边界、字符串与索引的隐藏陷阱

先说个真实场景。我接过不少业务系统的SQL排查每年都能遇到至少三四次因为BETWEEN AND查日期范围导致数据对不上的问题。症状很统一明明界面显示“查询1月1日到1月31日”结果1月31日的数据稀稀拉拉或者压根不出现。问题不在业务逻辑也不在网络就在这一行不起眼的where create_time between 2024-01-01 and 2024-01-31上。BETWEEN AND是MySQL里最基础的区间查询语法看起来就是“在什么和什么之间”很多初学者用两天就觉得自己会了。但这个语法牵扯到的边界语义、字符串比较规则、NULL处理方式、索引命中情况每一个环节都有反直觉的地方。这篇就把这个语法彻底拆开讲透从日期时间的隐蔽坑到闭区间语义再到字符串和NULL的边界行为最后用EXPLAIN验证性能问题并给出业务开发里可以直接落地的写法规范。适合正在学SQL的初学者也适合写了好几年业务代码但没系统梳理过这个语法的老手。1. 日期范围查不准BETWEEN AND第一个也是最大的一个坑1.1 症状上界日期当天的数据去哪了先看一个典型的失败案例。订单表orders里的create_time是datetime类型有一批1月31日下单的记录时间分别是2024-01-31 09:15:00、2024-01-31 14:20:00、2024-01-31 23:59:59。现在要查一月份的订单很自然地写select * from orders where create_time between 2024-01-01 and 2024-01-31;执行结果是什么1月31日那几条记录一条都查不出来或者只能查出2024-01-31 00:00:00整点的记录。很多人的第一反应是数据出问题了但数据确确实实存在。问题出在BETWEEN AND的边界比较方式上。先把SQL改成等价的写法验证一下select * from orders where create_time 2024-01-01 and create_time 2024-01-31;结果还是查不出来。这就基本确认问题不是SQL写错而是MySQL对2024-01-31这个字符串的理解和我们不一样。1.2 病因MySQL把日期字符串补齐成了零点MySQL在拿datetime字段和字符串常量比较时会把字符串尝试转换成datetime类型。2024-01-31在转换时会被自动补全成2024-01-31 00:00:00。所以上面那条SQL真正执行的比较条件是where create_time 2024-01-01 00:00:00 and create_time 2024-01-31 00:00:00;也就是说上界不是“1月31日结束的最后一刻”而是“1月31日刚开始的那一刻”。凡是09:15:00、14:20:00这些在零点之后的时间全部被排除在外。这就是数据“凭空消失”的根本原因。这里要特别说明一下date类型字段不存在这个问题因为date本来就只有年月日没有时间部分。但datetime和timestamp都躲不开。现在很多表的主键或创建时间字段都习惯用datetime所以这个坑的覆盖面非常广。1.3 正确姿势日期范围查询用左闭右开上界应该写成“下个月的1号零点”而不是“这个月的最后一天”。推荐写法select * from orders where create_time 2024-01-01 00:00:00 and create_time 2024-02-01 00:00:00;等价地也可以写成BETWEEN AND的形式但要把上界补到第二个月1号select * from orders where create_time between 2024-01-01 00:00:00 and 2024-01-31 23:59:59;第二种写法看着直观但我建议业务代码里尽量别用23:59:59。原因很简单MySQL的datetime精度可以到微秒6位小数2024-01-31 23:59:59只能覆盖到秒级如果某条记录是2024-01-31 23:59:59.500它依然会被漏掉。服务端程序生成这个时间字符串时通常也只能到秒风险更大。三种写法的对比写法边界效果风险等级between 2024-01-01 and 2024-01-31上界当天00:00:00漏掉当天非零点数据高between 2024-01-01 and 2024-01-31 23:59:59上界当天23:59:59漏掉微秒级数据中 2024-01-01 and 2024-02-01上界下月1号零点完整覆盖1月全部时间低到这里我已经把“看起来没问题但结果不对”的排查链路说完了。下面从语法本身讲起把BETWEEN AND的边界语义彻底说清楚。2. 基本语法不复杂但闭区间语义很多人理解有偏差2.1 最小语法结构和官方定义BETWEEN AND的基础语法很简单expr between lower_bound and upper_boundMySQL官方文档对它的定义是一个闭区间判断等价于expr lower_bound and expr upper_bound也就是说x between 1 and 10会在x 1且x 10时返回真。注意这是一个“且”的关系两个条件必须同时成立而且两边的边界值本身是包含在内的。用一组最基础的SQL验证一下select 5 between 1 and 10; -- 1 select 1 between 1 and 10; -- 1 select 10 between 1 and 10; -- 1 select 0 between 1 and 10; -- 0 select 11 between 1 and 10; -- 0MySQL里的1代表真0代表假。边界值1和10都返回1说明闭区间语义确实包含两端。2.2 “低值比高值大”时的反直觉行为还有一类行为容易被忽略。当lower_bound大于upper_bound时MySQL不会报错而是返回假select 5 between 10 and 1; -- 0如果业务代码里动态拼接了SQL参数参数顺序传反了MySQL只是安静地返回空结果不会给任何报错。这种错误在复杂业务中排查起来相当费劲因为从日志看SQL语句是对的只是结果为空。实际项目里我见过这样的案例前端传了minPrice和maxPrice两个参数后端拼SQL时写成了price between maxPrice and minPrice导致价格区间查询永远查不到数据。这种问题在开发环境可能因为测试数据恰好撞上边界值没暴露上线后才翻车。所以对动态拼SQL的场景在代码里做好参数校验非常关键。2.3 为什么闭区间在实际开发中要格外谨慎闭区间最大的问题在于上界是需要精确表达的“最后一刻”。在数学上闭区间[1, 10]很清晰拿来就用。但落到数据库的时间字段上“最后一刻”是一个非常不精确的概念是23:59:59还是23:59:59.999999如果数据模型里时间精度不可控闭区间就会漏数据。我的习惯是业务SQL中涉及日期时间范围的查询一律改写成“大于等于下界小于上界”的半开区间写法。半开区间[start, end)的语义天然规避了“最后一刻”的问题上界直接写下一天或下一个月纯整数思维不需要考虑精度。虽然BETWEEN AND写起来更简洁语法上也是合法的但在日期时间这类高精度字段上简洁让位给正确性。3. 字符串和NULLBETWEEN AND的另外两个边界坑3.1 字符串数字比较10居然在1和9之间先放一个测试结果很多人第一次看到都会愣一下select 10 between 1 and 9; -- 110按直觉应该不在1到9之间为什么MySQL返回真因为字符串比较是逐字符比较的字典序。比较10和9时先比较第一个字符1和91比9小所以10整体小于9。同样地10的第一个字符1不小于1于是10 between 1 and 9成立。类似地select 100 between 1 and 9; -- 1 select 200 between 1 and 9; -- 0因为2大于9吗不因为29比较时按字符集排序规则这里必须严谨一点在utf8mb4默认排序规则下字符2的排序值小于9所以200还是会落在1和9之间。这类比较结果和字符集、排序规则有关不同规则下可能不一样。但只要记住一个核心原则如果字段里存的是数字字段类型就该用数值类型int、decimal等别图省事用varchar存。用字符串类型存数字不仅BETWEEN AND有这个问题ORDER BY排序也会出现10排在9前面的经典乱象。如果历史遗留的表里已经有了这种字符串数字字段查询时用CAST转换后再比较select * from t where cast(price as decimal(10, 2)) between 100 and 200;CAST写在字段上会导致这一列的索引失效在数据量大的表上要谨慎。但从正确性角度说先保证结果对再谈性能优化。3.2 排序规则对字符串边界的影响字符串比较不是简单的“ASCII码比大小”而是受排序规则控制的。MySQL常用排序规则有utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_bin等ci结尾不区分大小写bin结尾按二进制比较。不区分大小写规则下select B between a and c; -- 1在utf8mb4_general_ci下B和b被视为等价的所以落到了a和c之间。如果业务里对大小写敏感查询时必须确认字段的排序规则。否则筛选范围可能比预期大。还有一种情况容易忽略字符串末尾空格。MySQL默认的PAD SPACE比较规则会忽略字符串尾部的空格所以select a between a and z; -- 1a 虽然带一个空格但比较时尾部空格被忽略a 被当作a处理。这在查询用户输入或外部系统导入的数据时特别容易造成“查出来但不该有”的脏数据。3.3 NULLBETWEEN AND遇上空值的三种表现NULL在SQL里的逻辑判断结果不是真也不是假而是“未知”。BETWEEN AND遇到NULL时同样遵循这个规则。第一种情况表达式本身是NULLselect null between 1 and 10; -- NULL select 5 between null and 10; -- NULL select 5 between 1 and null; -- NULL只要expr、lower_bound、upper_bound三者中有任何一个为NULL整个BETWEEN表达式结果就是NULL。在WHERE条件里NULL被当作假处理行会被过滤掉。第二种情况更隐蔽NOT BETWEEN。很多开发者以为NOT BETWEEN就是“不在这两者之间的都返回”但NULL参与时结果依然是NULLselect null not between 1 and 10; -- NULL这一行的结果既不是真也不是假所以WHERE create_time NOT BETWEEN ...同样不会返回NULL时间的记录。如果一个表里存在大量NULL时间字段想查“不在某范围内”的数据光靠NOT BETWEEN是查不全的必须单独处理NULLselect * from orders where create_time not between 2024-01-01 and 2024-02-01 or create_time is null;第三种情况是统计结果偏差。用COUNT(*)时NULL行会计入但如果用SUM或AVG配合条件统计NULL造成的影响要仔细核对。比如统计区间外的订单数直接写COUNT(*)没问题但如果写SUM(CASE WHEN create_time NOT BETWEEN ... THEN 1 ELSE 0 END)NULL时间记录就被默认归入ELSE分支统计口径直接错了。4. 性能验证BETWEEN AND能不能走索引关键看怎么用4.1 正常情况走的是range范围扫描BETWEEN AND在优化器眼里就是两个边界条件正常情况下可以走索引范围扫描。建一张测试表并插入数据create table test_between ( id int primary key auto_increment, create_time datetime not null, price decimal(10, 2), key idx_create_time (create_time) ); insert into test_between (create_time, price) values (2024-01-15 10:00:00, 120.00), (2024-02-10 12:00:00, 50.00), (2024-03-05 09:30:00, 300.00);执行计划验证explain select * from test_between where create_time between 2024-01-01 and 2024-02-01\G结果里type是rangekey是idx_create_time说明索引被用上了。对查询优化器来说BETWEEN AND和 没有本质区别性能表现一致。4.2 让索引失效的三种常见写法第一种对索引字段做函数处理explain select * from test_between where date(create_time) between 2024-01-01 and 2024-02-01\Gdate()函数包住了字段导致索引失效执行计划直接变成typeALL全表扫描。数据量一大这种写法立刻拖垮查询。正确做法是保持字段原样在参数值上做文章也就是第1节讲到的半开区间写法where create_time 2024-01-01 and create_time 2024-02-01第二种隐式类型转换。比如字段是varchar类型存数字在条件里和数值比较explain select * from t where phone between 13800000000 and 13899999999\GMySQL会把varchar字段隐式转换为数值类型再比较转换作用于字段本身索引同样失效。这又回到了第3节的结论类型匹配很重要字符串字段就按字符串比较数值字段就按数值比较混用等于主动放弃索引。第三种复合索引中范围条件位于中间位置。假设索引是idx_user_time(user_id, create_time)查询条件写成where user_id 1001 and create_time between 2024-01-01 and 2024-02-01user_id等值条件可以用到索引前缀create_time的范围条件也能帮上忙但如果create_time之后还有其它排序或过滤列那些列就无法再继续使用索引了。这是范围查询本身的特点不算写法错误但要清楚它的局限性。4.3 哪些字段上适合用BETWEEN AND字段类型是否适合BETWEEN AND说明自增ID非常适合支持范围分页索引天然有序时间日期适合注意边界和索引失效问题数值金额适合业务语义明确时为闭区间varchar字符串谨慎字典序不等于数值序blob/text不适合无法建立普通索引范围查询经验之谈对ID做范围分页时BETWEEN AND配合主键索引非常高效比LIMIT 100000, 20这种大偏移量翻页要稳定得多。这也是面试里常说的“基于游标的分页”思路。如果业务场景是后台列表按ID倒序翻页把上一页最小ID作为下界用id BETWEEN ? AND ?或id ?直接定位速度会非常快。5. 业务实战统计报表、金额区间与存储过程中的正确写法5.1 按天分组统计时别把函数套在字段上统计每天订单量新手最容易写成select date(create_time) as d, count(*) from orders where date(create_time) between 2024-01-01 and 2024-01-31 group by date(create_time);这个写法结果没问题但性能堪忧因为date(create_time)让索引失效数据量上来以后报表接口会越来越慢。正确做法是把条件改成字段直接比较函数留在SELECT和GROUP BY里用select date(create_time) as d, count(*) from orders where create_time 2024-01-01 and create_time 2024-02-01 group by date(create_time);同样是按天统计这种写法让create_time上的索引能够真正发挥作用。SELECT和GROUP BY子句里的date()函数不会阻止索引扫描因为索引判断发生在条件过滤阶段。5.2 查询连续日期序列时配合日期表报表场景里经常要求“没有订单的日期也要显示0”。如果只用分组统计缺失日期直接不出现。这就要生成一个连续的日期序列再左关联统计结果。用递归CTE生成某个月的日期序列with recursive date_range as ( select 2024-01-01 as d union all select date_add(d, interval 1 day) from date_range where d 2024-01-31 ) select d.d, ifnull(t.cnt, 0) as cnt from date_range d left join ( select date(create_time) as day, count(*) as cnt from orders where create_time 2024-01-01 and create_time 2024-02-01 group by date(create_time) ) t on d.d t.day;这个写法的关键还是那个半开区间先把订单统计的WHERE条件限定在1月再做关联。如果直接用BETWEEN AND并把上界写成2024-01-31又会漏掉1月31日白天的订单。5.3 金额区间的闭区间语义要不要保留相比时间字段金额字段的闭区间语义通常更清晰。比如“查询单价在100到200元之间的商品”业务上的确包含100元和200元写成select * from products where price between 100 and 200;没问题。但要注意业务规则里的“边界”是不是真的闭区间。我见过一个营销系统的例子规则是“满100减20满200减50”但运营提需求时说“查一下消费金额在100到200之间的用户”如果写BETWEEN AND恰好消费200元的人会同时进入“满100减20”的区间统计口径重复。这种场景下正确写法是price 100 and price 200把同一个边界值明确归属到其中一个区间。金额字段还有一个细节浮点误差。如果字段是float或double0.1 0.2这类计算可能出现精度问题导致边界值判断不准确。业务上涉及金额字段类型应该用decimal别用浮点。如果你在维护老表且字段类型改不了查询时可以适当用round或调整边界值留出误差空间。5.4 存储过程中动态区间拼接存储过程里经常用PREPARE配合变量拼SQL。这里有个常见问题变量本身类型不明确时MySQL可能推断错类型。比如set start 2024-01-01; set end 2024-02-01; prepare stmt from select * from orders where create_time between ? and ?; execute stmt using start, end;start和end都是用户变量类型推断通常没问题。但如果是字符串拼接出来的SQL没加引号或引号位置不对时间字符串被当成数值比较结果就会离奇。我的建议是动态SQL一定要先SELECT生成好的完整SQL打印到日志里肉眼确认字符串格式没问题再执行。排查慢查询和诡异结果时这步能省下大量时间。存储过程内部也可以强制转换参数类型后配合半开区间写法create procedure get_month_orders(in p_month date) begin select * from orders where create_time p_month and create_time date_add(p_month, interval 1 month); end;用这个存储过程查1月订单时传入2024-01-01内部自动把它扩展成[2024-01-01, 2024-02-01)月份边界永远正确不用在调用方处理“最后一天”的逻辑。6. 容易混淆的几个问题和我的最终建议6.1 BETWEEN AND与IN到底怎么选IN适合离散值集合select * from users where id in (1, 3, 7, 20);BETWEEN AND适合连续区间select * from users where id between 1 and 20;两者用在连续整数区间时结果不同BETWEEN 1 AND 20包含从1到20的全部19个整数实际是20个数1到20口误修正为20个IN (1,3,7,20)只包含那4个值。选哪个取决于业务到底是连续区间还是离散点集。把BETWEEN AND当IN用数据量小无所谓数据量大会扫描过多的行拖慢查询。6.2 NOT BETWEEN的语义细节NOT BETWEEN等价于expr lower OR expr upper注意是OR不是AND。配合NULL时问题更多。一个典型的坑是查某个时间段之外的订单直接写create_time not between 2024-01-01 and 2024-02-01NULL时间记录不会出现在结果里。如果业务要求“时间缺失也算范围外”必须加上or create_time is null。6.3 对BETWEEN AND的整体评价BETWEEN AND不是不能用的语法它简洁语义清晰在数值和日期区间上确实好用。问题在于必须清楚它什么时候安全什么时候有坑。就我个人这些年的实践最终沉淀出的规范是这么几条日期时间字段一律用和的半开区间不用BETWEEN AND写“最后一天”数值和金额字段可以用BETWEEN AND但必须确认业务边界确实包含两端字符串字段尽量别用BETWEEN AND尤其是存数字的字符串所有涉及范围条件的查询上线前用EXPLAIN看一遍执行计划确认索引真的走了代码评审时重点检查所有写23:59:59的地方这是高频出错点。最后分享一个我常用的自查技巧任何范围查询先在脑子里过一遍“边界值测试三连”——取一个刚好等于下界的值、一个刚好等于上界的值、一个上界之后的值分别确认是否应该出现在结果集里。拿这三个值各写一条SELECT验证一下比调试半天线上问题效率高得多。数据库的东西很多坑都是使用者用出来的边界想清楚了BETWEEN AND其实是个很趁手的工具。