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