sql - 递归 "ON DUPLICATE KEY UPDATE"

Question

我有一个数据库表，在日期列上有一个 UNIQUE 键，因此每个日期永远不会超过一行。如果我为数据库中已存在的某一天插入一个新行，我希望它将所有后续的连续日期往后推一天，直到它到达“空闲”日。

这是我的想法:

INSERT INTO
 `activity`
 (`name`,`date`)
VALUES
 ('date 7','2009-07-31')
ON DUPLICATE KEY
 UPDATE `date` = DATE_ADD(`date`, INTERVAL '1' DAY)

我的想法是，ON DUPLICATE KEY UPDATE 会在表中冒泡，并不断向每个日期添加一天，直到到达不存在的一天。

例如，如果我的表格内容如下所示:

date 1, 2009-07-30
date 2, 2009-07-31
date 3, 2009-08-01
date 4, 2009-08-02
date 5, 2009-08-04 <- notice this date is two days past the previous one

...然后我在 2009-07-31 插入“日期 7”，我希望它这样做:

date 1, 2009-07-30
date 7, 2009-07-31 <- newly inserted row
date 2, 2009-08-01 <- this and subsequent rows are all incremented
                      by one day until it hits a non-consecutive day
date 3, 2009-08-02
date 4, 2009-08-03
date 5, 2009-08-04 <- notice this date hasn't changed

但是重复键更新不是那样工作的，它只更新冲突的行，如果该行随后与不同的行发生冲突，它会因重复键错误而退出。

有没有一种聪明的方法可以单独在 SQL 中实现这种效果？

Answer 1

查询您的目标日期或之后的第一个空闲日期。这涉及左自连接以查找表中没有后继的日期。

SELECT DATE_ADD(Min(a.`date`), INTERVAL '1' DAY) AS `free_date`
FROM `activity` a
LEFT JOIN `activity` z
ON z.`date` = DATE_ADD(a.`date`, INTERVAL '1' DAY)
WHERE z.`date` IS NULL 
AND a.`date` >= '2009-07-31'

运行更新以递增目标日期和第一个空闲日期之间范围内的每个日期。

现在有空间放置您想要的插入物。