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+）或拆成两个语句。
