如何用单条MySQL语句实现PizzaDay表的增删改同步？

先处理表结构（逻辑删除场景） #

如果需要保留历史记录，推荐添加逻辑删除字段：

ALTER TABLE PizzaDay ADD COLUMN IsDeleted TINYINT(1) NOT NULL DEFAULT 0;

方案1：逻辑删除（保留历史） #

通过CTE定义新调度数据，在一个事务内完成「标记旧记录为已删除」和「插入/更新新记录」的操作，确保原子性：

START TRANSACTION;

WITH NewPizzaDays AS (
    -- 替换为你的动态调度数据，支持任意数量的记录
    SELECT 
        1 AS Id,
        NOW(3) AS CreatedOn,
        NOW(3) AS ModifiedOn,
        '2024-06-01 00:00:00.000' AS StartDate,
        '2024-06-02 23:59:59.999' AS EndDate
    UNION ALL
    SELECT 
        1 AS Id,
        NOW(3) AS CreatedOn,
        NOW(3) AS ModifiedOn,
        '2024-06-03 00:00:00.000' AS StartDate,
        '2024-06-04 23:59:59.999' AS EndDate
)
-- 标记该Id下不在新调度中的记录为已删除
UPDATE PizzaDay pd
LEFT JOIN NewPizzaDays npd 
    ON pd.Id = npd.Id 
    AND pd.StartDate = npd.StartDate 
    AND pd.EndDate = npd.EndDate
SET pd.IsDeleted = 1, pd.ModifiedOn = NOW(3)
WHERE pd.Id = 1
AND npd.Id IS NULL;

-- 插入新记录，若主键冲突则更新现有记录
INSERT INTO PizzaDay (Id, CreatedOn, ModifiedOn, StartDate, EndDate, IsDeleted)
SELECT Id, CreatedOn, ModifiedOn, StartDate, EndDate, 0 FROM NewPizzaDays
ON DUPLICATE KEY UPDATE 
    ModifiedOn = VALUES(ModifiedOn),
    IsDeleted = 0;

COMMIT;

方案2：物理删除（直接清除旧数据） #

若无需保留历史，可在事务内直接删除旧记录再插入新数据：

START TRANSACTION;

-- 删除指定Id的所有旧调度记录
DELETE FROM PizzaDay WHERE Id = 1;

-- 插入新的调度记录
INSERT INTO PizzaDay (Id, CreatedOn, ModifiedOn, StartDate, EndDate)
VALUES
    (1, NOW(3), NOW(3), '2024-06-01 00:00:00.000', '2024-06-02 23:59:59.999'),
    (1, NOW(3), NOW(3), '2024-06-03 00:00:00.000', '2024-06-04 23:59:59.999');

COMMIT;

核心要点 #

• 动态数据适配：把示例中的静态数据替换为业务生成的动态数据（比如程序参数化传入的记录集）。
• 原子性保障：事务确保两个操作要么全部成功，要么全部回滚，避免出现数据部分更新的异常。
• 主键冲突处理：ON DUPLICATE KEY UPDATE会匹配表的复合主键（Id+StartDate+EndDate），自动处理「记录已存在则更新，不存在则插入」的逻辑。

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