检查表格的时间重叠?

2022-01-24 00:00:00 time sql range overlap mysql

我有一个包含以下字段的 MySQL 表:

I have a MySQL table with the following fields:

  • 姓名
  • 开始时间
  • 结束时间

starttime 和 endtime 是 MySQL TIME 字段(不是 DATETIME).我需要一种定期扫描"表格的方法，以查看表格内的时间范围是否有任何重叠.如果有一个来自 10:00-11:00 的事件和另一个来自 10:30-11:30 的事件，我想提醒我存在时间重叠.

starttime and endtime are MySQL TIME fields (not DATETIME). I need a way to periodically "scan" the table to see if there are any overlaps in time ranges within the table. If there is an event from 10:00-11:00 and another from 10:30-11:30, I want to be alerted of the presence of the time overlap.

没什么特别的，我只想知道是否存在重叠.

Nothing fancy really, all I want to know whether an overlap exists or not.

我将使用 PHP 来执行此操作.

I'm going to be using PHP to execute this.

推荐答案

这是我多年前找到答案的查询模式:

This is a query pattern for which I found the answer many years ago:

SELECT *
FROM mytable a
JOIN mytable b on a.starttime <= b.endtime
    and a.endtime >= b.starttime
    and a.name != b.name; -- ideally, this would compare a "key" column, eg id

要找到任何重叠"，您需要将时间范围的相反端相互比较.这是我必须拿出笔和纸并画出相邻范围才能意识到边缘情况归结为这种比较的东西.

To find "any overlap", you compare the opposite ends of the timeframe with each other. It's something I had to get a pen and paper out for and draw adjacent ranges to realise that the edge cases boiled down to this comparison.

如果您想防止任何行重叠，请将此查询的变体放入触发器中:

If you want to prevent any rows from overlapping, put a variant of this query in a trigger:

create trigger mytable_no_overlap
before insert on mytable
for each row
begin
  if exists (select * from mytable
             where starttime <= new.endtime
             and endtime >= new.starttime) then
    signal sqlstate '45000' SET MESSAGE_TEXT = 'Overlaps with existing data';
  end if;
end;

相关文章