SQL INSERT SELECT如何迁移数据并防止重复-SQL-PHP中文网

主键冲突时整条insert into select会因任意一行违反唯一约束而回滚，应使用insert ignore跳过冲突行，或on duplicate key update实现可控upsert；需避免not in子查询、显式指定字段、按主键分批并确保索引以减少锁。

sql insert select如何迁移数据并防止重复

INSERT INTO SELECT 迁移时主键冲突直接报错，怎么办？

整条语句会因任意一行违反唯一约束而回滚，百万数据里混一条脏数据就全废。不能靠“先清空再插”这种粗暴方式，线上表根本禁不起。

INSERT IGNORE INTO 最省事：冲突行静默跳过，其他正常插入；但会忽略所有警告（比如字段截断），上线前务必 SHOW WARNINGS 检查
INSERT ... ON DUPLICATE KEY UPDATE 更可控：用 id=id 空更新实现“只插不改”，或指定字段更新；注意它会触发行锁升级，高并发下可能死锁
真正要审计冲突行？得拆成两步：SELECT ... FROM source LEFT JOIN target ON ... WHERE target.id IS NULL，再插——别图省事写成 NOT IN (SELECT id FROM target)，只要 target.id 有 NULL 就全失效

WHERE 条件里嵌子查询，为什么慢到卡死？

写成 INSERT INTO t2 SELECT * FROM t1 WHERE id IN (SELECT id FROM t3)，MySQL 5.7 默认对 t1 每行都执行一次子查询，100 万行 = 100 万次独立扫描。

立刻改写为 JOIN：INSERT INTO t2 SELECT t1.* FROM t1 INNER JOIN t3 ON t1.id = t3.id
t3.id 必须有索引，否则 JOIN 也走全表；用 EXPLAIN 看 type 列是不是 ref 或 eq_ref
别信“小结果集就没事”——优化器不保证子查询物化，生产环境一律禁止在 WHERE 里写子查询

INSERT 后接 DELETE，数据却对不上？

两条语句默认不原子，中间出错或并发写入，轻则漏删，重则双写。不是语法问题，是事务意识缺失。

必须用 BEGIN / COMMIT 包裹：BEGIN; INSERT INTO ... SELECT ...; DELETE FROM ... WHERE ...; COMMIT;
DELETE 的 WHERE 条件必须和前面 INSERT 的 WHERE 完全一致，字段、值、括号层级都不能差
别用 SELECT * 迁移——目标表字段顺序、默认值、自增列行为稍有差异，就会导致 Column count doesn't match value count

分批迁移时 LIMIT 报语法错误？

INSERT INTO t2 SELECT * FROM t1 LIMIT 1000 在 MySQL 5.7 及更早版本直接报错：“This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’”，这不是性能问题，是硬性语法限制。

绕过方法：用派生表 INSERT INTO t2 SELECT * FROM (SELECT * FROM t1 WHERE id > 1000000 ORDER BY id LIMIT 1000) AS tmp
但更稳的方案是按主键范围切分：WHERE id BETWEEN 1000001 AND 1010000，避免依赖子查询，也方便断点续跑
MySQL 8.0.20+ 虽支持子查询 LIMIT，但线上环境仍推荐主键分片——索引稳定、执行计划可预期、运维排查简单

实际执行时最容易被忽略的是锁行为：INSERT INTO SELECT 在 RR 隔离级别下会对源表扫描范围加 next-key 锁，没索引就是全表锁等效。哪怕你只迁 1 万行，如果 WHERE 条件没走索引，照样卡住线上写入。