MySQL CSV 导入导出实战：5.7 与 8.x 全流程详解

一、为什么要用 CSV？

生产环境中，我们经常遇到这些场景：

• 业务方要一份表数据，需要导出成 Excel 能打开的格式
• 从外部系统接收数据文件，需要批量导入 MySQL
• 数据库迁移时，先导出成文件再按需导入
• 定时任务跑完的结果集，需要落地归档

CSV 格式简单、通用，几乎所有工具都支持。MySQL 原生提供了 SELECT ... INTO OUTFILE 和 LOAD DATA [LOCAL] INFILE 两条核心 SQL，效率比应用层循环 INSERT 高一个数量级。

但——5.7 和 8.x 的行为不一样，坑也不一样。这篇文章基于真实操作记录整理，两个版本都会踩到的坑都写进来了。

二、执行前必查：3 个关键变量

不管是 5.7 还是 8.x，操作前先查这三个变量，后面碰到报错 80% 都跟它们有关。

SELECT VERSION() AS mysql_version;
SHOW VARIABLES LIKE 'secure_file_priv';
SHOW VARIABLES LIKE 'local_infile';

5.7 典型环境：secure_file_priv=NULL，local_infile=ON
8.x 典型环境：secure_file_priv=（空，不限制），local_infile=OFF

三、5.7 实战：遇到 ERROR 1290 怎么办

3.1 真实报错复现

mysql> SELECT id, name, dept, salary, hire_date
    -> INTO OUTFILE '/tmp/emp_57.csv'
    -> CHARACTER SET utf8mb4
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> ESCAPED BY '\\'
    -> LINES TERMINATED BY '\n'
    -> FROM emp;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

原因：secure_file_priv=NULL 时，服务端完全禁止文件操作。

3.2 两种解决方案

方案一：不改配置，用客户端重定向（临时方案）

mysql -uroot -p -S /data/3308/mysql.sock \
  -e "SELECT id,name,dept,salary,hire_date FROM demo57.emp" \
  | sed 's/\t/,/g' > /tmp/emp_57_with_header.csv

输出结果（自动带表头，Tab 分隔转逗号）：

id,name,dept,salary,hire_date
1,张三,研发,15000.00,2024-01-10
2,李四,测试,12000.00,2024-02-15
3,王五,运维,13000.00,2024-03-20

方案二：修改配置文件重启（长期方案）

编辑 my.cnf：

[mysqld]
secure-file-priv=/tmp/

重启服务：

service mysqld.3308 stop
service mysqld.3308 start
service mysqld.3308 status

验证生效：

mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | /tmp/ |
+------------------+-------+

3.3 5.7 完整操作流程

Step 1：建库建表，插入测试数据

CREATE DATABASE IF NOT EXISTS demo57 CHARACTER SET utf8mb4;
USE demo57;

CREATE TABLE emp (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  dept VARCHAR(50),
  salary DECIMAL(10,2),
  hire_date DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO emp (id, name, dept, salary, hire_date) VALUES
(1, '张三', '研发', 15000.00, '2024-01-10'),
(2, '李四', '测试', 12000.00, '2024-02-15'),
(3, '王五', '运维', 13000.00, '2024-03-20');

Step 2：导出（无表头）

SELECT id, name, dept, salary, hire_date
INTO OUTFILE '/tmp/emp_57.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM emp;

生成的 CSV 内容：

1,"张三","研发",15000.00,"2024-01-10"
2,"李四","测试",12000.00,"2024-02-15"
3,"王五","运维",13000.00,"2024-03-20"

Step 3：导出（带表头，用 UNION ALL）

SELECT 'id', 'name', 'dept', 'salary', 'hire_date'
UNION ALL
SELECT CAST(id AS CHAR), name, dept, CAST(salary AS CHAR), DATE_FORMAT(hire_date, '%Y-%m-%d')
INTO OUTFILE '/tmp/emp_57_with_header.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM emp;

注意：5.7 不支持 SELECT ... INTO OUTFILE 直接带别名表头，只能用 UNION ALL 把表头行拼进去。

Step 4：回导（无表头）

TRUNCATE TABLE emp;

LOAD DATA LOCAL INFILE '/tmp/emp_57.csv'
INTO TABLE emp
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(id, name, dept, salary, hire_date);

执行结果：

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Step 5：回导（有表头）

TRUNCATE TABLE emp;

LOAD DATA LOCAL INFILE '/tmp/emp_57_with_header.csv'
INTO TABLE emp
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, dept, salary, hire_date);

验证结果：

mysql> SELECT * FROM emp;
+----+--------+--------+----------+------------+
| id | name   | dept   | salary   | hire_date  |
+----+--------+--------+----------+------------+
|  1 | 张三   | 研发   | 15000.00 | 2024-01-10 |
|  2 | 李四   | 测试   | 12000.00 | 2024-02-15 |
|  3 | 王五   | 运维   | 13000.00 | 2024-03-20 |
+----+--------+--------+----------+------------+

四、8.x 实战：更严格的安全策略

4.1 8.x 默认环境

(root@localhost) [(none)]> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |    -- 空字符串=不限制目录
+------------------+-------+

(root@localhost) [(none)]> SHOW VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |    -- 8.x 默认关闭
+---------------+-------+

secure_file_priv 为空字符串，服务端文件操作无限制。但 local_infile=OFF，所以不能用 LOCAL INFILE，只能用 LOAD DATA INFILE（服务端路径）。

4.2 8.x 完整操作流程

Step A：建库建表，插入测试数据

CREATE DATABASE IF NOT EXISTS demo80 CHARACTER SET utf8mb4;
USE demo80;

CREATE TABLE emp (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  dept VARCHAR(50),
  salary DECIMAL(10,2),
  hire_date DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO emp (id, name, dept, salary, hire_date) VALUES
(1, '张三', '研发', 15000.00, '2024-01-10'),
(2, '李四', '测试', 12000.00, '2024-02-15'),
(3, '王五', '运维', 13000.00, '2024-03-20');

Step B：导出（无表头）

SELECT id, name, dept, salary, hire_date
INTO OUTFILE '/tmp/emp_8x.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM emp;

Query OK, 3 rows affected (0.00 sec)

Step C：导出（带表头）

SELECT 'id', 'name', 'dept', 'salary', 'hire_date'
UNION ALL
SELECT CAST(id AS CHAR), name, dept, CAST(salary AS CHAR), DATE_FORMAT(hire_date, '%Y-%m-%d')
INTO OUTFILE '/tmp/emp_8x_with_header.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM emp;

Query OK, 4 rows affected, 1 warning (0.00 sec)

4 rows = 1 个表头行 + 3 条数据，1 warning 是正常的类型转换告警。

Step D：回导（无表头）

TRUNCATE TABLE emp;

LOAD DATA INFILE '/tmp/emp_8x.csv'    -- 8.x 不加 LOCAL，直接服务端路径
INTO TABLE emp
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(id, name, dept, salary, hire_date);

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Step E：回导（有表头）

TRUNCATE TABLE emp;

LOAD DATA INFILE '/tmp/emp_8x_with_header.csv'
INTO TABLE emp
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, dept, salary, hire_date);

验证结果：

(root@localhost) [demo80]> SELECT * FROM emp;
+----+--------+--------+----------+------------+
| id | name   | dept   | salary   | hire_date  |
+----+--------+--------+----------+------------+
|  1 | 张三   | 研发   | 15000.00 | 2024-01-10 |
|  2 | 李四   | 测试   | 12000.00 | 2024-02-15 |
|  3 | 王五   | 运维   | 13000.00 | 2024-03-20 |
+----+--------+--------+----------+------------+

五、5.7 vs 8.x 关键差异对比

六、命令参数速查

SELECT … INTO OUTFILE 参数

LOAD DATA [LOCAL] INFILE 参数

七、常见报错汇总

八、总结

MySQL CSV 导入导出的核心逻辑很简单，记住以下两点就够了：

1. 先查 secure_file_priv 决定方案

secure_file_priv=NULL  →  必须用客户端重定向，或修改配置
secure_file_priv=/tmp/ →  OUTFILE/INFILE 路径必须在 /tmp/ 下
secure_file_priv=空    →  无限制，直接用

2. 5.7 用 LOCAL，8.x 不用 LOCAL

-- 5.7（local_infile=ON）
LOAD DATA LOCAL INFILE '/tmp/file.csv' INTO TABLE t ...

-- 8.x（local_infile=OFF）
LOAD DATA INFILE '/tmp/file.csv' INTO TABLE t ...

搞清楚这两个差异，剩下的字符集、分隔符、跳过表头都是固定写法，照抄模板就行。

专注于 Oracle / MySQL / PostgreSQL / 达梦等主流数据库的运维实战与架构分享，欢迎关注，一起做靠谱的数据库人。