mysql - 租车SQL查询

Question

表：出租

rent_id       |    plateNum  |    Dispatch_Date   |     Return_Date   |   customer_id
-------------------------------------------------------------------------------------
001           |     1        |  1-10-2019 2:35pm  | 10-10-2019 9:00am |    2
002           |     2        | 12-10-2019 1:00am  | 18-10-2019 9:00am |    4
003           |     2        |  15-10-2019 1:35pm | 17-10-2019 9:00am |    6

我的问题是我想避免发生冲突，例如rent_id 2的车是从12-10-2019租出到18-10-2019，我想防止rent_id 003预订冲突的时间，所以客户只能预订车从2019年10月20日开始

有人可以帮我编写SQL来防止上述表结构的侵害吗？我正在使用MySQL作为数据库引擎。提前致谢。

Answer 1

我相信您应该在表中输入BEFORE INSERT触发器。
这是一个小DEMO。

CREATE TRIGGER rentcheck 
BEFORE INSERT ON loans 
FOR EACH ROW 
begin

-- I have used this variables to go out from the loop. Maybe there is a better solution
declare done int; 
declare done2 int;

DECLARE begin_date DATE;
DECLARE end_date DATE;

declare my_cursor cursor for 
select date_taken, date_return 
from loans 
where car_id = new.car_id;

--the loop will end when there is no more data in cursor
select count(*) from ( 
select date_taken, date_return
from loans 
where car_id = 100) z
into done2;

set done = 0;

open my_cursor;

  simple_loop: LOOP


    FETCH my_cursor INTO begin_date, end_date;

    --here I have inserted conditions your date columns should satisfy before inserting
    if (new.date_taken >= begin_date and new.date_taken <= end_date) 
       or (new.date_return >= begin_date and new.date_return <= end_date) 
       or (begin_date between new.date_taken and new.date_return) then

       signal sqlstate '45000';

    else 
       set done = done + 1;
       if done = done2 then 
          leave simple_loop;
       end if;
    end if;

  end loop simple_loop;

close my_cursor;

end;
DELIMITER ;