考虑以下…
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-
以确保在用户预订过程中没有人抓住这些日期。
