mysql - 如何防止同一日期的时间重叠？

Question

我正在开展一个项目，必须防止同一日期的时间重叠。用户必须选择提货日期、投递日期、投递时间和投递时间。我的逻辑工作正常并防止时间重叠，但我试图弄清楚如何允许用户选择相同的日期，然后比较时间。这是我的 HTML 代码:

<tr>
  <td>
    <label>Pick up Date</label>
    <input name="DateFrom" id="DateFrom" type="text" size="10" maxlength="10" value="">
    <label>Pick up Time</label>
    <input name="TimeFrom" id="TimeFrom" type="text" size="10" maxlength="10" value="">
  </td>
</tr>

<tr>
  <td>
   <label>Drop off Date</label>
   <input name="DateTo" id="DateTo" type="text" size="10" maxlength="10" value="">
   <label>Drop off Time</label>
   <input name="TimeTo" id="TimeTo" type="text" size="10" maxlength="10" value="">
  </td>
</tr>

这是我的传递参数的逻辑:

<cfargument name="DateFrom" type="string" required="yes">
<cfargument name="DateTo" type="string" required="yes">
<cfargument name="TimeFrom" type="string" required="yes">
<cfargument name="TimeTo" type="string" required="yes">

<cfquery name="qryTest">
   Select PickDate,DropDate,PickTime,DropTime
   From ptReservation
   Order by PickDate
</cfquery>

<cfif (arguments.TimeTo LT qryTest.PickTime OR qryTest.DropTime LT arguments.TimeFrom)>
    <cfquery name="addReservation" datasource="testData">
       //Do insert
    </cfquery>
</cfif>

例如，我的代码将防止时间重叠，但如果时间不在同一日期怎么办？如何首先检查日期是否匹配，然后检查时间重叠？如果有人可以帮忙，请告诉我。

Answer 1

坦率地说，将日期和时间存储在单独的列中使这项任务比我所需要的更加困难。在预订系统中，两者是紧密相关的。将它们分开只会使执行常见任务变得更加困难，例如检查冲突或重叠(如您最近的线程所支持的)。除非有充分的理由必须将它们分开，否则我建议重组表并将预订日期和时间存储在单列中。

例如，使用这样的结构，您可以轻松地在单个查询中检查冲突的预订，甚至是跨越多天的预订:

添加新记录时，仅在未找到冲突的保留时才使用 EXISTS 子句插入记录:

   <!--- example of reservation date/time to check --->
   <cfset requestedStartDateTime = createDateTime(2016, 1, 14, 14, 0, 0)>
   <cfset requestedEndDateTime = createDateTime(2016, 1, 15, 16, 0, 0)>
   ...

   <cfquery result="yourResult" ....>
    INSERT INTO Reservation ( startDateTime, endDateTime, .... )
    SELECT <cfqueryparam value="#requestedStartDateTime#" cfsqltype="cf_sql_timestamp">
            , <cfqueryparam value="#requestedEndDateTime#" cfsqltype="cf_sql_timestamp">
            , ....
    WHERE NOT EXISTS 
          (
            <!--- existing reservation that would overlap --->
            SELECT  reservationID
            FROM    reservation
            WHERE   startDateTime < <cfqueryparam value="#requestedEndDateTime#" cfsqltype="cf_sql_timestamp">
            AND     endDateTime > <cfqueryparam value="#requestedStartDateTime#" cfsqltype="cf_sql_timestamp">
          )
   </cfquery>

您可以通过检查 cfquery 的 result 属性来确定结果。如果 result.recordcount > 0，则插入新的预留。否则，您就知道发现了冲突并可以采取相应的措施。使用CF9/11和MySQL5.6进行测试。

   <cfif yourResult.recordCount gt 0>
       SUCCESS: New reservation added 
   <cfelse>
       ERROR: Conflict detected. INSERT failed.
   </cfif>

更新:如果您的表包含某种自动递增列，另一个选项是使用返回的新 ID 值 as part of the "result" structure 。如果存在则插入成功。否则，就失败了。请注意，在 CF9 等旧版本中，ID 键名称是特定于数据库的，即 MySQL - "generated_key"。从 CF10+ 开始，您可以使用通用 key 名称 generateKey。

   <!--- ID was generated --->
   <cfif structKeyExists(yourResult, "generated_key")>
       SUCCESS: New reservation added 
   <cfelse>
       ERROR: Conflict detected. INSERT failed.
   </cfif>

如果你绝对不能改变表结构，另一种可能是创建一个 VIEW或 COMPUTED COLUMN (如果您的数据库支持)在单个列中包含组合的日期和时间。这至少会简化查询这两个值，并允许您使用上述技术来解决您的问题。然而，虽然它会简化 SQL，但它们并不总是 SARGable 。因此，对于大型数据集，它们的性能可能不如单个索引列。