检查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 sessionWHERE "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 WHERE

existing_start BETWEEN $newStart AND $newEnd OR 

existing_end BETWEEN $newStart AND $newEnd OR

$newStart BETWEEN existing_start AND existing_end

if (!empty($result))

throw new Exception('We have overlapping')

这3行sql子句涵盖了4个需要重叠的情况。

Answer 2

你可以多一点优化它。

SELECT * FROM tbl WHEREexisting_start BETWEEN $newSTart AND $newEnd OR$newStart BETWEEN existing_start AND existing_end

这将捕获范围重叠的所有四种情况，并排除它们不重复的两种情况。