SQL批量INSERT数据时如何避免主键冲突

在MySQL中，INSERT IGNORE堪称处理冲突的最简方案，它能在面对主键或唯一索引冲突时，悄无声息地忽略，既不报错，也不更新，更不会加锁；而在PostgreSQL里，对应的是ON CONFLICT (列) DO NOTHING，但它需要明确指定冲突目标，语法要求更为严格；SQL Server则要借助MERGE来模拟，不过这样做开销较大，还容易引发争抢。

INSERT IGNORE 是最轻量、最直接的解法，尤其适合“只插入新数据、跳过已存在记录”的场景。它不加锁、不更新、不报错，真正实现静默跳过。

MySQL 用 INSERT IGNORE 跳过冲突行

当表有 PRIMARY KEY 或 UNIQUE 索引时，INSERT IGNORE 会把违反约束的行直接忽略，其余行照常插入。

• 常见错误现象：ERROR 1062 (23000): Duplicate entry 'xxx' for key 'PRIMARY' —— 这说明你没加任何容错，直接用了裸 INSERT
• 执行示例：INSERT IGNORE INTO users (id, name) VALUES (1,'Alice'),(2,'Bob'),(1,'Charlie'); → 第二个 (1,'Charlie') 被跳过，不报错也不插入
• 注意：它只忽略由唯一键或主键冲突引发的错误；字段超长、NOT NULL 插 NULL 等仍会报错并中止整条语句
• 返回值中 rows affected 少于 VALUES 行数，差值就是被忽略的重复行数，可用于统计去重效果

PostgreSQL 用 ON CONFLICT DO NOTHING 精准控制

PostgreSQL 没有 IGNORE，但 ON CONFLICT 更明确——必须指定冲突依据（列名或约束名），不能省略 DO NOTHING。

• 使用场景：你想按 email 去重，而表上有 CREATE UNIQUE INDEX idx_users_email ON users(email);
• 写法示例：INSERT INTO users (id, email) VALUES (1,'a@b.com'),(2,'c@d.com') ON CONFLICT (email) DO NOTHING;
• 更安全的写法是显式引用约束名：ON CONFLICT ON CONSTRAINT idx_users_email DO NOTHING，避免列名歧义
• 如果漏写 DO NOTHING，语法直接报错；也不能写成 DO UPDATE SET，否则就不是“跳过”了

SQL Server 用 MERGE 模拟跳过逻辑

MERGE 不是 INSERT 变体，而是匹配驱动型语句。想“只插入不更新”，就得严格限定分支逻辑。

• 必须写全 WHEN NOT MATCHED THEN INSERT，且不能带 WHEN MATCHED 分支，否则可能误触发更新或失败
• 源数据要用 USING (VALUES ...) 构造，例如：MERGE users AS t USING (VALUES (1,'Alice'),(2,'Bob')) AS s(id,name) ON t.id = s.id WHEN NOT MATCHED THEN INSERT (id,name) VALUES (s.id,s.name);
• 目标表必须有唯一索引（如 id 主键），否则 ON 条件无法保证单行匹配，行为不可控
• 别指望 MERGE 像 INSERT IGNORE 那样轻量——它涉及匹配扫描、锁资源，高并发下仍可能争抢

为什么别用 ON DUPLICATE KEY UPDATE 来“跳过”？

它本质是“查+锁+可能更新”，开销远高于跳过需求本身，还容易卡住。

• 主键冲突时不报错而是卡住，是因为它在检测前就加了 X lock 和 insert intention lock，多个事务插入相同键会互相等待
• 即使写 ON DUPLICATE KEY UPDATE id=id 这种伪更新，照样锁行、照样触发索引维护，毫无性能优势
• 批量插入时若 VALUES 中主键乱序，InnoDB 间隙锁会交叉加锁，引发死锁风险；必须提前在应用层 ORDER BY id ASC
• 真需要更新字段时，建议改用 SELECT ... FOR UPDATE 显式加锁再 UPDATE，逻辑更可控

实际批量插入时，最容易被忽略的是**批量内部的主键顺序**和**数据库方言差异**：MySQL 的 INSERT IGNORE 不关心顺序，但 SQL Server 的 MERGE 和 PostgreSQL 的 ON CONFLICT 对唯一性判断都依赖底层索引结构，一旦建表时多加了二级唯一索引，就可能引入额外锁竞争。