查找预订重叠以检查日期可用性。

Question

查找预订重叠以检查日期可用性。

7

我希望你能帮我编写一个查询来检查MySql中房间的可用性。当前我有这样一种表：

 ROOM |    FROM    |     TO
 -----------------------------
  101 | 2014-08-09 | 2014-08-14 
  102 | 2014-08-09 | 2014-08-14 
   ...           ...        ...

我已经预订了101房间，时间从2014年9月8日到2014年9月14日，我的查询可用性的方式如下=

SELECT order_id FROM booking 
WHERE `ROOM` = '101' 
AND (`FROM` BETWEEN '2014-08-08' AND '2014-08-20') 
AND (`TO` BETWEEN '2014-08-08' AND '2014-08-20')

在上面的例子中，我将检查在以下日期之间的可用性：

   What i trying to archive is this
Order  --------09++++++++++13--------------
Check1 -----08+++++++++++++++++++++++++17-- Not availble
Check2 -----------------12+++++++++++++17-- Not availble
Check3 -----------10----------------------- Not availble
Check4 -----------10+11-------------------- Not availble
Check5 -----------------------14+++++++17-- Available
Check6 --07++++09-------------------------- Not availble
Check7 --------------------------15-------- Availble


SCALE  6-07-08-09-10-11-12-13-14-15-16-17-18-19...

我必须检查房间是否可用。如果查询结果返回了一些内容，那么这意味着房间已经被预订了... 如果没有返回任何内容，则相反。

- KeySee

1

检查列名。在查询中使用小写字母。 - Prashant Tapase

啊哈哈。你现在能帮我解决这个查询吗？ - KeySee

你的问题能说得更清楚一些吗？ - Suganthan Madhavan Pillai

已经尝试过使用SELECT order_id FROM booking WHERE ROOM = '101' AND (FROM BETWEEN '2014-08-08' AND '2014-08-20') OR (TO BETWEEN '2014-08-08' AND '2014-08-20')，但不起作用... - KeySee

谢谢你的回答，我会尽快检查它们。现在我必须走了... - KeySee

可能是重复的，https://dev59.com/K4rda4cB1Zd3GeqPSOPX#38094012 - Bhaveshkumar

3个回答

1

如果您想检查整个时间段内的房间是否可用，请查找与所询问的时间段重叠的现有预订：

SELECT order_id FROM booking 
WHERE `ROOM` = '101' 
 AND `FROM` <= '2014-08-20' 
 AND `TO` >= 2014-08-08'

如果查询返回行，则表示您有预订冲突，房间不可用。

- Bulat

0

为了测试一个提议的房间预订是否与现有的预订冲突（重叠），您需要像这样的东西：

select count(*) as num_conflicts
from booking
where room = ?proposed_room
    and (from <= ?proposed_to)
    and (to >= ?proposed_from);

如果任何一天的预订已经分配给另一个预订，那就算是冲突。

- John Bollinger

显然，?proposed_* 不是 SQL，但这就是你在某些宿主语言中使用预处理语句参数的地方。 - John Bollinger

谢谢，你的代码有效，但我不需要计数，而是订单ID :D - KeySee

从booking表中选择order_id，其中ROOM等于'101'且(FROM <= '2014-08-10')且(`TO >= '2014-08-15')的记录未显示（失败）。 - KeySee

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- dcp · Accepted Answer

首先，让我们概括一种算法来检查区间[a,b]和[c,d]之间是否有重叠。请注意这些区间上的方括号，这意味着包含端点。我们可以使用以下逻辑来检查区间重叠：

a <= d and b >= c

如果这个条件成立，那么我们就有重叠。

因此，要将这个算法应用到SQL中，我们可以像这样做：

a = 2014-08-08
b = 2014-08-20
c = FROM
d = TO
SELECT order_id FROM booking 
WHERE NOT EXISTS (
     SELECT * FROM booking
       WHERE ROOM = '101'
         AND '2014-08-08' <= `TO`
         AND '2014-08-20' >= `FROM`
 )
 AND ROOM = '101'

你的方法存在另一个问题，就是你在检查房间是否可用，假设如果房间可用，那么你将用另一条SQL语句来预订它。这是一个有问题的方法，因为有可能会出现重复预订房间的情况。考虑到两个进程在同一时间（或接近同一时间）检查房间可用性的可能性。或者，如果此代码是事务的一部分，但尚未提交，则其他进程将无法看到您已提交的结果，从而会重复预订房间。

为了解决这个错误的方法，我们需要在检查其可用性之前锁定房间行。假设您还有另一个名为ROOM的表，您可以使用“FOR UPDATE”语句锁定该行：

SELECT * FROM `ROOM` WHERE ROOM = '101' FOR UPDATE

"FOR UPDATE"将锁定该房间行，这将防止另一个进程在您的事务完成之前检查该房间的可用性。锁定行后，您可以运行重叠检查。因此，您消除了双重预订问题。

您可以在此处了解更多关于“FOR UPDATE”的信息。