MySQL BETWEEN AND 范围查询：边界陷阱与索引优化实战-北京尧图网络

做后台开发和报表统计这些年MySQL里的范围查询我几乎每天都在写而BETWEEN AND又是大家最先上手、也最容易踩坑的一个语法。它的写法非常简单就是where 某列 between 最小值 and 最大值看起来就是把“中间这一段”捞出来但真到业务里落地边界条件、日期格式、索引失效、NULL 值处理、隐式类型转换每一个细节都可能让查询结果悄悄变错。这篇内容我会把 MySQL 中BETWEEN AND从基本语法到边界规则、不同数据类型的差异、常见组合写法、性能优化再到排查经验完整过一遍适合刚入门 MySQL 的开发者也适合写了好几年 SQL 但偶尔被范围查询坑过的同学。1. BETWEEN AND 的基础用法与边界规则1.1 语法长什么样先从最常用的写法说起BETWEEN AND的标准语法是SELECT 列1, 列2, ... FROM 表名 WHERE 列名 BETWEEN 下界值 AND 上界值;它要表达的意思是某个字段的值大于等于下界并且小于等于上界。比如我要查products表里价格在 100 到 500 之间的商品SELECT id, title, price FROM products WHERE price BETWEEN 100 AND 500;这条语句等价于SELECT id, title, price FROM products WHERE price 100 AND price 500;实际工作中很多人会把BETWEEN AND当成“区间缩写”来用觉得它只是省了几个字符。确实从优化器角度看MySQL 最终会把BETWEEN AND解析成对应的范围条件执行计划本质上和写 / 没有区别。但这不代表你可以随便写因为BETWEEN AND有一种“看起来很直白、边界却又容易搞错”的特性接下来要重点讲。1.2 边界包含两个端点都会被查进来BETWEEN AND在 MySQL 里是闭区间也就是下界和上界本身都包含在结果集里。举个例子SELECT id FROM orders WHERE amount BETWEEN 200 AND 300;查询结果会包含amount 200的行也会包含amount 300的行。这个特性看起来很简单但实际开发中经常有人把它当成“开区间”理解然后出现丢数据的现象。尤其是日期时间字段后文我会专门展开讲。另外有一个容易忽略的点如果下界大于上界MySQL 不会报错而是直接返回空结果。-- 不会报错但查不到任何数据 SELECT id FROM orders WHERE amount BETWEEN 300 AND 200;这种写法常见于调用方传参时没做大小校验。某个后台管理系统里用户把“最低价”和“最高价”填反了接口直接把两个参数拼进 SQL结果页面显示“该条件下没有商品”用户还以为真是没货。应用层最好提前校验如果下界大于上界要么交换两个值要么明确提示用户。1.3 与 / 的等价转换什么时候别用 BETWEEN既然BETWEEN AND等同于 AND 那我是不是什么场景都可以用不完全是。下面给张对照表写法含义等价表达式col BETWEEN a AND bcol a且col bcol a AND col bcol NOT BETWEEN a AND bcol a或者col bcol a OR col bcol BETWEEN b AND aa b空结果集恒假条件这里要注意一个很实用的点如果你需要“大于等于下界但是严格小于上界”那就不适合用BETWEEN AND你得写成col a AND col b。这种“左闭右开”区间在日期统计里尤其重要具体原因等一下说。2. 不同数据类型下范围查询的细节差异2.1 日期时间最容易被边界“吃掉”数据的地方日期时间类型的范围查询是BETWEEN AND翻车率最高的场景没有之一。先用一个典型的错误举例。假设订单表orders的创建时间是create_time DATETIME我想统计 2024 年 1 月整月的订单量新手往往这么写SELECT COUNT(*) FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31;这个查询在 DATE 类型的字段上不会出问题但在 DATETIME 字段上会丢数据。原因是MySQL 会把边界字符串自动转换成DATETIME值2024-01-31转换后是2024-01-31 00:00:00。而BETWEEN是闭区间只包含了 1 月 31 日零点整那一秒1 月 31 日 00:00:01 之后的所有订单全部被排除在外。我见过不少统计报表因此少算一天的数据排查半天才发现是边界写错了。正确的做法是“左闭右开”区间SELECT COUNT(*) FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-02-01 00:00:00;也有人会尝试把上界写成2024-01-31 23:59:59这在业务不敏感时看起来还行但一旦字段精度提高到毫秒、微秒甚至只是并发写入比较密集临界点的数据还是会漏。因为23:59:59小于23:59:59.500后者不会被包含。所以我的建议是涉及日期时间范围统一使用下界 AND 下一天/下一月的写法不要跟23:59:59较劲。这个习惯在 MySQL 8.0 的 DATETIME(6) 精度下尤其重要。2.2 字符串字典序不等于你以为的字母顺序BETWEEN AND也能用在字符串类型上但结果经常让人意外。字符串比较在 MySQL 里是按排序规则进行的本质上类似于字典序而不是你想当然的“字母表顺序”或者“拼音顺序”。举个例子SELECT * FROM students WHERE name BETWEEN a AND z;这个查询会取出所有 name 以 a 到 z 开头的记录吗会但又不全对。它按照排序规则逐个字符比较。apple肯定在a和z之间banana也在但zoo就不在因为zoo比z大。也就是说BETWEEN a AND z并不能保证“首字母从 a 到 z 的所有字符串都在”它只是一个字典序区间。字符串范围查询还有一个更深的坑中文。MySQL 里中文字符串按 Unicode 编码排序不是按拼音。你想查“姓张到姓李”的学生用BETWEEN 张 AND 李完全不可靠因为这里比较的是字符编码和拼音顺序没关系。所以涉及中文前缀范围最好用LIKE 张%或者明确结合具体排序规则单独设计过滤逻辑。如果你确实想按字符串前缀做范围可以借用“左闭右开”思路。比如查订单号order_no以2024开头的记录可以写成SELECT * FROM orders WHERE order_no 2024 AND order_no 2025;利用字典序让所有2024...开头的字符串都落在[2024, 2025)这个区间里。这个技巧在按年月字符串前缀查数据时非常实用。2.3 数值类型精度和隐式转换是两座大山数值型的范围查询相对安全但也分类型。INT、BIGINT、DECIMAL都是精确的直接比较没有歧义。真正容易出问题的是FLOAT和DOUBLE。FLOAT在 MySQL 里是近似存储比如你插入19.9实际存的可能是19.8999996。如果查询写SELECT * FROM sku WHERE price BETWEEN 19.89 AND 19.91;你预期能查到那条19.9的记录实际上大概率也能查到因为近似值还在区间内。但如果你把边界卡得特别紧比如BETWEEN 19.8999995 AND 19.8999997就会产生“肉眼看着是这条数据但 SQL 就是查不出来”的诡异现象。对于涉及金额、价格、库存数量这类对精度有要求的字段我强烈建议使用DECIMAL而不是FLOAT/DOUBLE。DECIMAL是定点数按十进制存储范围查询行为完全符合直觉。还有一类坑是隐式类型转换。假如order_no字段是VARCHAR里面存着100001、100002之类的字符串然后你写了SELECT * FROM orders WHERE order_no BETWEEN 100001 AND 100005;MySQL 发现边界值是数字而字段是字符串就会把字段值转换成数字再比较。绝大多数情况下结果看起来正常但有两个隐患一是字段上的索引可能失效变成全表扫描二是如果表里有A10001、0abc这类无法完整转成数字的脏数据转换时会变成 0 或部分数字进而可能被意外捞出来。解决方法是保持类型一致要么把字段改成数值类型要么把边界值加上引号写成BETWEEN 100001 AND 100005。这一点在写范围查询前一定要先确认字段类型。2.4 NULL 值范围条件天然排除 NULL这是另外一个高频困惑。表里某一行的price是NULL查询SELECT * FROM products WHERE price BETWEEN 100 AND 200;这行永远不会出现在结果里。因为NULL参与任何比较运算结果都是NULL而WHERE子句只会保留条件为TRUE的行NULL相当于“不成立”。如果你业务上要求“价格为空也展示”就得单独把 NULL 捞回来SELECT * FROM products WHERE price BETWEEN 100 AND 200 OR price IS NULL;同理NOT BETWEEN也会排除掉 NULL。想表示“价格不在 100 到 200 之间或者价格为空的商品”时需要写成price NOT BETWEEN 100 AND 200 OR price IS NULL。这个坑在报表统计中特别常见不加IS NULL会导致总数对不上。3. 组合查询与动态范围的实战写法3.1 范围排序分页ORDER BY 和 LIMIT 的配合要点BETWEEN AND最常见的业务场景就是商品筛选、订单查询这类“选一个区间再排序分页看结果”。典型写法SELECT id, title, price FROM products WHERE price BETWEEN 100 AND 500 ORDER BY price ASC, id ASC LIMIT 20 OFFSET 0;这里有个容易被忽略的细节排序字段最好加上一个唯一字段比如id。因为如果只按price排序而表里存在大量价格相同的商品翻页时同样的记录可能出现在不同页面也可能有记录被跳过。ORDER BY price ASC, id ASC能保证排序顺序完全稳定。再往后如果用户翻到第 100 页、第 200 页OFFSET越来越大性能会明显恶化。因为 MySQL 需要先扫描并丢弃前 N 行再返回目标行。此时可以改用基于游标的“键集分页”也就是记住上一页最后一条记录的排序键值下一页从这个位置继续往后取-- 第一页查到的最小 price 是 105最后一个 id 是 8888 SELECT id, title, price FROM products WHERE price BETWEEN 100 AND 500 AND (price 105 OR (price 105 AND id 8888)) ORDER BY price ASC, id ASC LIMIT 20;这种写法能让数据库直接通过索引定位到上次的位置不用再把前面几万行全部读一遍。对后台商品筛选、订单翻页这类高并发场景收益非常明显。3.2 NOT BETWEEN反范围查询的语义和 NULL 坑NOT BETWEEN表达的语义是“不在某个区间”。我先提醒一句在 SQL 里NOT BETWEEN并不是简单地“把 BETWEEN 的结果取反”因为 NULL 值会被额外排除掉。看这两个查询SELECT * FROM products WHERE price NOT BETWEEN 100 AND 500; SELECT * FROM products WHERE price 100 OR price 500;在没有 NULL 数据的前提下这两个结果是一样的。一旦表里存在price IS NULL的行第一个查询不会返回它第二个查询同样不会返回它因为NULL 100的结果是 NULL。所以两者在 NULL 处理上是一致的但很多开发者误以为“取反”应该把 NULL 也算进来。如果业务确实需要把无价格的商品也算作“不在区间内”要在外层补一个OR price IS NULL。另外注意NOT BETWEEN在实际执行时往往会被优化器转换成两个范围条件的OR。如果字段上有合适的索引price 100 OR price 500有可能走range扫描但也可能退化成全表扫描具体要看数据分布和执行计划。遇到这种反范围查询性能不佳时我会更推荐用UNION ALL拆成两段查或者检查是否需要调整索引。3.3 多条件组合IN、LIKE、OR 一起用时的优先级范围查询很少是孤立条件经常要跟IN、LIKE、其他等值条件组合。这里要特别强调AND和OR的优先级AND 的优先级高于 OR。所以下面的查询SELECT * FROM orders WHERE status PAID OR status REFUND AND amount BETWEEN 100 AND 500;实际解析执行顺序是WHERE status PAID OR ( status REFUND AND amount BETWEEN 100 AND 500 )如果你本意是“支付状态在两种里任选一种并且金额都要在 100 到 500 之间”那就必须加括号WHERE (status PAID OR status REFUND) AND amount BETWEEN 100 AND 500;这个错误在真实项目中出现频率很高有时候排查半天发现不是业务逻辑问题而是括号漏了。我的习惯是只要一个WHERE里同时出现AND和OR就把OR的条件用括号括起来不管优先级是否真的有歧义至少后期维护的人不会看错。LIKE和BETWEEN组合的时候也有讲究。比如模糊搜索商品名称同时按价格区间过滤SELECT * FROM products WHERE title LIKE %保温杯% AND price BETWEEN 50 AND 100;这种查询如果只靠price上的索引MySQL 可能先按价格范围过滤出大量记录再在内存里做LIKE匹配。但如果LIKE是前缀匹配保温杯%并且有对应的联合索引执行计划会更理想。经验是LIKE尽量写成前缀匹配而不是%关键词%后者无法利用普通索引。3.4 动态范围参数从存储过程到防注入实际业务里范围查询的上下界经常由前端传参决定比如后台筛选项里的“最低价”“最高价”可能只有一个值也可能两个都有还可能都为空。一种常见做法是在应用层拼 SQLWHERE amount BETWEEN #{minPrice} AND #{maxPrice}这里的#{minPrice}如果来自用户输入必须走参数化预处理绝不能直接字符串拼接。虽然BETWEEN的边界值看起来只是数字但黑客照样能通过构造特殊输入改变语义比如传一个1 OR 11尝试注入。数据库驱动层的预处理语句会把这些变量当作参数值而不是 SQL 片段这是最基础也最重要的一道防线。如果是在存储过程里写动态范围可以这样DELIMITER // CREATE PROCEDURE sp_query_orders( IN min_amount DECIMAL(10,2), IN max_amount DECIMAL(10,2) ) BEGIN SELECT id, total_amount, create_time FROM orders WHERE total_amount BETWEEN min_amount AND max_amount; END // DELIMITER ;调用时传入两个参数即可CALL sp_query_orders(100.00, 500.00);但要注意存储过程的参数同样来自外部如果业务上允许传入 NULL需要先用条件分支处理默认值否则BETWEEN NULL AND 500会返回空结果。更好的做法是在应用层把边界值补齐再以普通参数方式执行 SQL这样排查问题更直观。4. 性能优化让范围查询用好索引4.1 范围查询为什么能走索引先理解 B 树的查找逻辑MySQL 的 InnoDB 索引底层是 B 树叶子节点上的数据按索引键值有序排列。范围查询之所以高效是因为数据库可以快速定位到范围的起点然后沿着叶子节点链表顺序扫描到终点不需要全表扫描。这个特性让BETWEEN AND天然适合走索引。比如表上有一个普通索引idx_amountALTER TABLE orders ADD INDEX idx_amount (amount);那么查询SELECT * FROM orders WHERE amount BETWEEN 100 AND 500;优化器大概率会选择idx_amount做范围扫描执行计划里type会显示为range。这也是为什么我经常提醒范围查询的字段一定要建索引并且尽量让范围条件能够“直接作用在裸字段上”。4.2 组合索引与范围条件的位置关系等值在前范围在后单独字段的范围查询好理解组合索引才是最容易设计错的地方。假设经常需要按“用户 ID 下单时间”查询订单SELECT * FROM orders WHERE user_id 10086 AND create_time BETWEEN 2024-01-01 AND 2024-01-31;最佳索引设计是(user_id, create_time)这样的联合索引。因为user_id先做等值匹配可以直接定位到该用户的索引区间然后create_time在这个区间里是一个连续的有序序列范围扫描非常高效。反过来如果索引是(create_time, user_id)查询条件里create_time走范围扫描那么user_id 10086这个条件就很难再通过索引精确定位。范围条件之后的索引列对过滤的帮助会大打折扣。所以组合索引的设计原则可以简单概括为等值条件放前面范围条件放后面。还有一个更细的点索引里范围条件后的列不能用于后续的“定位”但有些场景下依然可以作为覆盖索引的一部分避免回表。例如索引是(user_id, create_time, status)上面的查询再带上status字段如果SELECT只取这三列那么存储引擎可以直接从索引叶子节点拿到所有数据连回表都省了执行计划里会出现Using index。4.3 让索引失效的三种常见写法我排查过很多慢查询发现范围查询不走索引的原因高度集中主要有三类。第一类列被函数包裹。比如按创建日期统计订单量时很多人会这样写SELECT COUNT(*) FROM orders WHERE DATE(create_time) BETWEEN 2024-01-01 AND 2024-01-31;画风看着没问题但在create_time上套了DATE()函数之后索引就失效了。因为 MySQL 无法在 B 树里直接对函数结果做范围查找只能把整列取出来算一遍再过滤。正确写法是改写范围条件SELECT COUNT(*) FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-02-01 00:00:00;第二类列和边界值类型不一致。前面讲的VARCHAR字段配数字边界就属于这一类。一旦发生隐式类型转换MySQL 通常会放弃索引。第三类在范围条件上做运算。比如WHERE price * 0.8 BETWEEN 100 AND 200;price列被乘法运算包裹索引失效。改成WHERE price BETWEEN 100 / 0.8 AND 200 / 0.8;虽然边界值变成了表达式但列本身是裸的索引可以正常使用。这个技巧很实用。有些特殊情况确实避免不了函数包裹列比如必须按月份统计。MySQL 8.0 之后可以建函数索引ALTER TABLE orders ADD INDEX idx_create_time_month ((DATE(create_time)));但我不建议一上来就上函数索引优先还是改写 SQL。函数索引会带来额外的写入成本和维护成本能不用就不用。4.4 用 EXPLAIN 确认范围查询到底怎么执行写 SQL 不能靠猜确认执行计划是一个好习惯。以刚才的订单查询为例EXPLAIN SELECT id, total_amount FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-02-01 00:00:00;输出里重点看这几列列名典型值说明typerange说明走了索引范围扫描keyidx_create_time实际使用的索引名rows5000估算扫描行数ExtraUsing index condition索引条件下推效率不错如果type显示ALL说明全表扫描大概率是索引没建或者条件写得不合适。如果rows特别大说明估算扫描行数很多即使走了索引也要考虑是不是范围太大。MySQL 8.0 里还可以用EXPLAIN ANALYZE做实际执行分析EXPLAIN ANALYZE SELECT id, total_amount FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-02-01 00:00:00;它会把真实执行时间、扫描行数、回表行数都列出来比普通EXPLAIN直观很多。排查异常范围查询时我一般先看EXPLAIN再对慢查询用EXPLAIN ANALYZE做二次确认。4.5 大表范围查询的几条优化路线当表数据量很大时即使有索引范围查询可能也会因为命中行数过多而变慢。这里说几条实战验证过的方法。第一尽量用覆盖索引。如果查询只需要少数几个字段可以建立一个包含这些字段的联合索引让扫描过程不回表。比如统计某段时间内的订单金额总和CREATE INDEX idx_create_time_amount ON orders (create_time, total_amount); SELECT SUM(total_amount) FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-02-01 00:00:00;二级索引里已经有total_amount统计时不必回表性能会有质的提升。第二分区表要谨慎。RANGE 分区对日期范围查询确实能避免扫描无关分区但分区表本身有限制比如主键和分区键的关系、全局唯一约束等。不要为了优化范围查询盲目建分区通常先把索引做对再考虑冷热数据分离和归档。第三超大范围查询拆小范围。比如一次查一年的数据很慢可以拆成按日或按周的多段查询分段执行每段只扫描一小段索引。这在数据同步、批量处理的场景里很实用。范围查询锁定的区域也会变小并发冲突概率降低。5. 高频问题排查与避坑速查5.1 高频问题复盘现象、原因和解决办法下面这些场景几乎都是BETWEEN AND最常见的坑。我按“现象 - 原因 - 解法”整理成表格方便你直接对照。现象可能原因解决方案按月份查订单月底数据总少一天DATETIME 字段配合字符串日期边界时上界被转成00:00:00使用月初 AND 下月月初范围条件没走索引查询很慢列被函数包裹或者发生隐式类型转换改写条件让列保持裸字段参与比较字符串范围查询结果“莫名其妙”字典序排序规则和业务直觉不一致用LIKE前缀匹配或自行设计编码范围浮点字段查不到明明存在的记录FLOAT/DOUBLE存储误差导致边界比较失败改用DECIMAL类型明明有 NULL 数据范围查询却查不到NULL比较结果为NULL被WHERE过滤显式加OR 列 IS NULL上下界传反了返回空结果却不报错MySQL 对BETWEEN b AND a不报错应用层校验边界大小翻页翻得越深越慢OFFSET越大数据库扫描丢弃行越多改成基于游标的键集分页5.2 自查清单写范围查询前过一遍这些点我把多年的习惯整理成一份自查清单。每次写完一条带范围条件的 SQL我都会按顺序过一遍字段类型是什么边界值和它一致吗如果不一致优先改边界值类型别让 MySQL 做隐式转换。日期字段是DATE、DATETIME还是TIMESTAMP时间精度到秒还是微秒有没有用“下一天”作为上界业务需不需要包含NULL值如果字段允许为空确认查询逻辑是否把 NULL 单独考虑。组合索引的列顺序是不是“等值在前、范围在后”范围条件之后的索引列能否起到辅助过滤或覆盖查询的作用查询条件里的列是否被函数、运算包裹如果有考虑改写 SQL 而不是建函数索引。分页排序是否稳定排序字段是否包含主键或其他唯一字段用EXPLAIN看过执行计划了吗type是不是rangerows估算是否合理这套清单在团队代码评审里也特别好用能让很多隐蔽问题在发上线前就被拦下来。5.3 我长期使用的几条写作习惯最后分享几个我写范围查询时长期遵守的习惯不算什么高深技巧但确实救过我好几次。第一能写左闭右开就不写闭区间。尤其日期时间字段我基本不用BETWEEN接字符串日期而是直接用下界 AND 上界的裸条件。这个习惯让边界口径永远清晰也方便后续在索引上做优化。第二边界值永远写成参数或字段类型匹配的字面量。不要图省事让数字边界和字符串字段硬碰。遇到VARCHAR存数字的情况我宁可多写几个字符加引号也不愿意赌 MySQL 的隐式转换不出问题。第三写完查询先看执行计划再上线。一次偷懒可能就会产出一次慢查询事故。范围查询本身非常依赖索引而索引是否生效在EXPLAIN里一目了然。养成习惯之后你会在写 SQL 时自然地考虑字段顺序、类型匹配、函数包裹这些因素而不是等出了问题再返工。BETWEEN AND的底层逻辑并不难难的是它在不同数据类型、不同查询场景里延伸出的各种边界细节。把上面这些点都纳入日常习惯之后你会发现范围查询不再是“偶尔翻车”而是可以放心直接写的一段常规 SQL。

MySQL BETWEEN AND 范围查询：边界陷阱与索引优化实战

相关推荐

Oracle数据泵expdp/impdp实战：参数详解与迁移避坑全攻略

Redis基础知识全解析：从数据结构到持久化与性能优化

短剧什么都知道：欲望叙事与高质量进化之路

更多资讯

逻辑智力题的本质是结构化拆解能力训练

气象数据小波分析实战：MATLAB实现CWT、XWT与WTC周期诊断

C#扩展方法实战：为.NET Framework基础类型赋能

揭秘AnimeGen-T2V-A14B-Lightning-int4的双专家MoE架构：高噪声与低噪声模型如何分工

SciPy多重积分实战：从dblquad到nquad的数值积分指南

Django电信资费管理系统：资费模型与出账流程设计实践

今日更新

Cephadm实战指南：Linux系统工程师的生产级Ceph集群部署与排障

不想再被 Token 额度卡脖子？把免费 API 接入 WorkBuddy 的完整教程

ITK中SpatialObject几何体系解析：坐标系、变换与管道集成

本周精选

交通拥堵预测毕设：基于Python+LSTM的车辆流量预测系统全流程解析

MATLAB/Simulink搭建10机39节点电力系统暂态稳定仿真实战

无人机航拍三维重建全流程：从SfM到网格生成的避坑指南

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？