MySQL5.7如何仅通过日期列实现日期范围表的区间更新

前提规则 #

表采用起始日期标记法存储区间：每条记录的有效日期为[start_date, 下一条记录start_date - 1天]，最后一条记录的有效期到业务定义的最大日期。方案仅需传入待更新开始日期、结束日期、目标价格三个参数，自动完成区间拆分、旧数据清理、新段插入、同价相邻段合并。

注意：以下所有操作必须在InnoDB事务中执行，避免中间状态导致数据错乱，执行校验无误后再提交，出错直接回滚。

-- 三个业务入参，按需修改即可
SET @update_start = '2022-10-08'; -- 待更新区间开始日期
SET @update_end = '2022-10-12';   -- 待更新区间结束日期
SET @target_price = 30;           -- 更新后的目标价格
SET @next_day_after_end = DATE_ADD(@update_end, INTERVAL 1 DAY); -- 计算更新区间结束日的次日，用于右边界截断

实现步骤 #

1. 开启事务 #

START TRANSACTION;

2. 截断左边界 #

如果待更新区间的起始日落入某个原有区间内部，插入该区间在更新起始日位置的拆分标记，保证更新起始日之前的原有数据不受影响：

INSERT INTO calendar (start_date, data_id, price)
SELECT @update_start, data_id, price
FROM calendar
WHERE start_date < @update_start
ORDER BY start_date DESC
LIMIT 1
AND (
    SELECT MIN(start_date) FROM calendar c2 WHERE c2.start_date > calendar.start_date
) > @update_start OR (
    SELECT MIN(start_date) FROM calendar c2 WHERE c2.start_date > calendar.start_date
) IS NULL;

3. 截断右边界 #

如果待更新区间的结束日的次日落入某个原有区间内部，插入该区间在该位置的拆分标记，保证更新结束日之后的原有数据不受影响：

INSERT INTO calendar (start_date, data_id, price)
SELECT @next_day_after_end, data_id, price
FROM calendar
WHERE start_date < @next_day_after_end
ORDER BY start_date DESC
LIMIT 1
AND (
    SELECT MIN(start_date) FROM calendar c2 WHERE c2.start_date > calendar.start_date
) > @next_day_after_end OR (
    SELECT MIN(start_date) FROM calendar c2 WHERE c2.start_date > calendar.start_date
) IS NULL;

4. 清理被更新区间完全覆盖的旧数据 #

所有start_date落在更新区间范围内的旧记录，全部属于要被替换的片段，直接删除：

DELETE FROM calendar
WHERE start_date >= @update_start
AND start_date <= @update_end;

5. 插入新价格区间 #

INSERT INTO calendar (start_date, data_id, price)
VALUES (@update_start, 3, @target_price);
-- 若需要保留原区间的data_id，可将上面的3替换为子查询：(SELECT data_id FROM calendar WHERE start_date < @update_start ORDER BY start_date DESC LIMIT 1)

6. 合并相邻同价区间 #

自动判断新插入的区间和左右相邻区间价格是否一致，一致则删除冗余记录完成合并：

-- 合并左侧同价段：如果左侧相邻区间价格和目标价一致，删除新插入的冗余段
DELETE FROM calendar
WHERE start_date = @update_start
AND price = @target_price
AND EXISTS (
    SELECT 1 FROM calendar c_left
    WHERE c_left.start_date < @update_start
    AND c_left.price = @target_price
    ORDER BY c_left.start_date DESC LIMIT 1
);

-- 合并右侧同价段：如果右侧相邻区间价格和目标价一致，删除右侧冗余段
DELETE FROM calendar
WHERE start_date = @next_day_after_end
AND price = @target_price
AND EXISTS (
    SELECT 1 FROM calendar c_new
    WHERE c_new.start_date <= @update_end
    AND c_new.price = @target_price
    ORDER BY c_new.start_date DESC LIMIT 1
);

7. 提交/回滚事务 #

校验查询结果符合预期后执行提交，否则执行回滚：

-- 结果正确执行
COMMIT;
-- 结果错误执行
-- ROLLBACK;

效果验证 #

用示例数据走完整流程，最终结果和预期完全一致：

注意事项 #

• 必须给start_date字段建立索引，否则相邻记录查询的性能会随数据量增长快速下降
• 逻辑兼容所有边界场景：更新区间完全覆盖多段旧数据、更新区间和旧区间边界完全对齐、更新后和左右段价格相同自动合并、更新区间跨多个旧区间等
• 不要尝试用单条UPDATE语句实现该需求，区间拆分涉及新增和删除操作，单条DML无法覆盖所有逻辑

内容的提问来源于stack exchange，提问作者Phyron