MySQL两个百万记录表按SKU匹配条件实现插入或更新方案咨询

实现前提 #

首先需要给dumpdata表的SKU字段添加唯一索引，这是实现「SKU匹配则更新、不匹配则插入」逻辑的基础，只有唯一键冲突才能触发后续的更新动作：

ALTER TABLE `dumpdata` ADD UNIQUE INDEX `idx_sku_unique` (`SKU`);

注：MySQL的唯一索引允许多个NULL值共存，如果你的业务中SKU为NULL也需要做匹配更新，可以自行调整字段为非NULL再建索引。

方案1：单语句UPSERT（适合可接受短时间锁表的场景） #

直接使用MySQL原生的INSERT ... ON DUPLICATE KEY UPDATE语法，完全匹配你的需求：

INSERT INTO `dumpdata` (`QTY`, `SKU`)
SELECT `QTY`, `SKU` FROM `dumpdata_temp`
ON DUPLICATE KEY UPDATE `QTY` = 1;

注意：这里不要读取dumpdata_temp的TEST_ID字段，dumpdata的自增主键会自行生成，避免主键冲突问题。

方案2：分批次处理（适合百万级大数据量、生产环境避免长时间锁表的场景） #

如果数据量过大，单条语句执行会锁表过久影响业务，可以按主键范围分段批量执行，每次处理1000~10000条：

1. 先获取dumpdata_temp的最大主键值：

SELECT MAX(TEST_ID) FROM `dumpdata_temp`;

2. 循环执行以下语句，逐次偏移处理：

-- 每次处理1000条，@offset从0开始逐次加1000，直到超过上述查询到的最大TEST_ID
INSERT INTO `dumpdata` (`QTY`, `SKU`)
SELECT `QTY`, `SKU` FROM `dumpdata_temp` 
WHERE TEST_ID BETWEEN @offset AND @offset + 999
ON DUPLICATE KEY UPDATE `QTY` = 1;

操作注意事项 #

• 执行前务必先备份dumpdata表，避免误操作导致数据丢失
• 建议先在测试环境验证逻辑正确性，确认符合预期再到生产环境执行
• 大数据量写入前可以临时关闭非必要的普通索引，写入完成后再重建，可大幅提升执行效率

内容的提问来源于stack exchange，提问作者TSCAmerica.com