如何按用户取还车日期范围查询autos_bookings表冲突预订记录

业务背景 #

开发汽车租赁系统车辆预订功能时，需要根据用户提交的预期取车时间、预期还车时间，查询指定车辆是否存在时段冲突的已有预订记录，避免同一车辆被重复预订。
涉及入参：

• $dateclientewantsrent：用户提交的预期取车日期时间
• $dateclientewantsreturn：用户提交的预期还车日期时间
• $target_auto_id：用户要预订的目标车辆ID（仅同车辆需要校验时段冲突，不同车辆的预订时段重叠无影响）

涉及数据表为autos_bookings，表结构如下：

CREATE TABLE `autos_bookings` (
  `id` int(11) NOT NULL,
  `booking_id` int(11) NOT NULL DEFAULT '0',
  `auto_id` int(11) NOT NULL DEFAULT '0',
  `date_out` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_return` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `autos_bookings`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `autos_bookings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

冲突判断逻辑 #

两个时间段存在重叠（冲突）的核心判断规则为：两个时段不是完全错开的，就属于冲突。
完全错开的场景只有两种：

1. 已有预订的还车时间早于或等于用户的取车时间（已有预订在用户租车开始前就结束了）
2. 已有预订的取车时间晚于或等于用户的还车时间（已有预订在用户租车结束后才开始）
   排除这两种场景，剩下的所有情况都属于时段冲突。

最终SQL实现 #

基础查询框架补充WHERE条件后的完整语句如下（默认业务规则允许首尾时间相接，即前一单还车时间等于下一单取车时间不算冲突）：

SELECT * FROM `autos_bookings` 
WHERE 
  `auto_id` = $target_auto_id
  AND `date_out` < $dateclientewantsreturn
  AND `date_return` > $dateclientewantsrent;

注意事项 #

• 如果业务规则不允许首尾时间相接，哪怕时间点完全重合也算冲突，只需要把条件中的<和>替换为<=和>=即可
• 禁止直接把用户传入的参数拼接进SQL语句，避免SQL注入风险，生产环境请使用预编译语法绑定参数
• 如果查询返回结果条数大于0，说明该车辆在用户选择的时段已有冲突预订，无法完成下单

内容的提问来源于stack exchange，提问作者Ricardo Capistran