mysql - 检查两个时间是否重叠(如果它们在同一天)

Question

我有下表。

CREATE TABLE IF NOT EXISTS mydb.Hours (
  Date DATE NULL,
  Start VARCHAR(5) NULL,
  End VARCHAR(5) NULL,
  Courses_ID INT NOT NULL,
  Classroom_ID INT NOT NULL,
  PRIMARY KEY (Courses_ID, Classroom_ID),
  INDEX fk_Hours_Classroom1_idx (Classroom_ID ASC),
  CONSTRAINT fk_Hours_Courses1
    FOREIGN KEY (Courses_ID)
    REFERENCES mydb.Courses (ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
 CONSTRAINT fk_Hours_Classroom1
   FOREIGN KEY (Classroom_ID)
   REFERENCES mydb.Classroom (ID)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB

我想在插入之前检查是否已经有分配给当天教室的类(class)，其时间可能与新条目重叠。

我不确定如何创建一个 BEFORE INSERT 触发器来获取该日期的教室时间并检查时间是否重叠，因为它们只是长度为 5 的字符串。

<小时/>

CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`Hours_BEFORE_INSERT` BEFORE INSERT ON `Hours` FOR EACH ROW
BEGIN
    if exists(select 1
    from Hours h
    where h.Classroom_ID = new.Classroom_ID
      and h.Date = new.Date
      and h.Start <= new.End
      and h.End   >= new.Start) then
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Room is already booked at that time';
      end if;
END

Answer 1

您可以检查是否存在重叠:

select 1
from Hours h
where h.Classroom_ID = @Classroom_ID
  and h.Date = @Date
  and h.Start <= @End
  and h.End   >= @Start;

或者运行条件插入:

insert into Hours (Date, Start, End, Courses_ID, Classroom_ID)
    select @Date, @Start, @End, @Courses_ID, @Classroom_ID
    from dual
    where not exists (
        select *
        from Hours h
        where h.Classroom_ID = @Classroom_ID
          and h.Date = @Date
          and h.Start <= @End
          and h.End   >= @Start;
    );

然后检查是否已插入任何行。

注意:将@变量替换为新记录的值。

您还可以检查触发器中的重叠并引发错误:

BEGIN
    IF EXISTS (
            select *
            from Hours h
            where h.Classroom_ID = new.Classroom_ID
              and h.Date = new.Date
              and h.Start <= new.End
              and h.End   >= new.Start
        )
        THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'course time overlap'
    END IF;
END

请注意，您可能还需要编写类似的 UPDATE 触发器。

另请注意，您可能想要更改 <=和>=至<和> ，如果您想允许在一分钟内重叠(例如 11:30-12:30 和 12:30-13:30)。