MERGE 与 UPSERT 与批量导入导出与数据迁移

• WITH src AS 定义源
• MERGE INTO ... USING src
• 源表与目标表匹配

MERGE 可与 CTE 联用，先定义源结果集，再 MERGE 到目标表。语法为 WITH src AS (...) MERGE INTO target USING src ON ... WHEN MATCHED ... WHEN NOT MATCHED ...。CTE 提供灵活的数据源。

源可以是 CTE 构造的集合，便于在 MERGE 前做转换/聚合。

• WHEN MATCHED
• WHEN NOT MATCHED
• UPDATE/DELETE/INSERT

MERGE 基于源表与目标表匹配情况分派：WHEN MATCHED THEN UPDATE/DELETE 处理匹配行；WHEN NOT MATCHED THEN INSERT 处理源有目标无的行。可组合多个分支。

分支按顺序评估，读者可精确控制同步/删除/插入逻辑。

• 多行匹配
• 唯一索引冲突
• 行锁

MERGE 源中多行匹配同一目标行会导致不确定更新；并发执行时可能死锁或唯一索引冲突。需保证源键唯一、目标有唯一约束，并考虑锁粒度。

与 UPSERT 并发一样，MERGE 需要事务与锁的正确配合，避免多行匹配与竞态。

• ON DUPLICATE KEY UPDATE
• 需要唯一索引
• 等价于 UPSERT

MySQL 8.0 无原生 MERGE，用 INSERT ... ON DUPLICATE KEY UPDATE 实现 UPSERT：插入，若唯一键冲突则更新。需表有唯一索引/主键。不像 MERGE 支持多分支。

MySQL 用 ON DUPLICATE KEY UPDATE 实现简单 upsert，但无多分支能力。

• 各分支语义
• 语法关键字
• 可移植性

Oracle、SQL Server、DB2 都支持 MERGE，语法基本遵循 SQL 标准，但细节有差异：SQL Server 可用 WHEN NOT MATCHED BY SOURCE；Oracle 支持 UPDATE 分支的 WHERE 与 DELETE 子句；DB2 支持 IGNORE 等。总体可移植但需注意子句差异。

核心 USING/ON/WHEN MATCHED/NOT MATCHED 一致，扩展子句各异。

• PG15 引入 MERGE
• 之前用 ON CONFLICT
• 限制（不能更新同一行多次）

PostgreSQL 15 引入原生 MERGE，语法为 MERGE INTO ... USING ... ON ... WHEN MATCHED/NOT MATCHED。之前用 INSERT ... ON CONFLICT 或 CTE 模拟。限制：每个分支的 WHERE 不能导致同一行被多个分支处理，且不能同时对同一行执行更新+删除。

MERGE 提供更完整的同步语义，但 ON CONFLICT 仍是 UPSERT 常用首选。

• 标准语义
• WHEN MATCHED / NOT MATCHED
• 源表与目标表

SQL:2003 引入 MERGE，基于源表与目标表的匹配（ON 条件）执行：WHEN MATCHED 处理匹配行，WHEN NOT MATCHED 处理不匹配行。用于数据同步、增量更新。

MERGE 是标准化的"upsert/同步"操作，各数据库实现以此为基准。

• ON CONFLICT
• INSERT IGNORE
• REPLACE INTO

UPSERT 各数据库实现不同：PostgreSQL 用 INSERT ... ON CONFLICT（精细控制）；MySQL 用 INSERT ... ON DUPLICATE KEY UPDATE（更新）或 INSERT IGNORE（跳过）或 REPLACE INTO（删除+插入）；SQL Server 用 MERGE。选择取决于冲突处理语义。

REPLACE 是删除+插入，代价大；ON CONFLICT/ON DUPLICATE 更精细。

• SQL Server 特有
• 目标有源无
• DELETE/UPDATE

WHEN NOT MATCHED BY SOURCE 是 SQL Server 特有子句，处理"源表中没有但目标表中有"的行，可执行 DELETE 或 UPDATE（如标记为失效）。实现目标表与源表的完全同步。

该子句处理目标有源无的行，实现源表与目标表的完全同步。

• DO UPDATE 返回更新行
• DO NOTHING 不返回冲突行
• 返回值

ON CONFLICT DO UPDATE 时，RETURNING 返回更新后的行；DO NOTHING 时冲突行不返回（未插入也未更新）。可用于区分结果。

需精确统计插入/更新时注意 DO NOTHING 的跳过行为。

• 需要唯一索引/主键
• 冲突触发
• 无唯一索引则插入

ON DUPLICATE KEY UPDATE 依赖唯一索引或主键检测冲突。若无唯一索引，则不会触发冲突（都是插入）。冲突时更新；否则插入。需注意唯一索引列与 AUTO_INCREMENT 消耗。

冲突检测完全依赖唯一索引或主键，无唯一约束时与普通 INSERT 无异；且每次冲突尝试都会消耗自增值，高频冲突需关注主键跳跃与性能，这是常见隐藏坑点。

• USING 源
• ON 匹配条件
• WHEN MATCHED UPDATE

标准语法：MERGE INTO target USING source ON condition WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...。USING 提供源，ON 定义匹配，WHEN 分支执行动作。

这是 MERGE 的核心结构，各数据库遵循。

• 两者语义对应
• 改写方式
• 差异

简单同步场景下 MERGE 的 WHEN MATCHED UPDATE + WHEN NOT MATCHED INSERT 等价于 INSERT ... ON CONFLICT DO UPDATE。但 MERGE 支持多分支（DELETE、BY SOURCE），ON CONFLICT 更轻量。改写时注意语义差异。

简单 upsert 两者等价，复杂同步用 MERGE。

• 多分支顺序
• UPDATE/DELETE
• 条件分支

可以，MERGE 可定义多个 WHEN MATCHED 分支，用 WHERE 条件区分，如 WHEN MATCHED AND cond THEN DELETE、WHEN MATCHED THEN UPDATE。分支按顺序评估，第一个满足的分支执行。

多个 WHEN MATCHED 分支按顺序评估，用 WHERE 区分 UPDATE/DELETE。

• 引用源与目标
• 分支过滤
• 条件

可以。MERGE 分支的 WHERE 子句可引用源表与目标表的列，用于条件性执行动作（如仅当值变化时更新）。这样可以减少无效更新。

分支 WHERE 可引用两表列，实现条件性动作避免无效更新。

• 多分支组合
• DELETE + INSERT
• 单行限制

可以。MERGE 可在不同分支使用 DELETE 和 INSERT，但同一行在一次 MERGE 中只能被一个分支处理（不能对同一行既 DELETE 又 INSERT）。不同行可分别 DELETE 或 INSERT。

用 DELETE 分支清理源中没有的行，INSERT 分支补充新行。

• MERGE 计划含 Merge Join
• 多种操作
• 代价

MERGE 的执行计划通常包含 Merge Join 或 Hash Join 来匹配源与目标，再按分支执行 UPDATE/DELETE/INSERT 操作。相比单一 UPDATE/INSERT，MERGE 一次扫描完成多种操作，计划更复杂。

MERGE 在匹配阶段一次性处理，适合大批量同步，但计划复杂度高。

• 触发器按操作触发
• UPDATE/DELETE/INSERT 触发器
• 兼容性

MERGE 执行的操作会触发相应的 DML 触发器：UPDATE 分支触发 UPDATE 触发器，DELETE 分支触发 DELETE 触发器，INSERT 分支触发 INSERT 触发器。各数据库对 MERGE 触发器的兼容性有差异（如 SQL Server 对 MERGE 触发器有特殊处理）。

需注意 MERGE 触发多个触发器时的行为与个别数据库限制。

• CTE 数据修改
• 模拟 upsert
• 多语句

PG14 之前可用 CTE 模拟 MERGE：先 UPDATE 匹配行（RETURNING），再 INSERT 未匹配行。如 WITH upd AS (UPDATE ... RETURNING ...) INSERT ... SELECT ... WHERE NOT EXISTS。实现 upsert 同步。

PG14 之前无 MERGE，用 CTE 先更新再插入模拟：UPDATE 返回更新主键，INSERT 用 NOT EXISTS 排除匹配行；理解即可在无 MERGE 库中实现 upsert。

• 同源多分支冲突
• 不能对同一行多次操作
• 限制

PG15 MERGE 的限制：同一条源行不能匹配目标后又被多个分支处理（不能对同一行执行 UPDATE 后又 DELETE），需保证每个分支互斥。这对复杂同步逻辑有约束。

设计 MERGE 分支需避免同一行被多个动作命中。

• REPLACE 是删除+插入
• ON DUPLICATE 是更新
• 副作用差异

REPLACE INTO 在冲突时先 DELETE 再 INSERT，副作用大（自增消耗、触发 DELETE/INSERT 触发器、改变行位置）；ON DUPLICATE KEY UPDATE 就地 UPDATE，保留行，副作用小。通常推荐 ON DUPLICATE KEY UPDATE。

REPLACE 不适合频繁 upsert，ON DUPLICATE 更精细可控。

• HOLDLOCK 锁
• 防止并发
• 表暗示

MERGE 的源/目标表可带 WITH (HOLDLOCK) 表提示，持有范围锁以阻止并发插入导致的竞态，保证 MERGE 一致性。常见于 SQL Server 的 upsert 模式。

MERGE 并发下存在竞态：两会话可能同时判断未匹配后重复插入，WITH (HOLDLOCK) 通过范围锁串行化写入，是 SQL Server upsert 标准写法；理解锁语义才能解释锁等待与死锁。

• COPY 批量高效
• FREEZE 标记
• 绕开 WAL 优化

COPY 比逐行 INSERT 快，通过批量加载减少解析与日志开销。COPY FREEZE 选项将行标记为已冻结（无需后续 VACUUM 清理），但对已有活动事务的可见性有限，适合导入后立即处于稳定态的数据。

FREEZE 减少后续 VACUUM 开销，但需在事务初始快照下使用。

• COPY 触发 ROW 触发器
• 默认行为
• 性能影响

PostgreSQL 的 COPY 默认会触发 BEFORE/AFTER ROW 触发器。触发器会降低 COPY 性能。

若需跳过触发器，PostgreSQL 可临时禁用触发器。

• HEADER
• DELIMITER
• QUOTE/FORCE_NULL

CSV 选项包括 HEADER（首行表头）、DELIMITER（分隔符）、QUOTE（引用符）、NULL（NULL 字符串，如 ''）、FORCE_NULL 等。控制导入导出的格式与 NULL 处理。

这些选项控制 CSV 的解析方式与 NULL 表示，需与文件格式匹配。

• 批量加载
• 语法
• 性能优势

LOAD DATA INFILE 'file' INTO TABLE t [FIELDS ...] [LINES ...] 从文件批量加载，比逐行 INSERT 快得多，是 MySQL 最高效的导入方式。可指定分隔符、引用符、忽略行等。

LOAD DATA 是 MySQL 最高效导入，绕过逐行解析直接批量装载。

• COPY FROM/TO
• 二进制模式
• TEXT/CSV

COPY 支持服务器端（COPY FROM 'file'）与客户端（\copy）两种。格式支持 TEXT、CSV、BINARY。COPY 二进制格式（BINARY）最高效，无文本解析开销。COPY TO 用于导出。

二进制模式快但不可读，适合内部迁移；TEXT/CSV 可读可交换。

• FREEZE 标记
• 减少 VACUUM
• 约束

COPY FREEZE 将导入的行标记为已冻结，减少后续 VACUUM 清理这些行的开销。但需注意：FREEZE 仅在导入的数据相对于当前事务可见性上下文安全时起作用，通常适用于导入后不立即修改的数据。

FREEZE 提升导入性能，减少 autovacuum 负担，但非必需。

• 各自用途
• 性能
• 工具

LOAD DATA INFILE 是服务器端批量加载（最快）；mysqlimport 是 CLI 工具，底层调用 LOAD DATA；多值 INSERT 是 SQL 语句（一次多条）。性能上 LOAD DATA > 多值 INSERT > 逐行 INSERT。

选型取决于数据来源与场景，工具与语句等价。

• pg_bulkload
• pgloader
• COPY

pg_bulkload 是第三方批量加载工具，绕过部分 WAL/日志，性能最高；COPY 是内置标准加载，性能良好；pgloader 支持从其他数据库迁移，功能丰富但性能次之。选择取决于场景。

常规导入用 COPY，极限性能用 pg_bulkload，异构迁移用 pgloader。

• 批次大小
• 事务边界
• 权衡

分批提交没有固定"最佳"行数，取决于数据量、事务大小、锁、WAL 与回滚段。常见经验是每批 500-5000 行或每 x 秒提交一次。太大导致锁与回滚段压力，太小导致提交开销占比高。

需权衡吞吐与原子性，通常按可接受的重做量与回滚代价定界。

• maintenance_work_mem
• shared_buffers
• work_mem

批量导入常调整：maintenance_work_mem（增大以加速索引/约束创建）、shared_buffers（缓存数据）、work_mem（排序/哈希）。导入前可临时关闭索引、外键约束，导入后再重建，可大幅提升速度。

关闭约束/索引 + 增大 maintenance_work_mem 是典型导入优化。

• CSV/JSON/XML
• Parquet/Avro
• 各数据库支持

CSV 是最通用格式，各数据库原生支持；JSON 用于交换，PostgreSQL/MySQL 支持 json 输出；XML 用于异构系统；Parquet/Avro 是列式/二进制格式，主要用于大数据分析，需外部工具或扩展（如 pg_parquet）支持。

列式格式压缩率与查询性能好，但非数据库原生，需转换。

• 原生工具
• 第三方
• 实时 vs 离线

pg_dump/pg_restore、mysqldump 是原生逻辑备份/迁移工具；pgloader 支持异构迁移；AWS DMS 是托管迁移服务（支持不停机）；Debezium 是 CDC 实时同步工具。取舍：同库用原生工具，异构用 pgloader，实时/零停机用 CDC/DMS。

选型取决于同构/异构、停机窗口、实时性要求。

• COPY 锁
• 与并发 DML
• 锁级别

PostgreSQL 的 COPY 目标表会加 ACCESS EXCLUSIVE 锁（阻止并发读写的表级锁），即 COPY 期间其他事务无法访问该表。MySQL 的 LOAD DATA 同样有锁行为。因此 COPY 宜在低峰期或使用分区。

COPY 的排他表锁影响并发，需规划维护窗口。

• NULL 字符串
• CSV 中 NULL
• FORCE_NULL

COPY 默认把空字符串（''）当作 NULL（TEXT 格式），CSV 中需指定 NULL 字符串（默认空字符串）。可用 FORCE_NULL 强制某列空字符串为 NULL。COPY TO 时 NULL 输出为空字符串或指定字符串。

NULL 与空字符串的区分需在 COPY 选项中明确。

• LOCAL 绕过 secure_file_priv
• 客户端文件读取
• 注入风险

LOAD DATA LOCAL INFILE 从客户端读取文件而非服务器端，绕过 secure_file_priv 限制。恶意服务器可诱导客户端读取本地文件（如 /etc/passwd）并上传，构成客户端文件读取注入风险。生产环境应禁用 LOCAL 或严格限制。

这是 MySQL 客户端安全风险，客户端应禁用 LOCAL 或仅信任服务器。

• 导出文件
• 语法
• 与 COPY TO 等价

SELECT ... INTO OUTFILE 'file' 将查询结果导出到文件，等价于 PostgreSQL 的 COPY TO。可指定 FIELDS/LINES 分隔符。受 secure_file_priv 限制。

INTO OUTFILE 导出查询结果，等价于 PostgreSQL 的 COPY TO。

• source 命令
• 脚本执行
• 与 mysql < file 等价

在 mysql 客户端中，source /path/file.sql 或 . 执行 SQL 脚本文件，等价于 mysql < file.sql。用于批量执行导入、迁移脚本。

source 在客户端内执行，逐条执行脚本中的语句。

• 并行导出
• 功能
• 弃用

mysqlpump 支持并行导出（多线程）、按库/表过滤、更细粒度，是 mysqldump 的改进；但 mysqlpump 在 MySQL 8.0 中已被弃用（推荐使用 mysqldump 或 mysqlsh 的 util.dump）。mysqldump 更通用。

mysqlpump 已被弃用，新项目用 mysqldump 或 dump 工具。

• 外部文件
• pg_parquet 等扩展
• 大数据集成

PostgreSQL 通过扩展（如 pg_parquet、parquet_fdw）或外部工具访问 Parquet/ORC 列式文件，实现与大数据体系的数据交换。列式格式压缩率与查询性能好。

通常用 FDW 或导入导出工具，非原生支持。

• BINARY 格式
• 高效
• 局限

COPY ... WITH (FORMAT binary) 使用二进制格式，无文本解析开销，性能最高，且保留类型精度。但二进制文件不可读、跨版本/跨平台兼容性差，仅适用于同一实例迁移。

BINARY 用于高性能内部迁移，不适合交换。

• -j 并行
• 加速恢复
• 限制

pg_restore -j N 使用 N 个并行作业恢复数据，加速恢复。但 DDL 与依赖需按顺序，部分对象（如外键、索引）无法并行。并行度受 CPU 与磁盘 I/O 限制。

并行作业加速恢复，但 DDL 与依赖对象仍按顺序执行。

• 从命令读取
• 管道
• 安全

COPY ... FROM PROGRAM 'command' 从 shell 命令的标准输出读取数据，可直接从压缩文件、远程命令等导入。TO PROGRAM 可导出到命令。需注意安全（命令以超级用户权限执行）。

通过命令管道读取数据，便于压缩/远程导入，但需注意命令权限。

• FSM 空闲空间
• VM 可见性
• COPY 填充

COPY 大量插入数据时，会更新表的 free space map（FSM）和 visibility map（VM）。COPY 产生的新页会记录空闲空间，冻结的页更新 VM。导入后合理维护这些结构有助于后续 VACUUM 与查询。

VM 标记全可见页，加速 index-only scan；COPY 影响这些结构。

• 错误处理
• MAX_ERRORS
• 部分导入

PostgreSQL 的 COPY 默认遇到错误即中止（事务回滚）。某些数据库/工具支持 ERRORS 子句（如 Greenplum 的 MAX_ERRORS）允许跳过一定数量的错误行继续导入。PostgreSQL 原生 COPY 不支持 MAX_ERRORS，需用分段或外部工具。

实现容错导入需分段分批或使用支持错误行数的工具。

• file_fdw
• mysql_fdw
• 外部表

PostgreSQL 通过 postgres_fdw、mysql_fdw、file_fdw 等创建外部表，访问远程表或文件。可对外部表 SELECT/INSERT，实现跨库数据交换。批量导入可用 INSERT INTO t SELECT * FROM ft。

FDW 提供外部数据源的统一访问，file_fdw 直接读 CSV 文件。

• COPY 一次性导入
• file_fdw 持续访问
• 场景

COPY 把文件一次性导入表中（常驻），适合一次性/周期性加载；file_fdw 把外部文件作为外部表直接查询，不落库，适合只读访问、临时分析。选型看是否需要持久化与查询频率。

需要持久化用 COPY，临时/只读用 file_fdw。

• 一致性快照
• 不锁表
• InnoDB

--single-transaction 在 InnoDB 下使用单一事务（REPEATABLE READ）导出，获得一致性快照，无需锁表，避免阻塞在线写。适合 InnoDB 表的一致性备份。

该选项保证备份期间数据一致，但仅适用于支持事务的引擎。

• COPY 协议管道
• pgcopydb
• 跨版本迁移

用 COPY 协议配合管道（如 pg_dump | pg_restore 或 pgcopydb）实现大库克隆与跨版本迁移。pgcopydb 利用 COPY 协议、并行与增量同步，实现高效迁移；pg_dump 管道流式传输避免中间文件。

管道 + COPY 协议避免磁盘中转，提升迁移效率。

• 指定列
• 部分列
• 默认值

可以。COPY t(col1, col2) FROM ... 只导入指定列，未指定列用默认值或 NULL。同理 COPY TO 可导出指定列。

指定列导入时其余列用默认值或 NULL，提供灵活加载。

• ETL 工具
• 原生 COPY
• 场景

ETL 工具（Talend、Kettle、Airflow）提供复杂转换、调度、监控，适合复杂数据管道；原生 COPY 用于简单高效加载，性能更高但无转换能力。复杂 ETL 用工具，简单批量用 COPY。

选型看管道复杂度与性能要求：ETL 工具提供转换、调度与监控但性能开销大，原生 COPY 加载快但只做搬运；答题要说出复杂管道用工具、简单批量用 COPY 的取舍，而非笼统说哪个更好。

• pg_dump 单库
• pg_dumpall 全集群
• 角色/全局对象

pg_dump 备份单个数据库；pg_dumpall 备份整个集群（所有数据库 + 全局对象：角色、表空间、权限）。恢复时 pg_dumpall 需先建库。

备份角色/权限用 pg_dumpall，单库用 pg_dump。

• 导入外部 schema
• 批量建外部表
• 语法

IMPORT FOREIGN SCHEMA remote_schema FROM SERVER s INTO local_schema 批量创建远程表对应的外部表，避免逐个 CREATE FOREIGN TABLE。可用 LIMIT TO / EXCEPT 过滤。

该命令批量创建外部表，避免逐个手工定义。

• 行数统计
• checksum
• 校验方法

导入后校验一致性的方法：对比行数（COUNT）、比对聚合（SUM/checksum）、抽样比对、或对关键列做哈希汇总。可计算源与目标的校验和（如 MD5(SUM(...))）验证。

校验是迁移的关键步骤，防止静默数据损坏。

• 编码指定
• 转换
• 乱码

COPY 支持 ENCODING 选项指定文件的字符编码，导入时自动转换到数据库编码。未指定时按数据库默认编码。可避免乱码。

COPY 的 ENCODING 选项指定源文件字符集，导入时数据库自动转换到目标编码，从而避免乱码；注意该选项只在读写文件时生效，未指定则按数据库默认编码解释，回答时抓住自动转换这一关键点即可。

• COPY 写 WAL
• 崩溃恢复
• 性能

COPY 导入的数据会写入 WAL（预写日志），以保证崩溃恢复。增大 WAL 量。某些工具（如 pg_bulkload、COPY FREEZE）可减少 WAL。COPY 的 WAL 开销比逐行 INSERT 低。

WAL 保证数据持久性，COPY 也需写入。

• COPY 原子性
• 事务内 COPY
• 性能差异

COPY 可在一个事务内执行，整个事务要么全部提交要么全部回滚，保证原子性。COPY 比逐行 INSERT 快，因为批量处理减少解析与 WAL 开销。大批量导入建议在事务内或分批。

事务内 COPY 保证整体一致性，失败则整体回滚。

• 触发 INSERT 触发器
• 与 INSERT 的差异
• 性能影响

MySQL 的 LOAD DATA 与 INSERT 相同，会逐行触发 BEFORE/AFTER INSERT 触发器（不是默认不触发），因此无法通过 LOAD DATA 绕过触发器逻辑；若需跳过触发器，可在导入前临时禁用。

LOAD DATA 逐行触发 INSERT 触发器，批量导入性能会受触发器影响；这与 PostgreSQL COPY 默认触发 ROW 触发器的行为类似。

• \copy 客户端
• 与 COPY 差异
• 文件路径

\copy 是 psql 客户端元命令，在客户端执行 COPY，从本地文件读取/写入（文件路径在客户端解析），无需服务器文件权限。与服务器端 COPY 区别是文件访问位置。

\copy 在客户端解析文件路径，适合本地文件导入导出。

• 压缩导入
• 管道
• gzip

COPY 本身不支持直接压缩文件，但可用 COPY ... FROM PROGRAM 'gzip -dc file.gz' 或 psql 管道（\copy ... | gzip）实现压缩导入导出，减少磁盘与网络开销。

用 PROGRAM 管道或 psql 管道实现压缩传输，节省磁盘与网络。

• CDC 实时同步
• 双写
• 回切

零停机迁移常用：CDC（变更数据捕获，如 Debezium）把源库变更实时同步到目标库，先全量再增量，最后切换；或双写（应用同时写新旧库），校验后切换。目标是迁移期间服务不中断。

零停机迁移的核心是在线迁移：先全量后增量（CDC）或双写，最后切换，但任何方案都离不开一致性校验与回切预案，否则切换失败会造成数据不一致或长时间停机，这是面试中的必答要点。