BETWEEN ... AND 是闭区间还是开区间？日期边界容易踩坑的地方？

考察要点

这道题看似基础，实则深入考察候选人对 SQL 操作符语义的精确理解、对数据类型隐式转换的警惕性，以及在真实业务场景下处理边界问题的严谨性。面试官想看你是否懂：

1. BETWEEN ... AND 操作符的本质：它等价于 a >= x AND a <= y，是一个闭区间。
2. 日期时间类型的处理陷阱：特别是 DATE 和 DATETIME / TIMESTAMP 在混合使用时的隐式转换规则，这是导致查询结果不符合预期的重灾区。
3. SQL 优化意识：能否写出既正确又能高效利用索引的查询。

核心概念

BETWEEN ... AND 是一个用于判断表达式值是否在指定范围内的操作符。其语法为 expr BETWEEN min_val AND max_val。

1. 区间定义：闭区间

BETWEEN 定义的是一个闭区间，包含两个端点值。它完全等价于： expr >= min_val AND expr <= max_val

这意味着，如果 expr 的值等于 min_val 或 max_val，BETWEEN 条件都将成立。

2. 日期边界陷阱：DATETIME / TIMESTAMP 的隐式转换

这是 BETWEEN 最常见的坑，尤其是在处理包含时间的日期列时。

假设我们有一个订单表 orders，created_at 字段类型为 DATETIME。

 1CREATE TABLE orders (
 2    id INT PRIMARY KEY,
 3    order_no VARCHAR(20),
 4    created_at DATETIME
 5);
 6INSERT INTO orders VALUES
 7(1, 'ORD001', '2023-12-31 08:00:00'),
 8(2, 'ORD002', '2023-12-31 23:59:59'),
 9(3, 'ORD003', '2024-01-01 00:00:00'),
10(4, 'ORD004', '2023-12-31 00:00:00');

错误示例：查询 2023 年 12 月的所有订单。

 1SELECT * FROM orders
 2WHERE created_at BETWEEN '2023-12-01' AND '2023-12-31';

问题分析： 当一个 DATETIME 类型的列与一个只包含日期的字符串 '2023-12-31' 比较时，数据库会进行隐式转换，将字符串转换为该日期的零点时刻。 因此，上述 WHERE 子句实际上等价于：

 1WHERE created_at >= '2023-12-01 00:00:00' AND created_at <= '2023-12-31 00:00:00';

结果：这个查询只会返回 id=4 的订单，而 id=1 和 id=2 这两条在 12 月 31 日当天但非零点创建的订单会被遗漏。这显然不符合“查询 12 月所有订单”的业务初衷。

3. 正确处理日期范围的方案

方案一（最佳实践）：使用半开区间 这是最推荐、最严谨、最不容易出错的方法。它利用 >= 和 < 构造一个左闭右开的区间。

 1-- 查询 2023 年 12 月的所有订单
 2SELECT * FROM orders
 3WHERE created_at >= '2023-12-01' AND created_at < '2024-01-01';

优点：

• 逻辑清晰：小于下一个时间区间的开始，完美覆盖所有精度（秒、毫秒等）。
• 无需关心精度：无论 created_at 是 DATETIME, TIMESTAMP, DATETIME(3) 还是 DATETIME(6)，此写法永远正确。
• 普适性强：对 DATE 类型同样有效，且行为一致。

方案二：在 BETWEEN 中明确时间 如果坚持使用 BETWEEN，必须手动指定右边界的结束时刻。

 1SELECT * FROM orders
 2WHERE created_at BETWEEN '2023-12-01 00:00:00' AND '2023-12-31 23:59:59';

缺点：

• 繁琐且易错：如果列精度是毫秒 DATETIME(3)，你需要写成 '2023-12-31 23:59:59.999'，非常不便。
• 可读性差：不如半开区间直观。

常见低分回答

1. 回答“BETWEEN 是开区间”：这是最基础的概念错误，直接出局。
2. 直接使用 BETWEEN '...' AND '...' 查询 DATETIME 列：如 WHERE created_at BETWEEN '2023-12-01' AND '2023-12-31'，这表明候选人没有意识到日期时间处理的经典陷阱，缺乏项目经验。
3. 在列上使用函数：WHERE DATE(created_at) = '2023-12-31' 或 WHERE DATE_FORMAT(created_at, '%Y-%m') = '2023-12'。这是典型的“索引失效”写法。在 created_at 列上使用任何函数都会导致数据库放弃使用该列上的索引（除非是函数索引，但 MySQL 8.0 之前不支持），转而进行全表扫描，在数据量大时是性能灾难。
4. 使用 LIKE 操作符：WHERE created_at LIKE '2023-12-31%'。虽然这个查询在功能上可能正确，但它将 DATETIME 列当作字符串处理。这不仅性能通常劣于日期范围比较，而且无法利用日期/时间类型的专门优化，也不是标准的 SQL 实践。
5. 混淆 NULL 的处理：认为 NULL 值会被 BETWEEN 匹配。实际上，任何涉及 NULL 的比较（IS NULL 除外）结果都是 UNKNOWN，在 WHERE 子句中被当作 FALSE，所以 col BETWEEN ... 不会返回 col 为 NULL 的行。

工程实践与优化

• 索引是关键：用于范围查询的列（如 created_at）必须建立 B+ 树索引。一个 ALTER TABLE orders ADD INDEX idx_created_at (created_at); 可以让查询性能从 $O(N)$（全表扫描）提升到 $O(\log N + K)$（范围扫描，K为结果集大小）。
• 查看执行计划：对于上述推荐的半开区间写法，EXPLAIN 的结果应该是这样的：
  text

  ⧉⛶

   1+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
   2| id | select_type | table  | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
   3+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
   4|  1 | SIMPLE      | orders | NULL       | range | idx_created_at| idx_created_at | 5       | NULL |    2 |   100.00 | Using where |
   5+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+

  • type: range：表示使用了索引进行范围扫描，这是高效的标志。
  • key: idx_created_at：表示实际使用了 created_at 列上的索引。
  • 如果 type 显示为 ALL，说明正在进行全表扫描，需要立即排查索引失效问题。
• 数据类型选择：如果业务场景只关心到天，就使用 DATE 类型，不要用 DATETIME 或 VARCHAR。精确的数据类型选择能节省存储空间，并让查询意图更清晰。

常见追问与延伸题目

1. 追问：为什么业界普遍推荐使用半开区间 (>=, <) 的写法，而不是 BETWEEN？

   • 回答要点 1: 精度无关性。半开区间写法对于 DATE, DATETIME, TIMESTAMP 以及带不同小数秒精度的 DATETIME(p) 类型，写法完全一致，永远正确。而 BETWEEN 写法在处理带时间的类型时，需要费力地构造 23:59:59.999... 这样的边界，极易出错。
   • 回答要点 2: 区间连续性。在按时间窗口（如每小时、每天）分割数据进行处理时，半开区间 [T1, T2), [T2, T3)... 可以完美衔接，无重叠无遗漏。而使用闭区间 [T1, T2-1s], [T2, T3-1s]... 则逻辑上更复杂，容易出错。
   • 回答要点 3: 遵循编程规范。在多数编程语言中，处理范围的循环和函数（如 C++ STL, Java substring）都采用左闭右开的设计，保持 SQL 与代码逻辑的一致性，可以减少开发者的心智负担。

2. 追问：如果我就是想用 BETWEEN，并且 created_at 列上建了 (user_id, created_at) 这样的复合索引，查询 user_id = 123 在 12 月的订单，SQL 怎么写最高效？

   • 回答要点: 此时，查询条件应严格遵循最左前缀原则。
   sql

   ⧉⛶

    1SELECT * FROM orders
    2WHERE user_id = 123
    3  AND created_at >= '2023-12-01'
    4  AND created_at < '2024-01-01';

   • 解释: 这个查询 user_id 是等值查询，created_at 是范围查询。复合索引 (user_id, created_at) 可以被高效利用。优化器会先用 user_id 定位到 123 的索引范围，然后在这个小范围内再根据 created_at 的 B+ 树结构进行范围查找。EXPLAIN 中 type 依然是 range，但 key_len 会显示同时使用了 user_id 和 created_at 两部分索引。

3. 追问：如果数据库换成 PostgreSQL，BETWEEN 的行为和日期处理有什么不同吗？

   • 回答要点:
     • BETWEEN 语义相同：PostgreSQL 的 BETWEEN SYMMETRIC (默认) 和 SQL 标准一样，都是闭区间，等价于 >= 和 <=。行为与 MySQL 一致。
     • 日期时间处理更严格：PostgreSQL 的类型系统更严谨。虽然它也会做相似的隐式转换，但最佳实践同样是使用半开区间 [start, end)。
     • 特色操作符: PostgreSQL 提供了更丰富的日期/时间函数和范围类型（RANGE Types），例如 tsrange 类型和包含操作符 @>，可以更优雅地处理范围查询。例如，可以创建一个 tsrange 类型的范围，然后用 @> 判断 created_at 是否被该范围包含，语法上更直观：WHERE tsrange('2023-12-01', '2024-01-01', '[)') @> created_at。这在功能上等价于半开区间写法，但在复杂场景下更具表现力。