商品表字段批量插入时如何避免重复数据？

Question

在批量插入商品表数据时，如何有效避免因重复商品编码或名称导致的数据冗余？常见场景如下：系统从多个渠道同步商品信息，若未校验唯一键（如商品条码、SKU编码），极易造成重复插入。直接使用 INSERT 忽略主键或唯一索引冲突可能导致性能下降，而先查后插（SELECT + INSERT）在高并发下仍可能产生重复。如何在保证性能的同时，通过数据库约束、INSERT IGNORE、ON DUPLICATE KEY UPDATE 或临时表中转等方式实现高效去重，成为批量操作中的关键技术难点。

Answer 1

一、问题背景与核心挑战

在现代电商平台或ERP系统中，商品数据往往来自多个外部渠道（如供应商接口、第三方平台同步、手工导入等）。当进行批量插入操作时，若未对商品编码（如SKU）、条码（EAN/UPC）或商品名称等唯一性字段进行有效校验，极易导致数据冗余。

常见的问题包括：

• 重复的商品记录污染数据库，影响库存、价格和订单逻辑；
• 直接使用 INSERT IGNORE 虽可避免主键冲突，但无法灵活处理更新逻辑；
• SELECT + INSERT 模式存在并发安全问题，在高并发场景下仍可能产生重复插入；
• 缺乏统一的数据清洗机制，导致后期维护成本剧增。

因此，如何在保证高性能的前提下实现精准去重，是数据库设计与批量处理中的关键技术难点。

二、由浅入深的技术演进路径

1. 初级方案：先查后插（SELECT + INSERT）

   最直观的方式是在插入前查询是否存在相同编码的商品。例如：

   SELECT id FROM products WHERE sku = 'ABC123'; -- 若存在则跳过
   INSERT INTO products (sku, name, price) VALUES ('ABC123', '手机', 2999);

   缺点明显：两次数据库交互增加延迟，且在并发环境下无法防止竞态条件（Race Condition），两个线程同时查询“不存在”并执行插入，造成重复。

2. 中级方案：利用唯一索引约束 + INSERT IGNORE

   为商品表的 SKU 字段建立唯一索引：

   ALTER TABLE products ADD UNIQUE INDEX uk_sku(sku);

   然后使用 INSERT IGNORE 自动忽略违反唯一性约束的插入：

   INSERT IGNORE INTO products (sku, name, price) VALUES 
   ('ABC123', '手机', 2999),
   ('XYZ789', '耳机', 199);

   优点是语法简单，能有效防止重复插入；但缺点在于无法区分“新插入”与“被忽略”的情况，也不支持自动更新已有记录。

3. 进阶方案：ON DUPLICATE KEY UPDATE 实现智能合并

   更进一步，可以使用 MySQL 的 ON DUPLICATE KEY UPDATE 子句，在冲突时选择性地更新部分字段：

   INSERT INTO products (sku, name, price, updated_at) 
   VALUES ('ABC123', '手机', 3099, NOW())
   ON DUPLICATE KEY UPDATE 
   price = VALUES(price), 
   updated_at = NOW();

   此方式既能防止重复，又能实现“存在即更新”的语义，适用于需要同步最新价格或库存的场景。

4. 高级方案：临时表中转 + 批量比对去重

   对于大规模数据同步任务（如每日百万级商品同步），推荐采用临时表策略：

   步骤	操作说明
   1	创建临时表 temp_products，结构同主表
   2	将所有来源数据批量导入临时表
   3	通过 JOIN 或 NOT EXISTS 筛选出主表中不存在的记录
   4	执行批量插入新增数据，并对已有数据做差异更新

   示例SQL：

   INSERT INTO products (sku, name, price, created_at)
   SELECT t.sku, t.name, t.price, NOW()
   FROM temp_products t
   LEFT JOIN products p ON p.sku = t.sku
   WHERE p.sku IS NULL;

   该方法优势在于：减少锁竞争、提升吞吐量、便于审计与回滚。

三、综合解决方案架构图

graph TD
    A[多渠道商品数据] --> B{数据预处理}
    B --> C[清洗标准化: SKU/条码格式统一]
    C --> D[写入临时表 temp_products]
    D --> E[建立唯一索引加速匹配]
    E --> F[与主表 products 做 LEFT JOIN 比对]
    F --> G[插入新增记录]
    F --> H[更新变更字段]
    G --> I[提交事务]
    H --> I
    I --> J[清理临时表]

四、性能对比与选型建议

方案	并发安全性	性能表现	灵活性	适用场景
SELECT + INSERT	低	差	高	小批量、低并发
INSERT IGNORE	中	良好	低	仅防重，无需更新
ON DUPLICATE KEY UPDATE	高	优秀	中	需更新存量数据
临时表中转	极高	极佳（批处理优化）	高	大数据量同步、ETL任务

实际项目中，建议根据数据规模、频率和业务需求组合使用上述策略。例如：日常增量同步可用 ON DUPLICATE KEY UPDATE，而每日全量同步则采用临时表方案。