递归“重复密钥更新”
我有一个数据库表，日期列上有一个 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 来达到这种效果？
I have a database table with a UNIQUE key on a date column so that there can never be more than one row per date. If I insert a new row for a day that already exists in the database, I’d like it to push all the subsequent, consecutive dates back by one day until it reaches a ‘free’ day.
Here’s what I thought:
INSERT INTO
`activity`
(`name`,`date`)
VALUES
('date 7','2009-07-31')
ON DUPLICATE KEY
UPDATE `date` = DATE_ADD(`date`, INTERVAL '1' DAY)
My thinking is that the ON DUPLICATE KEY UPDATE will bubble up the table and keep adding one day to every date until it reaches a day that doesn’t exist.
E.g., if my table content looks like this:
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
…and I go to insert ‘date 7′ on 2009-07-31, I’d like it to do this:
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
But on duplicate key update doesn’t work like that, it only updates the clashing row, and if that row then clashes with a different row, it bails out with a duplicate key error.
Is there a smart way to achieve this effect in SQL alone?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
查询目标日期当天或之后的第一个空闲日期。这涉及左自连接来查找表中没有后继的日期。
运行更新以增加目标日期和第一个空闲日期之间范围内的每个日期。
现在有空间放置您想要的插入物。
Query for the first free date on or after your target date. This involves a left self-join to find dates with no successor in the table.
Run an update to increment each date in the range between your target date and the first free date.
Now there is room for your desired insert.
在进行更新之前选择最大日期，然后将一天添加到最大日期，
如下所示：
ON DUPLICATE KEY
select the max date before doing the update, then add one day to the max
something like that:
ON DUPLICATE KEY