INSERT ... SELECT 是什么
把一条查询的结果直接写进目标表，不需要 VALUES 关键字。数据搬迁、归档、宽表预计算都靠它。列是按 SELECT 的输出顺序与目标列清单一一对应的，不看列名，所以顺序写错不会报错，只会把值插进错误的列。
基本信息
| 基本语法 | INSERT INTO 目标表(列清单) SELECT … FROM 源表 WHERE … |
|---|---|
| 列对应 | 按位置一一对应，不按列名匹配 |
| 行数控制 | 用 WHERE 或 LIMIT 限定写入范围 |
| 同表读写 | 两库都允许从同一张表 SELECT 后再插入，内部会先物化结果 |
| 重复键 | 目标表有唯一键时冲突会终止语句，PG 还会让整个事务失败 |
| 大事务 | 千万行级别应分批提交，避免回滚段或 WAL 膨胀 |
归档搬迁示例
归档搬迁示例
INSERT INTO user_archive (id, name, created_at)
SELECT id, name, created_at -- 顺序必须与上面列清单完全一致
FROM users
WHERE created_at < '2023-01-01' -- 只搬一年前的数据
AND status = 0;
-- 执行前先用同样条件跑一次 SELECT COUNT(*)，核对待写入行数
-- 执行后看返回的受影响行数是否与预期一致分批写入
分批写入
-- 分批搬迁，避免一次大事务
INSERT INTO user_archive (id, name, created_at)
SELECT id, name, created_at
FROM users
WHERE created_at < '2023-01-01'
ORDER BY id
LIMIT 5000; -- 循环执行并逐批提交，便于中断后断点续搬使用要点
| 要点 | 说明 | 风险 |
|---|---|---|
| 列顺序 | 按位置对应，不看列名 | 顺序写错会静默插错列 |
| 源表过滤 | WHERE 控制搬迁范围 | 不带 WHERE 等于整表复制 |
| 唯一键冲突 | 目标表有唯一键时直接报错 | 大批量中途失败要能回滚或续搬 |
| 自增主键 | 由目标表生成或显式指定 | 批量插入后取值行为按库而异 |
| 锁与日志 | 写入产生大量日志与锁 | 分批提交可降低线上冲击 |
典型使用场景
- 冷热分离：把历史数据搬到归档表
- 宽表预计算：把明细聚合结果写入汇总表
- 表结构升级：数据从旧表搬到新结构表
- 分库分表迁移：按分片键分批搬数
- 初始化新环境：把基础数据复制到新库
- 补数：按历史区间重算并写入结果表
跨库搬运时源表与目标表的类型要能对应：MySQL 的 UNSIGNED、TEXT 等类型在 PostgreSQL 里没有完全等价物，映射关系要在建表阶段先定好，别等写入时才报类型错误。
不带 WHERE 的 INSERT ... SELECT 等于整表复制，行数可能远超预期。执行前先用 SELECT COUNT(*) 核对范围，并确认目标表空间充足、唯一键约束符合预期。