在MySQL中检查日期范围冲突

Question

我正在编写一个酒店预订系统。在大量学习（包括研究 Stack Overflow ）之后，我编写了以下 SQL 以查找空闲房间：

SELECT
*
FROM room
WHERE
    room_id NOT IN (
        SELECT room_id
        FROM bookings
        WHERE
                 checkin <= '$check_in'
            AND checkout >= '$check_out'
    )

但问题在于它没有考虑到时间检查是12:00:00和退房时间是11:59:00。

此外，它没有给出正确的查询结果，例如在日期范围内不起作用。例如，如果我预订了一个编号为501的单人间，入住日期为15-18日。如果我再次运行一个查询17-19，这个房间看起来是空闲的，但实际上它应该是被占用的。

有人能建议一个非常好的和有效的SQL语句，以获得准确的日期，以便预订系统不会发生冲突，因为该系统将在实际中实现，错误将导致许多问题。

提前感谢。

- Samia Ruponti

Answer 1

你遇到的问题是你的查询不够健壮。当你分解这个问题时，你会发现：

如果由 $check_in 和 $check_out 定义的时间范围与 checkin 和 checkout 定义的时间范围有任何重叠部分，则该房间已被预订。否则，该房间尚未预订。

这意味着：

• 如果 $check_in >= checkin 并且 $check_in <= checkout，则该房间已预订
• 或者 如果 $check_out >= checkin 并且 $check_out <= checkout，则该房间已预订
• 或者 如果 $check_in <= checkin 并且 $check_out >= checkout，则该房间已预订

因此，你需要在子查询中表示这两种情况，以获取你正在寻找的信息。

此外，你应该使用 datetime 进行比较而不仅仅是 time，否则会产生副作用。 编辑：SQL 查询 (请记住，有多种方法可以解决这个问题。我只是提供一个尽可能与你已经拥有的东西保持一致的示例。再次强调，我假设 checkin、checkout、$check_in 和 $check_out 都将解析为 datetime 类型)

SELECT *
FROM room
WHERE room_id NOT IN
(SELECT room_id 
 FROM bookings
 WHERE
   (checkin <= '$check_in' AND checkout >= '$check_in') OR
   (checkin <= '$check_out' AND checkout >= '$check_out') OR
   (checkin >= '$check_in' AND checkout <= '$check_out'))

- Brian Driscoll

---

你好，谢谢您的回复。请问您能否编写一个模拟SQL语句供我参考，因为我不太擅长编写SQL语句（尤其是MySQL）。再次感谢。 - Samia Ruponti

那是个好发现！该查询检查了范围的两端，但中间呢？换句话说，假设客户想预订一个为期4晚的房间，但在中间已经被预订了一晚怎么办？预订的入住时间将大于请求的$check_in，而预订的退房时间将小于请求的$check_out，但房间不可用...对吗？ - Aerik

@Aerik，你也发现得很好 - 我会相应地更新我的答案。 - Brian Driscoll

这个过程过于复杂了。请看我的回答：https://dev59.com/Jl_Va4cB1Zd3GeqPTGx-#14811558 - Pryo

@Pryo 正如我在答案中提到的，这只是许多可行方案之一。我同意你的方法更简单，且看起来能够达到相同的效果。 - Brian Driscoll

Answer 2

你原来的逻辑非常接近，只需要交换'$check_in'和'$check_out'的值即可。例如：

SELECT *
FROM room
WHERE room_id NOT IN
(
    SELECT room_id
    FROM bookings
    WHERE checkin <= '$check_out' AND checkout >= '$check_in'
)

Brian Driscoll的答案侧重于构成预订冲突的场景，如下所示：

---------------|-----Booked-----|---------------
       |----A1----|
                             |----A2----|
                    |--A3--|
            |----------A4----------|

Case A2 & A3: checkin <= '$check_in' AND checkout >= '$check_in'
Case A1 & A3: checkin <= '$check_out' AND checkout >= '$check_out'
Case A4:      checkin >= '$check_in' AND checkout <= '$check_out'

然而，构成无冲突的情境要简单得多。只有两种情况：

---------------|-----Booked-----|---------------
  |----B1----|                             
                                  |----B2----|

Case B1: checkin > '$check_out'
Case B2: checkout < '$check_in'

因此，当预订和潜在预订之间没有冲突的情况可以使用以下SQL语句表示：

checkin > '$check_out' OR checkout < '$check_in'

相反地，如果要检查冲突，我们只需要对此进行否定。因此，使用德摩根定律，否定为：

checkin <= '$check_out' AND checkout >= '$check_in'

......它达到了上述给出的解决方案。

- Pryo

Answer 3

我认为这可能会让你朝着正确的方向开始...

SELECT R.*
FROM room AS R
     LEFT OUTER JOIN bookings AS B USING (room_id)
WHERE B.room_id IS NULL
      OR (B.checkout < '$check_in'
          AND B.checkin > '$check_out')

- Web User

Answer 4

这里有一些好的想法：http://forums.digitalpoint.com/showthread.php?t=63746

另外，你在使用哪些列类型进行签入和签出？你确定你正在传递正确格式的值到$check_in和$check_out吗？

- Aerik