预订日历到达和离开日期

考虑以下…

DROp TABLE IF EXISTS my_table;CREATE TABLE my_table(book_id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,arrive_date DATE NOT NULL,depart_date DATE NOT NULL);INSERT INTO my_table VALUES(1,'2015-07-20','2015-07-22'),(2,'2015-07-22','2015-07-23'),(3,'2015-07-19','2015-07-20');SELECT * FROM my_table;+---------+-------------+-------------+| book_id | arrive_date | depart_date |+---------+-------------+-------------+|       1 | 2015-07-20  | 2015-07-22  ||       2 | 2015-07-22  | 2015-07-23  ||       3 | 2015-07-19  | 2015-07-20  |+---------+-------------+-------------+

因此，SELECt可能看起来像这样…

SELECT '2015-07-22','2015-07-24'   FROM (SELECt 1) x   LEFT   JOIN my_table y    ON y.arrive_date < '2015-07-24' AND y.depart_date > '2015-07-22' WHERe y.book_id IS NULL  LIMIT 1;Empty set (0.00 sec)SELECT '2015-07-23','2015-07-24'   FROM (SELECt 1) x   LEFT   JOIN my_table y    ON y.arrive_date < '2015-07-24' AND y.depart_date > '2015-07-23' WHERe y.book_id IS NULL  LIMIT 1;+------------+------------+| 2015-07-23 | 2015-07-24 |+------------+------------+| 2015-07-23 | 2015-07-24 |+------------+------------+

…但是您不需要先检查它们。我刻意编写了SELECTs，使得检查可以作为INSERT的一部分进行。

INSERT INTO my_table (arrive_date,depart_date)SELECT '2015-07-23','2015-07-24'   FROM (SELECt 1) x   LEFT   JOIN my_table y    ON y.arrive_date < '2015-07-24' AND y.depart_date > '2015-07-23' WHERe y.book_id IS NULL  LIMIT 1;SELECT * FROM my_table;+---------+-------------+-------------+| book_id | arrive_date | depart_date |+---------+-------------+-------------+|       1 | 2015-07-20  | 2015-07-22  ||       2 | 2015-07-22  | 2015-07-23  ||       3 | 2015-07-19  | 2015-07-20  ||       4 | 2015-07-23  | 2015-07-24  |+---------+-------------+-------------+

实际上，根据您要提供的用户体验，您可能需要先运行SELECT，以便用户可以立即查看哪些日期不可用，然后在进行预订时运行INSERT-
以确保在用户预订过程中没有人抓住这些日期。