检查MySQL中日期范围的重叠

Question

检查MySQL中日期范围的重叠

该表用于存储会话（事件）：

CREATE TABLE session (

  id int(11) NOT NULL AUTO_INCREMENT

, start_date date

, end_date date

);

INSERT INTO session

  (start_date, end_date)

VALUES

  ("2010-01-01", "2010-01-10")

, ("2010-01-20", "2010-01-30")

, ("2010-02-01", "2010-02-15")

;

我们不希望范围之间发生冲突。

假设我们需要在2010-01-05至2010-01-25之间插入一个新会话。

我们想知道冲突的会话。

这是我的查询：

SELECT *

FROM session

WHERE "2010-01-05" BETWEEN start_date AND end_date

   OR "2010-01-25" BETWEEN start_date AND end_date

   OR "2010-01-05" >= start_date AND "2010-01-25" <= end_date

;

结果如下：

+----+------------+------------+

| id | start_date | end_date   |

+----+------------+------------+

|  1 | 2010-01-01 | 2010-01-10 |

|  2 | 2010-01-20 | 2010-01-30 |

+----+------------+------------+

有没有更好的方法来实现这一目标？

Answer 1

SELECT * FROM tbl WHEREexisting_start BETWEEN $newStart AND $newEnd OR&nbsp;existing_end BETWEEN $newStart AND $newEnd OR$newStart BETWEEN existing_start AND existing_endif (!empty($result))throw new Exception('We have overlapping')这3行sql子句涵盖了4个需要重叠的情况。

Answer 2

你可以多一点优化它。SELECT&nbsp;*&nbsp;FROM&nbsp;tbl&nbsp;WHEREexisting_start&nbsp;BETWEEN&nbsp;$newSTart&nbsp;AND&nbsp;$newEnd&nbsp;OR$newStart&nbsp;BETWEEN&nbsp;existing_start&nbsp;AND&nbsp;existing_end这将捕获范围重叠的所有四种情况，并排除它们不重复的两种情况。