数据的导入与导出 - MySQL 教程

讲解

业务系统经常要和外部交换数据：把订单导出给财务、把供应商的 CSV 导入商品库。MySQL 自带的文本导入导出一对组合是 SELECT ... INTO OUTFILE（查询结果写成文本文件）和 LOAD DATA INFILE（把文本文件高速装进表）。LOAD DATA 比逐条 INSERT 快几个数量级——它走的是批量解析通道，百万行 CSV 几十秒就能导完，是初始化数据和迁移数据的首选。

有一个安全限制必须先知道：secure_file_priv 参数限定了 OUTFILE/INFILE 能读写的目录，默认指向一个专用目录（本教程环境中是 /var/lib/mysql-files/），不允许写任意路径。INTO OUTFILE 还有个保护：目标文件已存在就报错，防止覆盖。FIELDS TERMINATED BY、ENCLOSED BY、LINES TERMINATED BY 三个子句描述文本格式，导出和导入两边必须写一致。

LOAD DATA 还有个 LOCAL 变体：LOAD DATA LOCAL INFILE 读的是客户端机器上的文件而不是服务器的。方便之余有安全风险——恶意服务器可以诱导客户端读取本地任意文件，所以客户端默认关闭 local_infile，用的时候才显式打开。另外 mysqldump --tab=目录可以把每张表导出成「建表语句 .sql + 数据 .txt」的组合，本质是 OUTFILE 的封装。

示例

以练习库 learn 为例建表演示数据，确认安全目录后把数据导出为 CSV：

USE learn;

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  city VARCHAR(20),
  age INT
);

INSERT INTO users (name, city, age) VALUES ('李明', '北京', 25), ('王芳', '上海', 30), ('张伟', '广州', 28), ('刘洋', '深圳', 35);

SHOW VARIABLES LIKE 'secure_file_priv';

SELECT id, name, city, age
INTO OUTFILE '/var/lib/mysql-files/users_export.csv'
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
FROM users;

用 LOAD DATA 把 CSV 高速导入一张同构新表，格式子句与导出时严格一致：

CREATE TABLE learn.users_imported LIKE learn.users;

LOAD DATA INFILE '/var/lib/mysql-files/users_export.csv'
INTO TABLE learn.users_imported
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

SELECT * FROM learn.users_imported;

DROP TABLE learn.users_imported;

mysqldump --tab 把表导出为「建表语句 + 数据文本」两个文件（目录必须是 secure_file_priv 允许的）：

# --tab 导出：users.sql 是建表语句，users.txt 是制表符分隔的数据
mysqldump -uroot -p --tab=/var/lib/mysql-files learn users

ls -l /var/lib/mysql-files/users.sql /var/lib/mysql-files/users.txt

rm -f /var/lib/mysql-files/users.sql /var/lib/mysql-files/users.txt /var/lib/mysql-files/users_export.csv

常见坑

OUTFILE 路径被拒：报 1290 错误多半是没写在 secure_file_priv 允许的目录里。先 SHOW VARIABLES 查清楚，这个参数只能改配置重启。
导入导出格式子句不一致：导出用逗号分隔、导入按制表符读，数据全部挤进第一列或报错 1261/1262。两边的 FIELDS/LINES 子句逐字符对齐。
CSV 中文乱码：文件编码与连接的字符集不一致，LOAD 进去变成乱码。LOAD DATA 时可用 CHARACTER SET utf8mb4 子句声明文件编码。
随意开启 LOCAL INFILE：恶意服务器可利用它读取客户端本地文件（如 SSH 私钥）。确实需要才开，用完关掉，这是业界通报过的真实攻击手法。

小结

SELECT INTO OUTFILE 导出、LOAD DATA INFILE 高速导入，格式子句两边一致，secure_file_priv 限制目录；LOCAL 变体有安全风险，mysqldump --tab 是封装好的导出。下一章回到库内对象：视图。