在MySQL里做范围查询BETWEEN AND绝对是我日常写SQL时用得最多的写法之一。无论是统计某个时间段内的订单、筛选价格区间的商品还是给报表做日期分段一句BETWEEN ? AND ?总能比一串 和 来得直白省事。可就是这个人人都觉得简单的运算符在实际业务里翻车的次数远比想象中多同事跑月度订单汇总查完发现莫名少了几条当天的数据新手写日期查询以为把起始日期两端填上就万事大吉结果第二天凌晨零零散散的记录全被漏掉还有人明明给字段建了索引一条BETWEEN却走了全表扫描性能直接崩掉。这篇文章我想把BETWEEN AND完整地梳理一遍从最基础的语法规则和边界语义到日期时间查询里最容易踩的三个坑再到组合条件、索引优化和实战重写。适合刚入门的同学快速掌握用法也适合写过一段时间SQL但被边界问题坑过的老手对照排查。内容不绕弯子全是实际可验证的东西。1. BETWEEN AND 的语法本质闭区间、等价值写法与三种数据类型实测1.1 基本语法与闭区间语义BETWEEN AND的官方语法是这样的expr BETWEEN min_value AND max_value它表达的含义等价于两条比较条件用AND连接expr min_value AND expr max_value关键就一句话这是一个闭区间两个端点都包含在内。很多新手第一次用的时候会凭直觉以为是开区间觉得BETWEEN 100 AND 500应该排除掉 100 和 500 这两个点。实际上完全不是MySQL 会把等于端点的行一并查出来。看一个例子SELECT id, price FROM products WHERE price BETWEEN 100 AND 500;只要price是 100、150、499、500这四行都会返回。如果你想排除端点BETWEEN AND就帮不上忙了得老老实实写SELECT id, price FROM products WHERE price 100 AND price 500;这个细节在写统计报表时特别容易造成数据对不上。比如筛选价格区间为「100到500元」的商品业务上可能想让 500 元整的商品也计入也可能想让 100 元整的计入而 500 元整的不计入不同业务口径下结果完全不同。用BETWEEN AND时你得清楚自己选的是哪种口径。1.2 BETWEEN AND 与 AND 的完全等价性既然expr BETWEEN a AND b完全等价于expr a AND expr b那为什么还要用BETWEEN AND我的体会是可读性和表达意图。人眼扫到BETWEEN 2024-01-01 AND 2024-01-31一眼就知道这是一个范围筛选扫到 2024-01-01 AND 2024-01-31需要稍微反应一下。对于维护老代码的人来说BETWEEN AND能省掉不少阅读理解成本。而且它把范围边界集中在一条语句里看起来也更紧凑。另外要注意BETWEEN AND的优先级比AND低吗这里容易混淆。实际上BETWEEN是一个独立的运算符它在表达式里的组合方式需要你自己用括号控制。举一个常见场景-- 想查分类为3且价格在100到200之间的商品 SELECT * FROM products WHERE category_id 3 AND price BETWEEN 100 AND 200;这样写没问题AND把两个条件并列了。但如果你再加条件就容易写出歧义-- 这条语句的意图是谁查category3价格在100到200之间并且status1 -- 还是 category3 且价格在100到200或者 status1 SELECT * FROM products WHERE category_id 3 AND price BETWEEN 100 AND 200 OR status 1;由于AND优先级高于OR上面的语句实际含义是(category_id 3 AND price BETWEEN 100 AND 200) OR status 1。如果这不是你想要的必须加括号。这也是后面第3节要重点展开的坑。1.3 数字、字符串、日期三种数据类型的边界表现BETWEEN AND虽然语法相同但底层比较规则完全不同分开说。数字类型是最不容易出问题的。price BETWEEN 100 AND 500就是纯数值比较闭区间100 和 500 都包含。需要注意的只有浮点精度问题。比如SELECT * FROM product_price WHERE price BETWEEN 10.1 AND 10.3;如果price是FLOAT或DOUBLE10.1 和 10.3 在二进制里都只是近似值边界判断可能跟你预期的不完全一致。建议价格、金额这类字段一律用DECIMAL或者把边界值写成10.10、10.30这种格式避免隐式转换带来误差。字符串类型的比较就不是直觉上的“长度”或“数值”了而是按排序规则逐字符比较。默认的utf8mb4_general_ci排序规则下大小写不敏感排序按照字符集编码顺序来。举个例子SELECT name FROM users WHERE name BETWEEN A AND D;这条语句会把Alice、apple、Cathy查出来但banana不一定出现——因为如果数据库排序规则是区分大小写的utf8mb4_binb的编码值大于Dbanana就被排除在外。所以对字符串用BETWEEN AND时一定要先确认列采用的排序规则否则看起来是「A到D的字母范围」实际结果可能跟你想象的天差地别。日期类型是最常用的也是最容易出问题的。一行简单的SELECT * FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31;看起来没毛病实际上隐藏着一个大坑create_time如果是DATETIME类型2024-01-31会被 MySQL 自动转换成2024-01-31 00:00:00也就是 1 月 31 日零点整。这意味着 1 月 31 日当天 00:00:00 之后产生的所有订单全部不会被这条SQL查出来这个坑我在第2节里专门展开讲。2. 日期时间查询里最容易翻车的三个边界细节2.1 当列是 datetime 而边界值只写日期时先看一个具体的翻车现场。表结构简化如下CREATE TABLE orders ( id INT PRIMARY KEY, amount DECIMAL(10,2), create_time DATETIME, KEY idx_create_time (create_time) );你执行这条查询想统计 1 月 1 日到 1 月 31 日的订单SELECT COUNT(*) FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31;结果发现 31 日当天的订单数量非常少甚至为 0。根本原因就是刚才说的2024-01-31被当作2024-01-31 00:00:00所以查询范围实际是create_time 2024-01-01 00:00:00 AND create_time 2024-01-31 00:00:0031 日 0 点之后产生的任何订单都不在范围内哪怕业务上这些订单确实发生在 1 月。这是BETWEEN AND在日期查询中最经典的坑没有之一。排查方法也很简单先单独查一下那天到底有没有数据。SELECT COUNT(*) FROM orders WHERE create_time 2024-01-31 00:00:00 AND create_time 2024-02-01 00:00:00;有数据、但前面BETWEEN查不出来基本就可以确认是边界转换问题。2.2 跨月跨年查询的惯用套路右开区间解决上面那个坑最稳妥的写法不是去把BETWEEN的右端点补成2024-01-31 23:59:59而是干脆改用右开区间SELECT COUNT(*) FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-02-01 00:00:00;为什么说右开区间更稳因为下一个月的一号零点永远比本月最后一天的 23:59:59 好算你不需要去记每个月的最后一天是几号也不需要担心闰年、2月天数这种边缘情况。写2024-02-01它自然包含了 1 月 31 日 23:59:59 及以前的所有时间点。有人问我那BETWEEN 2024-01-01 00:00:00 AND 2024-01-31 23:59:59行不行行是行但有隐患如果某些记录的create_time带有毫秒精度23:59:59只能覆盖到秒23:59:59.500这类记录会被漏掉。虽然 MySQL 的DATETIME默认不带小数秒但你无法保证未来业务不会改表结构或者插入带毫秒的数据。所以跨日期范围统计我个人的标准写法永远是右开区间。如果非要用BETWEEN AND查完整月份可以写成SELECT COUNT(*) FROM orders WHERE create_time BETWEEN 2024-01-01 00:00:00 AND 2024-01-31 23:59:59.999;但这仍然依赖于精确到毫秒的边界值不如右开区间干净。2.3 用 BETWEEN 查“某一天”的正确姿势有时候需求很简单只查某一天的订单。新手最常见的写法是SELECT * FROM orders WHERE DATE(create_time) 2024-01-15;或者SELECT * FROM orders WHERE DATE(create_time) BETWEEN 2024-01-15 AND 2024-01-15;这两种写法都能查出来但它们有一个致命问题对create_time列调用了DATE()函数导致该列的索引完全失效。数据量小的时候无所谓数据量到了百万级这条查询就是全表扫描慢得你怀疑人生。正确写法有两种。第一种SELECT * FROM orders WHERE create_time 2024-01-15 00:00:00 AND create_time 2024-01-16 00:00:00;第二种如果你非要维护BETWEEN风格SELECT * FROM orders WHERE create_time BETWEEN 2024-01-15 00:00:00 AND 2024-01-15 23:59:59;但第二种如上所说有毫秒隐患我还是推荐第一种。用和的组合既保证索引能被使用又保证边界完整覆盖。3. NOT BETWEEN、NULL 与多条件组合括号往往决定结果对不对3.1 NOT BETWEEN AND 的语义与 NULL 的特殊处理NOT BETWEEN AND的语义很直观就是「不在这个范围内」等价于expr min_value OR expr max_value注意这里我用的是OR而不是AND因为一个值不可能同时小于下限又大于上限所以两边是或的关系。NULL 在这里有个特殊行为。如果被判断的列值为NULL无论 BETWEEN 还是 NOT BETWEEN结果都是NULL而WHERE子句只会保留条件为TRUE的行所以 NULL 行会被一律过滤掉。看个例子SELECT id, price FROM products WHERE price NOT BETWEEN 100 AND 200;假设有一行price NULL它既不会被NOT BETWEEN查出来也不会被BETWEEN查出来。如果你想单独统计空值得额外加条件SELECT id, price FROM products WHERE price IS NULL OR price NOT BETWEEN 100 AND 200;这一点在处理可空字段时特别重要很多统计对不上账就是因为空值被无声无息地排除了。3.2 多条件组合时括号优先级的实战意义我之前见过一段业务代码本意是查「分类为 3 且价格在 100 到 200 之间」的促销商品SQL 写成这样SELECT * FROM products WHERE category_id 3 AND price BETWEEN 100 AND 200 OR status 1;实际跑出来惨不忍睹因为AND优先级高于OR条件被解析成(category_id 3 AND price BETWEEN 100 AND 200) OR status 1;也就是说所有status 1的商品全都进来了跟「分类为3」没关系。正确写法必须把范围条件整体括起来SELECT * FROM products WHERE category_id 3 AND (price BETWEEN 100 AND 200 OR status 1);如果需求真是「分类为3且价格在范围内或者是促销状态」那括号位置就得像我这样放。多条件组合时优先级是逻辑问题不是风格问题写错了就是数据错误。3.3 与 IN、LIKE 混用时的注意事项BETWEEN AND经常跟IN、LIKE一起出现组合时也有几类典型问题。第一种是IN和BETWEEN混用表达复杂范围SELECT * FROM orders WHERE status IN (paid, shipped) AND amount BETWEEN 500 AND 5000;这里是两个独立条件的自然组合没问题。需要小心的是如果你想表达「状态为 paid 且金额在一个范围或者状态为 shipped 且金额在另一个范围」必须显式加括号SELECT * FROM orders WHERE (status paid AND amount BETWEEN 500 AND 1000) OR (status shipped AND amount BETWEEN 2000 AND 5000);第二种是LIKE和BETWEEN混用比如模糊匹配商品名后又按价格过滤SELECT * FROM products WHERE name LIKE iPhone% AND price BETWEEN 3000 AND 8000;这种组合通常不会出问题但要注意如果name的模糊前缀匹配条件本身可以走索引那price BETWEEN也能继续利用索引如果用了%iPhone%这种左右都带百分号的写法索引大概率失效整条SQL性能就会大打折扣。第三种容易踩的坑是多个 BETWEEN 条件用 OR 连接。比如SELECT * FROM products WHERE price BETWEEN 100 AND 200 OR price BETWEEN 500 AND 600;MySQL 优化器可能无法把两个范围合并成一个高效的索引区间查询性能会变得很差。这种情况下我更推荐用UNION ALL拆成两条独立查询或者在条件设计阶段就避免这种分段写法比如直接合并成一个大的范围再在应用层过滤。4. BETWEEN AND 与索引优化一条慢查询背后的执行计划真相4.1 索引判断EXPLAIN 里到底能不能走 range很多人担心BETWEEN AND用了区间判断是不是就没办法走索引其实不是。BETWEEN AND在 MySQL 优化器里会被转化成标准的范围扫描。拿前面的orders表举例EXPLAIN SELECT id, amount FROM orders WHERE create_time BETWEEN 2024-01-01 00:00:00 AND 2024-01-31 23:59:59\G如果idx_create_time索引存在执行计划里type字段显示的应该是range而不是ALL全表扫描key字段是idx_create_time。typerange虽然比const、ref级别低但比ALL和index好得多意味着 MySQL 会沿着索引树定位到范围的起点然后一路向后扫描直到终点不需要遍历整张表。所以在索引字段上使用BETWEEN AND本身是没问题的优化器是支持的。问题往往出在后面这些使用方式上。4.2 BETWEEN 写太宽照样慢区分“能用索引”和“用得好”哪怕走了range访问如果范围过大依然会扫描大量索引项每条索引项还要回表取一次数据这在千万级表上是灾难。举个例子一张订单表有 5000 万行create_time上有索引。你要统计过去一整年的订单量SELECT COUNT(*) FROM orders WHERE create_time BETWEEN 2023-01-01 AND 2023-12-31;MySQL 确实会走索引但它需要扫描一年的索引范围——哪怕最终聚合结果只有几行它也要把这一年里所有索引项位置都遍历一遍。这种情况优化空间在哪里如果数据库是分区表按create_time按月分区那么查询会被分区裁剪只扫描涉及的 12 个分区性能提升巨大。如果只是普通表可以按业务需求把时间范围尽量收窄。比如报表场景按周跑、按月跑而不是一次查一年。如果全表数据确实需要扫那么至少要保证查询是覆盖索引避免回表。覆盖索引值得多说一句。假设你有这样的查询SELECT id, amount FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31;如果索引只建在create_time一个字段上那索引里只有create_time和主键id要取amount必须回表。你可以改建成联合索引ALTER TABLE orders ADD INDEX idx_create_time_amount (create_time, amount);这样amount也被包含在索引里查询就不需要回表了。EXPLAIN的Extra字段里会出现Using index性能会好很多。这个优化在日常报表SQL里收益非常明显。4.3 函数包裹索引列导致索引失效这是性能问题里最常见的一种WHERE DATE(create_time) BETWEEN ...或WHERE YEAR(create_time) 2024这种写法看起来也是在“范围查询”实际上因为对索引列做了函数处理MySQL 无法直接使用索引树进行范围定位只能全表扫描。解决办法前面已经说过把函数去掉改成原始列与边界值比较。比如-- 错误示范函数包裹索引列 SELECT COUNT(*) FROM orders WHERE DATE(create_time) BETWEEN 2024-01-01 AND 2024-01-31; -- 正确写法保留原始列比较 SELECT COUNT(*) FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-02-01 00:00:00;还有一种隐式转换同样会导致索引失效。比如某字段是VARCHAR存的是数字字符串你用BETWEEN 100 AND 500去比较MySQL 会把字符串列隐式转换为数值后再比较导致无法使用索引。这个在用户表、订单表里经常出现比如order_no存成字符串但里面全是数字。避免方法就是保持字段类型和查询参数类型一致。5. 实战重写排查一条丢失数据的月度统计 SQL5.1 原始需求与第一条 SQL讲一个真实的排查过程。业务方需要一个「2024年1月每日订单金额统计」用于月末对账。当时同事写的第一版SQL是这样的SELECT DATE(create_time) AS day, SUM(amount) AS total_amount FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31 GROUP BY DATE(create_time) ORDER BY day;跑出来的结果中1月31日的汇总金额明显异常比其他天少了一大截。业务方反馈31日也有不少订单为什么统计不到5.2 从现象反推根因边界转换 索引失效我拿到这条SQL第一反应就是边界问题。前面讲过create_time是DATETIME类型2024-01-31会被转成2024-01-31 00:00:00所以范围实际上是到 31 日零点整为止。31日白天产生的订单全部被漏掉了。但这还没完另一个隐患是DATE(create_time)用在了SELECT、GROUP BY两个地方。SELECT里用函数还好GROUP BY DATE(create_time)意味着MySQL要做一次基于函数的排序分组无法直接利用索引还会产生临时表。为了验证我直接跑了两个排查SQL第一步确认当日数据量SELECT COUNT(*) FROM orders WHERE create_time 2024-01-31 00:00:00 AND create_time 2024-02-01 00:00:00;结果确实有一批数据。第二步看执行计划EXPLAIN SELECT DATE(create_time) AS day, SUM(amount) FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31 GROUP BY DATE(create_time)\Gtype ALLExtra里出现了Using temporary; Using filesort。这就是典型的既边界错误又性能低效的写法。5.3 重写方案与效果对比重写的目标有两个一是把时间范围改成完整的1月含31日整天二是去掉DATE()函数对分组的影响。新的SQL写成这样SELECT DATE(create_time) AS day, SUM(amount) AS total_amount FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-02-01 00:00:00 GROUP BY DATE(create_time) ORDER BY day;如果orders表数据量大到千万级GROUP BY DATE(create_time)依然会拖慢速度。更好的方式是把日期截断放到派生表里或者提前生成一张日期维度表不过那是另一个话题了。就这个例子来说重写后EXPLAIN里type变成rangeExtra里的Using temporary依然存在因为DATE()不是分组键本身但如果把查询改成按小时或按天分桶配合索引覆盖性能已经比原来好很多。补充一个更彻底的重写思路。如果报表需要精确按天汇总可以在建表时额外增加一个order_date DATE字段专门存create_time的日期部分然后给order_date建索引查询直接SELECT order_date, SUM(amount) FROM orders WHERE order_date BETWEEN 2024-01-01 AND 2024-01-31 GROUP BY order_date;这样BETWEEN AND走的是order_date本身没有函数包裹索引使用完全没问题。缺点是写入时要多维护一个字段适合查询量远大于写入量的报表场景。6. 使用 BETWEEN AND 的几条个人经验最后说几条我在实际项目里沉淀下来的习惯不算什么高深理论但确实帮我躲过不少雷。第一日期范围查询默认用右开区间。管它是不是月初月末一律写成 start AND next_start。能不用BETWEEN AND查日期就不用了除非你能百分之百确认边界值里包含了当天的所有时刻。这个习惯让我少了很多对账的烦恼。第二字符串范围慎用。除非你完全清楚列的排序规则否则不要轻易对字符串列使用BETWEEN AND。昨天还正常的查询可能因为有人改了表的collation结果就悄悄变了。更稳妥的方案是用LIKE配合前缀匹配或者在应用层做范围过滤。第三BETWEEN 的两个端点尽量用参数化方式传入避免手拼SQL。比如用框架的预编译语句传start_time和end_time这样不仅安全还能让 MySQL 的查询缓存对相同模板的SQL生效降低硬解析开销。顺手提一句很多公司线上会关闭查询缓存但参数化依然能帮你在慢日志里快速定位同类问题。第四数值型的范围查询优先考虑用 DECIMAL 类型存储边界字段。浮点数的二进制表示不精确边界判断常常差之毫厘。这里虽然没有让BETWEEN AND本身出错的明确场景但等到你哪天在金额统计上发现尾差就会回来感谢这条。第五BETWEEN AND与索引的关系可以简单记为列本身干净函数不包裹类型不隐式转换就能用上索引能用上索引但范围为王范围太大照样慢。当你发现一条BETWEEN查询变慢时先去EXPLAIN看type和Extra再回头看是不是范围太宽或者建了覆盖索引但没有命中。MySQL 里功能越简单的运算符用起来越容易掉以轻心。BETWEEN AND学起来五分钟但把边界、类型、索引这些细节吃透才真正算得上会用。希望这篇分享能帮你在下次写范围查询的时候少踩几个我已经替你踩过的坑。