LOAD DATA 与 COPY 是什么
LOAD DATA 是 MySQL 把文本文件高速灌入表的方式，PostgreSQL 的对应语句是 COPY。共同点是不走逐条 INSERT 的往返开销，由服务端直接读文件批量写入，百万行级别通常比逐条插入快一个数量级。关键区别在文件位置与权限：MySQL 默认读服务器本地路径（需要 FILE 权限），加 LOCAL 关键字才读客户端文件，且要求服务端与客户端都开启 local_infile；PostgreSQL 的服务端 COPY 需要超级用户，而 psql 的 \copy 走客户端权限，普通账号也能用。
基本信息
| MySQL 语句 | LOAD DATA [LOCAL] INFILE '路径' INTO TABLE 表 (列…) |
|---|---|
| PostgreSQL 语句 | COPY 表 (列…) FROM '路径' WITH (FORMAT csv, HEADER true) |
| 权限要求 | MySQL 服务端读文件需 FILE 权限；PG 服务端 COPY 需超级用户，\copy 不需要 |
| 事务行为 | InnoDB 下失败可自动回滚；PG 的 COPY 可被事务包住整体回滚 |
| 典型速度 | 百万行量级比逐条 INSERT 快约一个数量级，视索引与磁盘而定 |
典型使用场景
- 初始化导入：把历史导出的大文件一次性灌进新表
- 数据迁移：从旧库或上游系统导出的 CSV 批量搬入
- 日志与埋点批量入库：每小时生成一个文件，定时任务导入
- 测试数据准备：快速造百万级数据验证索引与查询性能
- 整表恢复：与导出文件配合做单表级快速还原
- 跨环境搬运：把生产抽样的部分数据导入测试库复现问题
MySQL 导入示例
MySQL 导入示例
LOAD DATA LOCAL INFILE '/tmp/orders.csv' # LOCAL 表示文件在客户端机器上
INTO TABLE orders
CHARACTER SET utf8mb4 # 显式声明，避免中文乱码
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' # 处理带引号的字段
LINES TERMINATED BY '\n'
IGNORE 1 LINES # 跳过 CSV 表头
(@id, user_id, @amount, created_at) # 先读进变量，便于清洗
SET id = @id, amount = REPLACE(@amount, ',', ''); # 去掉千分位后再入库
SHOW WARNINGS; # 立刻查看被跳过或截断的行PostgreSQL 导入示例
PostgreSQL 导入示例
\copy orders (id, user_id, amount, created_at) FROM '/tmp/orders.csv' \ # psql 客户端侧执行
WITH (FORMAT csv, HEADER true, NULL '');
-- 服务端 COPY 需要超级用户，可用事务保证失败整体回滚
BEGIN;
COPY orders FROM '/tmp/orders.csv' WITH (FORMAT csv, HEADER true);
COMMIT;
-- 导入后刷新统计，避免优化器按旧分布选计划
ANALYZE orders;常见报错与处理
| 现象 | 原因 | 处理 |
|---|---|---|
| secure-file-priv 报错 | 服务端限制了可读目录 | 把文件放到 secure_file_priv 目录，或改用 LOCAL 方式 |
| 中文乱码 | 文件编码与 CHARACTER SET 不一致 | 文件统一转 UTF-8，并在语句里显式声明 utf8mb4 |
| 日期列为 0000-00-00 | 源数据格式不合法 | 先读进变量，用 STR_TO_DATE 转换后再写入列 |
| permission denied for file | PG 服务端读文件权限不足 | 改用 psql 的 \copy，让客户端负责读文件 |
| 列错位 | 字段内含分隔符且未加引号 | 写 OPTIONALLY ENCLOSED BY，或改用 TSV 分隔 |
LOAD DATA 支持把列先读进用户变量再用 SET 清洗，这是处理带千分位数字、空值标记（如 NULL 字符串、\N）最常用的手法，比事后 UPDATE 一遍更省时间。
导入前临时关闭唯一性检查（SET unique_checks=0）或外键检查（SET foreign_key_checks=0）能提速，但只有在确认数据干净时才能用，否则会写进重复键与孤儿行。