在mysql中搜索两个日期之间的内容

Question

我有两个表格，这两个表格的名称分别为“rooms”和“bookings”。

我现在已经将这两个表格连接起来了。当我搜索 book_form = "2016-12-30" 和 book_to = "2016-12-31" 时，我希望返回值为true，因为这两个日期不存在于“bookings”表格中；而当我搜索book_form = "2016-12-30"和book_to = "2017-01-05"或者book_form = "2017-01-03"和book_to = "2017-01-15"时，应该返回false，因为这些日期都存在于“bookings”表格中。

以下是我的查询语句。

select * from rooms join room_book on rooms.room_id = room_book.room_id where status = 'available' and room_book.book_from NOT BETWEEN '2016-12-30' AND room_book.book_to NOT BETWEEN '2016-12-31'

---

注意：抱歉，实际上在预订表中，book_from列的日期是2017-01-01。

- user7136014

---

你错过了最重要的部分，你想要做什么？ - Troyer

@Troyer，我认为这一部分很清楚；它是一个房间预订系统。我想要预订从x到y的一个房间; 它需要找到在这个范围内没有被占用的房间。实际问题在于字段数据类型不适合此项任务。即日期是文本。将日期存储为时间戳，并进行大于/小于运算变得微不足道。 - Luke Briggs

@Troyer 当然可以，但这会引起很多本地化问题（而且比较数字会慢很多）。更好的方法是使用日期/时间戳/日期时间字段来存储日期 :) - Luke Briggs

@LukeBriggs 我完全同意你的观点，当你需要存储日期时，请使用正确的字段类型，但有时我们需要做一些愚蠢的事情... 在这种情况下，CONVERT（）必须解决问题。 - Troyer

@Troyer 这次完全是全新的 - 即屏幕截图几乎展示了空表；更好的方法是正确设计一个新的数据库，而不是试图迂回解决问题。 - Luke Briggs

Answer 1

select * 
from rooms 
join room_book on rooms.room_id = room_book.room_id 
where status = 'available' 
and room_book.book_from >= '2016-12-30' 
AND room_book.book_to <= '2016-12-31'

尝试像这样。

- Naveed Ramzan

---

这将错误地执行字母顺序的字符串比较，而不是日期比较。 - Luke Briggs

@LukeBriggs 有没有一种方法可以强制MySQL在日期或时间字段上执行另一种形式的比较？ - parrker9

@parrker9，是的，请参考Troyer关于CONVERT()函数的评论 :) - Luke Briggs

Answer 2

一个简单的SQL查询应该只返回那些没有包含提供日期的预订的房间：

SELECT *
FROM rooms
LEFT JOIN room_book
    ON room_book.room_id = rooms.room_id
    AND 'search_date' BETWEEN room_book.book_from AND room_book.book_to
WHERE rooms.room_id IS NULL
AND rooms.status = 'available'

在上方替换您要搜索的日期search_date。

通过使用LEFT JOIN，您将获得rooms中的所有记录。where子句中的IS NULL测试会消除那些在room_book中没有匹配行的行。

- Sloan Thrasher