SQL INSERT…SELECT 高效写入实践

insert……select 比循环 insert 快，因其单语句批量搬运，避免网络往返、连接开销与逐行解析；mysql/postgresql 内部缓冲整结果集，事务日志与索引更新更紧凑。

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

因为它是单条语句完成批量数据搬运，避免了网络往返、连接开销和逐行解析。MySQL 或 PostgreSQL 在执行时会把整个 SELECT 结果集作为内部缓冲，一次性写入目标表，事务日志、索引更新也更紧凑。

常见错误现象：Lock wait timeout exceeded 或 Out of memory —— 多半是 SELECT 返回几百万行却没加 LIMIT 或没分批，导致锁住源表太久或撑爆 buffer。

使用场景：ETL 中间层落地、归档旧数据、物化视图初始化、跨库同步（配合 FEDERATED 或 postgres_fdw）。

• 确保目标表主键 / 唯一约束与 SELECT 字段顺序、类型严格匹配，否则可能静默截断或报 Duplicate entry
• 如果源表有大字段（TEXT、BLOB），而目标表对应列为 VARCHAR(255)，MySQL 默认会截断且不报错（除非开了 STRICT_TRANS_TABLES）
• PostgreSQL 中若 SELECT 含 serial 列，目标表对应列必须是 GENERATED ALWAYS AS IDENTITY 或显式插入值，否则报 cannot insert into column "id"

怎么控制 INSERT……SELECT 的事务粒度和内存占用

默认是一次性提交所有行，对大表风险极高。不能靠应用层“手动分页”（比如 SELECT …… LIMIT 10000 OFFSET 0），因为 OFFSET 深度越大越慢，且并发写入时容易漏 / 重。

推荐用游标 + 主键范围分片：

INSERT INTO orders_archive SELECT * FROM orders  WHERE order_id BETWEEN 100000 AND 199999;

关键点：

• 分片字段必须是 ** 有索引的单调递增列 **（如 id、created_at），避免全表扫描
• 每次处理行数建议 1w–10w，具体看单行大小；超过 50MB 的结果集就该拆
• MySQL 中可通过 SET SESSION sort_buffer_size = 4*1024*1024 临时调大排序缓存，但别改全局值
• PostgreSQL 中注意 work_mem，太小会导致大量磁盘临时文件，拖慢速度

INSERT……SELECT 遇到 ON DUPLICATE KEY UPDATE 怎么办

MySQL 支持，但语法只允许在 INSERT 侧写逻辑，不能在 SELECT 里做条件判断。典型误用：INSERT INTO t1 SELECT ……, IF(……, 'a', 'b') FROM t2 ON DUPLICATE KEY UPDATE col=VALUES(col) —— 这里 VALUES(col) 只能取当前这一行 SELECT 的值，不能引用其他列或函数结果。

真正能用的组合只有两种：

• ON DUPLICATE KEY UPDATE col = VALUES(col)：安全，直接覆盖
• ON DUPLICATE KEY UPDATE col = COALESCE(VALUES(col), col)：保留原值优先，仅当新值非 NULL 才更新

想实现“存在则累加、不存在则插入”，必须确保 SELECT 中已算好最终值，例如：

INSERT INTO stats (day, clicks)  SELECT '2024-06-01', SUM(clicks) FROM raw_logs WHERE date = '2024-06-01' ON DUPLICATE KEY UPDATE clicks = clicks + VALUES(clicks);

注意：VALUES(clicks) 是 SELECT 计算出的那个 sum 值，不是原始表里的某一行。

PostgreSQL 中 INSERT……SELECT 没有 ON CONFLICT 怎么办

它有，只是叫法不同：ON CONFLICT DO UPDATE。但陷阱在于：必须显式指定冲突目标（通常是主键或唯一索引），不能只写 ON CONFLICT。

常见错误现象：there is no unique or exclusion constraint matching the ON CONFLICT specification —— 表上确实没建唯一索引，或者建了但字段顺序 / 表达式不一致。

实操要点：

• 唯一约束必须存在，且 ON CONFLICT ON CONSTRAINT constraint_name 中的名称要完全匹配 d table_name 输出的约束名
• 如果想按部分字段冲突（比如只看 (user_id, date)），必须提前建复合唯一索引：CREATE UNIQUE INDEX idx_user_date ON events (user_id, date)
• DO UPDATE SET x = EXCLUDED.x 中的 EXCLUDED 是关键字，代表本次 INSERT 尝试插入的那行数据，不是 SELECT 的别名

最易被忽略的是：PostgreSQL 的 ON CONFLICT 不支持延迟约束（deferred constraints）触发，如果业务依赖此行为，得换用 MERGE（v15+）或拆成两个语句。