致力于分享mysql相关知识,收集mysql相关工具!

mysql菜鸟教程

专栏导航

19.3 导出和导入数据

     在日常数据库管理中，我们不仅需要进行完整的数据库备份，还经常需要将部分数据导出为其他格式（如 CSV、文本文件），以便与外部系统交换数据、进行数据分析，或者批量导入数据到数据库表中。MySQL 提供了灵活的工具来实现这些需求，其中最常用的是 SELECT INTO OUTFILE 和 LOAD DATA INFILE 语句。

一、导出数据：SELECT INTO OUTFILE

SELECT INTO OUTFILE 语句将查询结果直接写入服务器上的一个文件。你可以自定义字段分隔符、行终止符、引用字符等，以生成 CSV 或其他格式的文本文件。

基本语法

SELECT 列1, 列2, ...
INTO OUTFILE '文件路径'
[FIELDS TERMINATED BY '分隔符' 
   [OPTIONALLY] ENCLOSED BY '引号字符'
   ESCAPED BY '转义字符']
[LINES TERMINATED BY '行终止符']
FROM 表名
[WHERE 条件];

常用选项说明

选项

默认值

说明

FIELDS TERMINATED BY

'\t'

（制表符）

指定字段之间的分隔符，常用于 CSV 设为 

','

。

FIELDS ENCLOSED BY

''

（无）

指定字段值的包围字符，如双引号 

'"'

。如果使用 

OPTIONALLY

，则仅对字符串字段添加包围符。

FIELDS ESCAPED BY

'\\'

指定转义特殊字符的字符。

LINES TERMINATED BY

'\n'

指定每行结束的字符，Windows 系统可用 

'\r\n'

。

示例 1：导出 students 表为 CSV 文件

假设我们要将 students 表的所有数据导出为 CSV 格式，字段用逗号分隔，字符串字段用双引号包围，每行以换行符结束。

SELECT id, student_no, name, IFNULL(age, '') AS age, IFNULL(major, '') AS major
INTO OUTFILE '/tmp/students.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM students;

注意：

  • 文件路径必须是 MySQL 服务器可写的目录，且受 secure_file_priv 系统变量限制（详见后文）。

  • 如果表中有 NULL 值，默认导出为 \N，可以使用 IFNULL 将其转换为空字符串或其他默认值。

示例 2：仅导出部分字段并添加标题行

虽然 SELECT INTO OUTFILE 本身不支持直接添加标题行，但可以通过 UNION 或使用 mysql 命令导出 CSV 时添加。不过，可以先导出数据，再手动添加标题，或者采用其他方法。

一种技巧是先查询字段名作为一行，再导出数据，但需要两次查询和合并文件，较麻烦。实际应用中常用 mysql 命令行工具来导出带标题的 CSV。

二、导入数据：LOAD DATA INFILE

LOAD DATA INFILE 语句是 SELECT INTO OUTFILE 的逆操作，它从文本文件中读取数据，快速插入到数据库表中。它通常比逐条 INSERT 语句快得多。

基本语法


LOAD DATA INFILE '文件路径'
INTO TABLE 表名
[FIELDS TERMINATED BY '分隔符' 
   [OPTIONALLY] ENCLOSED BY '引号字符'
   ESCAPED BY '转义字符']
[LINES TERMINATED BY '行终止符']
[IGNORE 行数 LINES]   -- 跳过文件开头的行，如标题行
[(列1, 列2, ...)]     -- 指定文件中列的顺序，如果与表中顺序不一致
[SET 列名 = 表达式];   -- 对读取的值进行转换

示例 3：从 CSV 文件导入数据到 students 表

假设备份文件 /tmp/students.csv 是我们刚才导出的 CSV，且文件没有标题行。导入语句如下：


LOAD DATA INFILE '/tmp/students.csv'
INTO TABLE students
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(id, student_no, name, @age, major)
SET age = NULLIF(@age, '');  -- 将空字符串转为 NULL

说明：

  • 我们使用了用户变量 @age 来接收文件中的年龄字段，然后通过 SET 子句将空字符串转换为 NULL，以匹配表中的允许 NULL 列。

  • 如果文件中字段顺序与表中完全一致，可以省略列名列表。

示例 4：导入包含标题行的文件

如果 CSV 文件第一行是列名（标题行），可以使用 IGNORE 1 LINES 跳过它。


LOAD DATA INFILE '/tmp/students_with_header.csv'
INTO TABLE students
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, student_no, name, age, major);

三、关于 secure_file_priv 的注意事项

出于安全考虑，MySQL 限制了 INTO OUTFILE 和 LOAD DATA INFILE 可以操作的目录。系统变量 secure_file_priv 决定了允许读写的目录：

  • 如果 secure_file_priv 设置为一个目录路径，则只能在该目录下进行导入导出。

  • 如果设置为 NULL，则禁止导入导出操作。

  • 如果设置为空（''），则允许在任何目录（不推荐，有安全风险）。

查看当前设置：

SHOW VARIABLES LIKE 'secure_file_priv';

如果路径受限，请将文件放在指定目录下，或者修改配置文件（需要重启服务）。

四、使用 mysql 命令行导出 CSV（带标题的常用方法）

许多开发者更习惯用 mysql 命令行工具结合重定向来生成 CSV，这种方法可以轻松添加标题行。

mysql -u root -p -e 
"SELECT id, student_no, name, 
IFNULL(age, '') AS age, IFNULL(major, '') AS major FROM students" school_db 
| sed 's/\t/,/g' > /tmp/students.csv

但这种方法生成的字段分隔符是制表符，通过 sed 替换为逗号。更可靠的方式是直接使用 mysql 的 --batch 选项并结合 sed 处理标题。

另一个方法是使用 INTO OUTFILE 配合外部脚本添加标题。不过，对于日常导出，使用 mysql 命令行并手动处理更常见。

五、使用 mysqlimport 工具导入数据

mysqlimport 是 MySQL 提供的一个命令行工具，本质上是 LOAD DATA INFILE 的封装，用于从文本文件导入数据到表。

基本语法：

mysqlimport -u root -p [选项] 数据库名 文件1 [文件2 ...]

常用选项：

  • --fields-terminated-by=, 指定字段分隔符。

  • --fields-enclosed-by='"' 指定字段包围符。

  • --lines-terminated-by='\n' 指定行终止符。

  • --ignore-lines=1 跳过文件前 N 行（用于跳过标题）。

  • --local 表示文件位于客户端本地（而不是服务器），MySQL 8.0 中需要此选项。

示例：导入 students.csv 到 school_db 数据库的 students 表（文件有标题行）：

mysqlimport -u root -p --local 
--fields-terminated-by=',' --fields-enclosed-by='"' 
--ignore-lines=1 school_db /tmp/students.csv

注意：文件名（不含扩展名）必须与表名相同，即 students.csv 会导入到 students 表。

六、实践练习

  1. 将学生表的数据导出为 CSV 文件，要求字段用 | 分隔，字符串不加引号，文件存放在 /tmp/students_pipe.txt。

  2. 使用 LOAD DATA INFILE 将刚导出的文件导入到一个新的表 students_backup 中（需提前创建相同结构的表）。

  3. 尝试用 mysqlimport 导入一个带标题行的 CSV 文件。

常见问题与解决方案

  • 文件权限问题：确保 MySQL 服务器进程对目标目录有读写权限。导出时文件由 MySQL 服务器创建，因此目录必须对 MySQL 用户可写。

  • 字符集乱码：在导出和导入时，可以指定字符集。在 INTO OUTFILE 中不支持直接指定字符集，但可以通过设置会话变量 CHARACTER_SET_RESULTS 或使用 mysql 命令行的 --default-character-set 参数。

  • NULL 值处理：默认导出为 \N，导入时也会识别 \N 为 NULL。如果使用其他占位符，需要在 LOAD DATA 中用 SET 子句转换。

  • 大文件性能：对于超大文件，LOAD DATA INFILE 是最高效的导入方式。可以临时禁用索引和外键检查以加速导入：

ALTER TABLE 表名 DISABLE KEYS;
SET FOREIGN_KEY_CHECKS = 0;
-- 执行 LOAD DATA
SET FOREIGN_KEY_CHECKS = 1;
ALTER TABLE 表名 ENABLE KEYS;

小结

  • 导出数据：SELECT INTO OUTFILE 将查询结果写入服务器文件，可自定义格式。

  • 导入数据：LOAD DATA INFILE 从文件读取并插入数据，速度快。

  • secure_file_priv 限制操作目录，需注意配置。

  • mysqlimport 是命令行工具，方便批量导入。

  • 实际开发中，CSV 是最常用的交换格式，掌握导出导入技巧能显著提升数据处理的效率。

至此，你已经掌握了通过文件与外部系统交换数据的基本方法。


所有评论

↑
关于我 备案号:蜀ICP备2023042032号-1