预订系统给定时段可用槽位计算SQL查询逻辑优化求助

问题重现 #

开发预订系统时，需判断指定时段内是否有可预订桌位/房间（最多支持N个，如30个），数据存储在MySQL中，表结构包含id, start_time, end_time及其他业务字段。

原SQL尝试通过判断时段重叠统计占用数，但逻辑错误：查询16:00-19:00的可用资源时，返回了大量完全不重叠的记录（比如id=1, start_time=2022-08-06 12:00:00, end_time=2022-08-06 15:00:00），导致误判无可用桌位。所有时段均取整到**:00或**:30。

原错误SQL：

SELECT * FROM reservations WHERE ((start_time < '${end_time}' AND start_time >= '${start_time}') 
        OR (start_time <= '${start_time}' AND end_time >= '${end_time}') OR (end_time > '${start_time}')

问题根源 #

原SQL的第三个条件OR (end_time > '${start_time}')逻辑完全错误，会无差别匹配所有结束时间晚于查询时段开始时间的记录，哪怕这些记录和查询时段完全没有交集，直接导致统计结果失真。

正确实现 #

1. 核心重叠判断规则 #

两个时段[查询开始, 查询结束)和[已预订开始, 已预订结束)存在重叠的唯一必要条件是：

查询开始 < 已预订结束 AND 已预订开始 < 查询结束

这个规则能精准覆盖所有交集场景，同时排除完全不重叠的情况（比如已预订时段在查询时段之前或之后），且符合业务逻辑——若A预订12:00-15:00，B预订15:00-18:00，两者不会被判定为重叠，15:00可以正常衔接预订。

2. 修正后的SQL #

不要用SELECT *，直接统计占用数量更高效：

SELECT COUNT(*) AS occupied_count
FROM reservations
-- 核心重叠判断条件
WHERE start_time < '${query_end}' 
  AND end_time > '${query_start}'
-- 可选：添加日期筛选，缩小查询范围（按天预订场景必备）
AND DATE(start_time) = DATE('${query_start}')

其中${query_start}是前端传入的预订开始时间（如2022-08-06 16:00:00），${query_end}是预订结束时间（如2022-08-06 19:00:00）。

3. 可用逻辑判断 #

拿到occupied_count后和最大可预订数N对比：

• 若occupied_count < N：该时段有可用桌位/房间；
• 若occupied_count >= N：无可用资源。

4. 性能优化建议 #

• 给start_time和end_time建立联合索引，大幅提升时段查询速度：

  CREATE INDEX idx_reservations_time ON reservations(start_time, end_time);
  

• 必须使用参数化查询替换字符串拼接，防止SQL注入攻击，同时避免日期格式解析错误。

内容的提问来源于stack exchange，提问作者Konstantin Mironov