MySQL数据备份与恢复之Select Into Outfile和Load Data Infile命令

一、概述

• SELECT...INTO：专注数据 “存储与导出”，支持将查询结果定向到变量或文件，适配单行提取、批量结构化导出、二进制 / 超长文本导出三种核心场景，是灵活处理查询结果的关键语句。
• LOAD DATA INFILE：聚焦 “批量数据导入”，速度远超传统 INSERT 语句，支持本地 / 服务器文件导入，适配跨机器、带表头、自定义格式等复杂场景，是大数据量迁移的优选方案。

二、SELECT ... INTO导出数据

2.1 使用场景

SELECT...INTO是MySQL中用于数据存储与导出的核心语句，支持将查询结果存储到变量或写入文件，共三种使用场景：

用法格式	功能说明	核心特点
SELECT ... INTO var_list	存储查询结果到变量	仅支持单行结果，适合获取单个/多个字段值
SELECT ... INTO OUTFILE	导出多行数据到文件	支持自定义分隔符、格式控制，适合批量导出
SELECT ... INTO DUMPFILE	导出单行数据到文件	无格式转换、不添加换行符，适合二进制文件/大文本

⚠️ 注意：SELECT语句中最多只能包含一个INTO子句

2.2 存储结果到变量

2.2.1 语法格式

SELECT col1, col2, ... 
INTO var1, var2, ... 
FROM table_name 
[WHERE condition];

关键注意事项

• 变量需用@前缀（用户变量），数量必须与查询列数一致
• 查询结果必须返回单行数据（0行报错No data - zero rows fetched，多行报错Subquery returns more than 1 row）
• 适合场景：获取单条记录的字段值用于后续逻辑判断

2.2.2 示例

-- 存储单行数据到多个变量
SELECT name, age INTO @uname, @uage FROM stu WHERE id=3;

-- 查看变量值
SELECT @uname, @uage;
-- 输出：jack | 20

-- 尾随锁定子句（如FOR UPDATE）之前
SELECT id FROM stu INTO @uid FOR UPDATE;

2.3 导出多行数据到文件

适合批量导出结构化数据（如CSV、自定义分隔符文件），支持丰富的格式控制参数。

2.3.1 语法格式

SELECT col1, col2, ...
INTO OUTFILE 'file_path'
FIELDS
  TERMINATED BY '字段分隔符'  -- 字段间分隔符（默认t）
  [ENCLOSED BY '字段包围符']  -- 包裹字段值（如'"'适合CSV）
  [ESCAPED BY '转义符']       -- 特殊字符转义（默认''）
LINES
  [STARTING BY '行前缀']      -- 行开头字符（默认无）
  TERMINATED BY '行终止符'    -- 行结束符（默认'n'）
FROM table_name
[WHERE condition];

核心配置说明

参数	示例	作用
FIELDS TERMINATED BY ','	字段用逗号分隔	生成CSV格式文件
FIELDS ENCLOSED BY '"'	字段用双引号包裹	避免字段值包含分隔符导致解析错误
LINES TERMINATED BY 'rn'	行结束符为Windows格式	兼容Excel打开不换行问题

2.2.2 实操步骤

1. 检查/配置secure_file_priv参数（必做）

该参数控制文件导入导出的目录权限，需在MySQL配置文件中设置：

-- 查看当前配置

show variables like '%secure_file_priv%';

• NULL：禁止所有导入导出
• 目录路径（如/var/lib/mysql-files/）：仅允许该目录操作
• 空字符串（''）：允许任意目录操作

修改方法（以Linux为例）：

vim /etc/my.cnf  # 编辑配置文件
[mysqld]
secure_file_priv=''  # 设置为允许任意目录
systemctl restart mysqld  # 重启生效

2. 导出示例（含复杂格式）

-- 导出为标准CSV文件（逗号分隔+双引号包裹）
SELECT id, name, age, address 
INTO OUTFILE '/tmp/stu_csv.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY 'rn'  -- 兼容 Windows
FROM stu;

-- 查看导出结果
cat /tmp/stu_csv.csv
"1","zhangsan","20","Xinxiang"
"2","tom","20","Xinxiang"

2.4 导出单行数据到文件

2.4.1 语法格式

SELECT col1  -- 仅支持单个列（多行报错）
INTO DUMPFILE 'file_path'
FROM table_name
WHERE condition;  -- 确保返回单行

核心特点

• 无格式转换：字段值原样写入文件，不添加分隔符/换行符
• 仅支持单行单列：适合导出二进制文件（如图片、PDF）或超长文本
• 目标文件必须不存在（否则报错File exists）

2.4.2 示例

-- 导出用户签名（单行超长文本）到文件
SELECT signature 
INTO DUMPFILE '/tmp/user_signature.txt'
FROM users WHERE id=10;

三、LOAD DATA INFILE导入数据

LOAD DATA INFILE是MySQL高效批量导入数据的命令，速度远超INSERT语句，支持本地文件/服务器文件导入。

3.1 语法格式

LOAD DATA [LOCAL] INFILE 'file_path'
INTO TABLE tbl_name
[FIELDS
  TERMINATED BY '字段分隔符'
  [ENCLOSED BY '字段包围符']
  [ESCAPED BY '转义符']]
[LINES
  [STARTING BY '行前缀']
  TERMINATED BY '行终止符']
[IGNORE number LINES]  -- 跳过前 N 行（如跳过表头）
[COLUMNS TERMINATED BY ...]  -- 同 FIELDS TERMINATED BY
(column1, column2, ...);  -- 指定导入的列顺序（与文件字段对应）

关键参数说明

参数	作用	适用场景
LOCAL	读取客户端本地文件（上传到服务器后导入）	客户端与MySQL服务器不在同一机器
IGNORE 1 LINES	跳过文件第一行	文件包含表头（如CSV表头行）
(col1, col2...)	指定表列与文件字段的对应关系	文件字段顺序与表结构不一致时
CHARACTER SET utf8mb4	指定文件编码	避免中文乱码

3.2 示例

3.2.1 导入服务器端CSV文件（带表头）

-- 表结构：id(int), name(varchar), age(int), address(varchar)
-- 导入文件：/tmp/stu_csv.csv（第一行为表头：id,name,age,address）
LOAD DATA INFILE '/tmp/stu_csv.csv'
INTO TABLE stu
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 1 LINES  -- 跳过表头行
(id, name, age, address);  -- 明确列对应关系

3.2.2 导入客户端本地文件（跨机器）

-- 客户端本地文件：C:datastu.txt（Tab 分隔）
LOAD DATA LOCAL INFILE 'C:/data/stu.txt'  -- Windows路径用/或
INTO TABLE stu
CHARACTER SET utf8mb4  -- 解决中文乱码
FIELDS TERMINATED BY 't'
LINES TERMINATED BY 'n';

3.2.3 导入自定义分隔符文件（补全原文档示例）

-- 导入逗号分隔的 stu2.txt（无表头）
LOAD DATA INFILE '/tmp/stu2.txt'
INTO TABLE stu
FIELDS TERMINATED BY ',';  -- 必须与导出时的分隔符一致

四、常见问题与解决方案

4.1 权限相关错误

错误信息	原因	解决方案
The MySQL server is running with the --secure-file-priv option so it cannot execute this statement	secure_file_priv限制了目录	重新配置secure_file_priv为目标目录或空字符串
Access denied for user 'xxx'@'localhost' (using password: YES)	用户无FILE权限	授予权限：GRANT FILE ON *.* TO 'xxx'@'localhost';
Can't create/write to file '/tmp/stu.txt' (Errcode: 13 - Permission denied)	目录无写权限	给目录授权：chmod 777 /tmp或chown mysql:mysql /tmp

4.2 数据格式错误

错误信息	原因	解决方案
Data truncated for column 'age' at row 1	分隔符不匹配，导致字段值解析错误	确保TERMINATED BY与导出时一致
Incorrect integer value: 'name' for column 'id' at row 1	跳过表头失败	添加IGNORE 1 LINES跳过表头行
Column count doesn't match value count at row 1	文件字段数与表列数不一致	明确指定列顺序：(col1, col2...)

4.3 其他常见问题

• 文件已存在：OUTFILE/DUMPFILE不覆盖现有文件，需先删除目标文件
• 中文乱码：导入时添加CHARACTER SET utf8mb4（确保文件编码与指定编码一致）
• LOCAL参数失效：MySQL配置文件禁用local-infile=0，需改为local-infile=1并重启服务

五、核心用法对比总结

操作类型	适用场景	优势	注意事项
SELECT ... INTO var_list	单行数据提取	简洁高效，直接关联变量	必须返回单行结果
SELECT ... INTO OUTFILE	批量结构化导出	格式灵活，支持CSV/自定义格式	需FILE权限，文件不存在
SELECT ... INTO DUMPFILE	二进制 / 超长文本导出	无格式污染，原样存储	仅支持单行单列
LOAD DATA INFILE	批量导入	速度快（10万级数据秒级完成）	格式需与导出时一致

最佳实践建议

1. 导出结构化数据优先使用OUTFILE+CSV格式（兼容Excel/其他工具）
2. 跨机器导入必须加LOCAL参数，避免路径权限问题
3. 生产环境中secure_file_priv建议设置为指定目录（而非空字符串），提升安全性
4. 导入前先备份表数据，避免数据覆盖