SQL INSERT…SELECT 批量写入实战

insert…select 比循环 insert 快，因批量处理减少网络往返、连接开销和日志刷盘，mysql 走 bulk insert 路径，innodb 可跳过逐行唯一索引检查；须显式写字段列表防错位。

INSERT…SELECT 为什么比循环 INSERT 快得多

因为数据库把整批数据当做一个逻辑单元处理，避免了多次网络往返、连接开销和事务日志频繁刷盘。MySQL 在 INSERT ... SELECT 中能直接走 bulk insert 路径，InnoDB 甚至可能跳过唯一索引的逐行检查（如果目标表没触发器、没外键约束、没生成列等干扰项）。

但前提是源表和目标表结构兼容、字段顺序或别名明确——否则会静默错位写入，查不到问题还丢数据。

• 必须显式写出字段列表：INSERT INTO t1(a,b,c) SELECT x,y,z FROM t2，不能依赖 SELECT *
• MySQL 8.0+ 对 INSERT ... SELECT 默认加 READ COMMITTED 一致性读，但若源表有大事务未提交，仍可能卡住或报 Lock wait timeout exceeded
• postgresql 的 INSERT ... SELECT 默认不自动加锁源表，但目标表会加 ROW EXCLUSIVE 锁，高并发写入时容易堵在目标表上

MySQL 中 ON DUPLICATE KEY UPDATE 怎么和 INSERT…SELECT 配合用

想边插边去重？INSERT ... SELECT 本身不支持 ON DUPLICATE KEY UPDATE 的语法糖，必须用 INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE 整体包裹，且 UPDATE 子句里只能引用 VALUES() 或源查询字段，不能写目标表原值（比如 col = col + VALUES(col) 是合法的，col = t1.col + 1 会报错 Unknown column 't1.col' in 'field list'）。

• 更新字段必须带别名或用 VALUES(col) 显式指代“这次想插入的值”
• 如果源查询含聚合（如 count(*)），记得在 SELECT 里给它起别名，否则 VALUES(alias_name) 才能引用
• MySQL 5.7 下，ON DUPLICATE KEY UPDATE 会触发所有匹配唯一键的行更新，哪怕只有一行冲突——不是“跳过”，而是“改掉”，这点常被误以为是“忽略”

PostgreSQL 的 INSERT…SELECT 遇到 NULL 或类型不匹配怎么办

PG 对类型强制更严格，INSERT ... SELECT 会逐字段做隐式转换，失败就直接报错，比如把 text 往 jsonb 里插，或把空字符串 '' 插进 numeric 字段，错误信息通常是 column "x" is of type jsonb but expression is of type text 或 invalid input syntax for type numeric: ""。

• 用 COALESCE(col, 'null') 或 NULLIF(col, '') 提前清洗
• 显式转换：把 col::jsonb 或 col::numeric 写进 SELECT 列表，而不是指望 PG 自动猜
• 目标表有 NOT NULL 约束但源字段可能为 NULL？必须用 COALESCE(col, default_value) 补默认值，否则整条 INSERT 失败

批量写入量大时，怎么避免锁表或 OOM

一次塞 100 万行？MySQL 可能撑不住事务日志，PG 可能爆 shared_buffers，而且锁持有时间太长，业务查询全被 block。分批次不是“可选优化”，是必须动作。

• MySQL 建议单次 INSERT ... SELECT 不超过 10 万行；用 LIMIT + OFFSET 分页时注意性能衰减，改用基于主键的游标分片更稳（如 WHERE id > last_id ORDER BY id LIMIT 10000）
• PG 推荐配合 WITH CTE 先抽样过滤再插，避免大子查询拖慢计划器；也可以用 INSERT ... SELECT ... WHERE ctid IN (SELECT ctid FROM ... LIMIT N) 做物理行级切片
• 无论哪种数据库，都别在事务里包多个 INSERT ... SELECT——每个批次单独提交，不然回滚成本极高

最麻烦的其实是字段映射关系：源表字段名变了、加了新字段、类型悄悄升级（比如 VARCHAR(255) 改成 VARCHAR(500)），这些都不会报错，但可能让 INSERT ... SELECT 写出截断或隐式转换异常的数据。上线前务必用小数据集跑通并校验结果行数和关键字段值。