MySQL插入遇重复键时迁移当前表旧数据到历史表的SQL实现咨询

前置要求 #

首先保证DATA_SELECTED_NOW和DATA_SELECTED_HISTORY的基础表结构完全一致，若需要记录归档时间可给DATA_SELECTED_HISTORY额外添加archive_time字段，默认值设为CURRENT_TIMESTAMP即可；DATA_SELECTED_NOW的主键/唯一键约束正常生效，DATA_SELECTED_HISTORY不要加主键/唯一键约束，避免历史数据重复写入报错。

方案1：事务+INSERT ON DUPLICATE KEY组合方案 #

适用场景：单条数据插入、小批量增量写入，并发度不高的场景

单条写入SQL示例 #

START TRANSACTION;

-- 1. 迁移待插入数据对应的旧记录到历史表
-- WHERE条件替换为你插入数据对应的主键/唯一键匹配条件即可
INSERT INTO DATA_SELECTED_HISTORY 
SELECT * FROM DATA_SELECTED_NOW WHERE id = [待插入数据的主键值];

-- 2. 插入新数据，重复时覆盖对应字段
INSERT INTO DATA_SELECTED_NOW (id, 字段1, 字段2, 字段N)
VALUES ([主键值], [值1], [值2], [值N])
ON DUPLICATE KEY UPDATE
字段1 = VALUES(字段1),
字段2 = VALUES(字段2),
字段N = VALUES(字段N);

COMMIT;

批量写入SQL示例 #

如果是批量写入，建议先把待写入的全部数据存入临时表tmp_insert_data（表结构和DATA_SELECTED_NOW一致），再执行以下操作：

START TRANSACTION;

-- 1. 批量迁移所有重复的旧记录
INSERT INTO DATA_SELECTED_HISTORY
SELECT n.* FROM DATA_SELECTED_NOW n
INNER JOIN tmp_insert_data t ON n.唯一键字段 = t.唯一键字段;

-- 2. 批量写入新数据
INSERT INTO DATA_SELECTED_NOW (id, 字段1, 字段2, 字段N)
SELECT id, 字段1, 字段2, 字段N FROM tmp_insert_data
ON DUPLICATE KEY UPDATE
字段1 = VALUES(字段1),
字段2 = VALUES(字段2),
字段N = VALUES(字段N);

COMMIT;

方案2：表交换方案 #

适用场景：后端算法每次生成全量最新数据，需要批量替换整个DATA_SELECTED_NOW表的高吞吐场景，对前端查询无影响

-- 1. 创建和DATA_SELECTED_NOW结构完全一致的临时表，存储新数据
CREATE TABLE tmp_new_data LIKE DATA_SELECTED_NOW;

-- 2. 写入所有新数据到临时表，这一步无锁表压力，性能极高
INSERT INTO tmp_new_data (id, 字段1, 字段2, 字段N)
VALUES (...); -- 也可以用LOAD DATA等批量导入方式

-- 3. 原子交换表，操作瞬间完成，前端不会感知到中断
RENAME TABLE 
DATA_SELECTED_NOW TO tmp_old_data,
tmp_new_data TO DATA_SELECTED_NOW;

-- 4. 迁移旧数据到历史表
INSERT INTO DATA_SELECTED_HISTORY SELECT * FROM tmp_old_data;

-- 5. 清理临时表
DROP TABLE tmp_old_data;

注意事项 #

• 方案1的所有操作必须放在事务中执行，避免中途操作失败导致数据丢失或不一致
• 方案2的RENAME TABLE本身是原子操作，不会出现中间状态，后续的历史数据迁移可以异步执行，不影响线上查询
• 如果需要保留同一条数据的多次变更记录，不要给DATA_SELECTED_HISTORY加任何主键/唯一键约束

内容的提问来源于stack exchange，提问作者안유빈