我有两个代表出租单位和预订的 MySQL (MyIsAm) 表:

• 出租单位(ID、名称 等...)
• LettingUnitBookings(ID、F_LU_ID、开始、结束)

其中 F_LU_ID 是单元的外键。

搜索在特定时间范围内可用的单元的最佳方法是什么？搜索通过开始、结束和持续时间。

• 开始 = 最早开始预订
• 结束 = 预订的最晚结束时间
• 持续时间 = 预订持续时间

我很想知道是否可以在 MySQL 中执行此操作，但如果不能，那么最好的方法是在 PHP 中执行此操作。

示例

在回答下面的答案时，我觉得一个例子将有助于解释问题。

出租单位:

• (123, "福堂")

一些 LettingUnitBookings:

• (400, 123, 01/01/09, 05/01/09) - 5 天预订
• (401, 123, 10/01/09, 20/01/09) - 10 天预订
• (402、123、25/01/09、30/01/09) - 5 天预订

如果我们搜索:

• 开始 = 01/01/09
• 结束 = 01/02/09
• 持续时间 = 5(天)

然后我们希望单位出现。因为在搜索范围内有 5 天预订的空房。

如果持续时间为 10，则该单元不会显示，因为搜索范围内没有连续 10 个未预订的天数。

最佳答案

这是一个似乎可行的解决方案:

SELECT t.*, DATEDIFF(t.LatestAvailable, t.EarliestAvailable) AS LengthAvailable
FROM 
   (SELECT u.*,
      COALESCE(b1.End, @StartOfWindow) AS EarliestAvailable,
      COALESCE(b2.Start, @EndOfWindow) AS LatestAvailable
    FROM LettingUnits u
    LEFT OUTER JOIN LettingUnitBookings b1
      ON (u.ID = b1.F_LU_ID AND b1.End BETWEEN @StartOfWindow AND @EndOfWindow)
    LEFT OUTER JOIN LettingUnitBookings b2
      ON (u.ID = b2.F_LU_ID AND b2.Start BETWEEN @StartOfWindow AND @EndOfWindow
          AND b2.Start >= b1.End) -- edit: new term
    ) AS t
LEFT OUTER JOIN LettingUnitBookings x
  ON (t.ID = x.F_LU_ID AND x.Start < t.LatestAvailable AND x.End > t.EarliestAvailable)
WHERE x.ID IS NULL AND DATEDIFF(t.LatestAvailable, t.EarliestAvailable) >= @WindowSize;

输出是:

+-----+-------------+-------------------+-----------------+-----------------+
| ID  | Name        | EarliestAvailable | LatestAvailable | LengthAvailable |
+-----+-------------+-------------------+-----------------+-----------------+
| 123 | Foo Cottage | 2009-01-05        | 2009-01-10      |               5 |
| 123 | Foo Cottage | 2009-01-20        | 2009-01-25      |               5 |
| 456 | Bar Cottage | 2009-01-20        | 2009-01-31      |              11 |
+-----+-------------+-------------------+-----------------+-----------------+

用 EXPLAIN 分析表明它很好地使用了索引:

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra                   |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL  |    9 | Using where             |
|  1 | PRIMARY     | x          | ref    | F_LU_ID       | F_LU_ID | 8       | t.ID  |    2 | Using where; Not exists |
|  2 | DERIVED     | u          | system | NULL          | NULL    | NULL    | NULL  |    1 |                         |
|  2 | DERIVED     | b1         | ref    | F_LU_ID       | F_LU_ID | 8       | const |    0 |                         |
|  2 | DERIVED     | b2         | ref    | F_LU_ID       | F_LU_ID | 8       | const |    0 |                         |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------------------+

与@martin clayton 的解决方案 given 的EXPLAIN 报告比较:

+----+--------------+---------------------+--------+---------------+---------+---------+------+------+---------------------------------+
| id | select_type  | table               | type   | possible_keys | key     | key_len | ref  | rows | Extra                           |
+----+--------------+---------------------+--------+---------------+---------+---------+------+------+---------------------------------+
|  1 | PRIMARY      | lu                  | system | PRIMARY,ID    | NULL    | NULL    | NULL |    1 |                                 |
|  1 | PRIMARY      | <derived2>          | ALL    | NULL          | NULL    | NULL    | NULL |    4 | Using where                     |
|  2 | DERIVED      | <derived3>          | ALL    | NULL          | NULL    | NULL    | NULL |    4 | Using temporary; Using filesort |
|  2 | DERIVED      | <derived5>          | ALL    | NULL          | NULL    | NULL    | NULL |    4 | Using where; Using join buffer  |
|  5 | DERIVED      | LettingUnitBookings | ALL    | NULL          | NULL    | NULL    | NULL |    3 |                                 |
|  6 | UNION        | LettingUnitBookings | index  | NULL          | F_LU_ID | 8       | NULL |    3 | Using index                     |
| NULL | UNION RESULT | <union5,6>          | ALL    | NULL          | NULL    | NULL    | NULL | NULL |                                 |
|  3 | DERIVED      | LettingUnitBookings | ALL    | NULL          | NULL    | NULL    | NULL |    3 |                                 |
|  4 | UNION        | LettingUnitBookings | index  | NULL          | F_LU_ID | 8       | NULL |    3 | Using index                     |
| NULL | UNION RESULT | <union3,4>          | ALL    | NULL          | NULL    | NULL    | NULL | NULL |                                 |
+----+--------------+---------------------+--------+---------------+---------+---------+------+------+---------------------------------+

通常，您希望避免强制执行 Using filesort 或 Using temporary 的优化计划，因为这些都是性能 killer 。使用 GROUP BY 的查询几乎肯定会导致这种优化，至少在 MySQL 中是这样。

关于php - 使用 MySQL(和 PHP)搜索可用性？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/1687519/