MySQL使用INTO OUTFILE和LOAD DATA INFILE导出导入百万级数据文件

MySQL数据库的备份与恢复（1）——使用Select Into Outfile和Load Data Infile命令

MySQL使用SELECT…INTO OUTFILE导出文本文件

MySQL中，可以使用SELECT...INTO OUTFILE语句将表的内容导出为一个文本文件。其基本的语法格式如下：

SELECT [列名] FROM table [WHERE 语句]
        INTO OUTFILE '目标文件' [OPTION];

该语句分为两个部分。前半部分是一个普通的SELECT语句，通过这个SELECT语句来查询所需要的数据；后半部分是导出数据的。其中，“目标文件”参数指出将查询的记录导出到哪个文件中；“OPTION”参数为可选参数选项，其可能的取值有：

• FIELDS TERMINATED BY ‘字符串’：设置字符串为字段之间的分隔符，可以为单个或多个字符。默认值是“\t”。
• FIELDS ENCLOSED BY ‘字符’：设置字符来括住字段的值，只能为单个字符。默认情况下不使用任何符号。
• FIELDS OPTIONALLY ENCLOSED BY ‘字符’：设置字符来括住CHAR、VARCHAR和TEXT等字符型字段。默认情况下不使用任何符号。
• FIELDS ESCAPED BY ‘字符’：设置转义字符，只能为单个字符。默认值为“\”。
• LINES STARTING BY ‘字符串’：设置每行数据开头的字符，可以为单个或多个字符。默认情况下不使用任何字符。
• LINES TERMINATED BY ‘字符串’：设置每行数据结尾的字符，可以为单个或多个字符。默认值是“\n”。
• FIELDS和LINES两个子句都是自选的，但是如果两个子句都被指定了，FIELDS必须位于LINES的前面。
  
  

提示：
该语法中的“目标文件”被创建到服务器主机上，因此必须拥有文件写入权限（FILE权限）后，才能使用此语法。同时，“目标文件”不能是一个已经存在的文件。

SELECT...INTO OUTFILE语句可以非常快速地把一个表转储到服务器上。如果想要在服务器主机之外的部分客户主机上创建结果文件，则不能使用SELECT…INTO OUTFILE语句。

实例

使用SELECT...INTO OUTFILE语句来导出example数据库下employee表的记录。其中，字段之间用“、”隔开，字符型数据用双引号括起来。每条记录以“>”开头。SQL代码如下：

SELECT * FROM example.employee INTO OUTFILE 'F:/backup/tb_chengji.txt'
    FIELDS
        TERMINATED BY '\、'
        OPTIONALLY ENCLOSED BY '\"'
    LINES
        STARTING BY '\>'
        TERMINATED BY '\r\n';

FIELDS必须位于LINES的前面，多个FIELDS子句排列在一起时，后面的FIELDS必须省略；同样，多个LINES子句排列在一起时，后面的LINES也必须省略。

如果在employee表中包含了中文字符，使用上面的语句则会输出乱码。此时，加入CHARACTER SET gbk语句即可解决这一个问题。修改SQL代码如下：

SELECT * FROM example.employee INTO OUTFILE 'F:/backup/tb_chengji.txt'
	CHARACTER SET gbk
    FIELDS
        TERMINATED BY '\、'
        OPTIONALLY ENCLOSED BY '\"'
    LINES
        STARTING BY '\>'
        TERMINATED BY '\r\n';

“TERMINATED BY '\r\n'”可以保证每条记录占一行。因为Windows操作系统下“\r\n”才是回车换行。如果不加这个选项，默认情况只是“\n”。

用root用户登录到MySQL服务器中，然后执行上述命令。

一、使用Select Into Outfile命令导出数据

命令格式如下：

SELECT ... 
INTO OUTFILE 'file_name'
fields 
	terminated by '\,' 
	optionally enclosed by '\"'
	escaped by ''
lines
	terminated by '\r\n'                                

说明：
（1）OUTFILE参数指定的文件所在的路径需要有mysql的访问权限，否则会报错。
（2）每一条记录的数据之间默认以 Tab 分隔，也可使用fields terminated参数指定分隔符。
（3）执行Select into outfile和Load data infile命令需要开启在my.cnf参数文件中设置secure_file_priv参数。该参数的设置如下：
——NULL：MySQL服务会禁止导入和导出操作；
——目录名：MySQL服务只允许在这个目录中执行文件的导入和导出操作。目录必须存在，MySQL服务不会创建它；
——空字符串（’ '）：代表文件可以在任意位置。

1. 查看secure_file_priv参数的取值

mysql> show variables like '%secure_file_priv%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)

2. 修改secure_file_priv参数的值

mysql> set global secure_file_priv='d:/'
> 1238 - Variable 'secure_file_priv' is a read only variable
> 时间: 0s

secure_file_priv是一个只读变量，不允许使用set进行赋值。

[root@Mysql11 ~]# vim /etc/my.cnf  ##编辑MySQL配置文件
#######################################################################
[mysqld]
..........
secure_file_priv=''
..........
#######################################################################

重启MySQL服务，查看secure_file_priv参数的取值

mysql> show variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.00 sec)

3、举例

（1）使用select导出数据——不指定分隔符

mysql> select * from stu into outfile '/tmp/stu.txt';
Query OK, 5 rows affected (0.00 sec)

查看stu.txt文件的内容，数据之间使用tab键分隔。

[root@Mysql11 ~]# cat /tmp/stu.txt
1	zhangsan	20	Xinxiang	15578941258
2	tom	20	Xinxiang	13778942222
3	jack	20	Zhengzhou	13675871454
4	john	21	Zhengzhou	13937681111
5	mark	22	Aanyang	13055882233

使用select导出数据——指定分隔符

mysql> select * from stu into outfile '/tmp/stu2.txt' fields terminated by ',';
Query OK, 5 rows affected (0.00 sec)

查看stu.txt文件的内容，数据之间使用逗号（,）分隔。

[root@Mysql11 ~]# cat /tmp/stu2.txt;
1,zhangsan,20,Xinxiang,15578941258
2,tom,20,Xinxiang,13778942222
3,jack,20,Zhengzhou,13675871454
4,john,21,Zhengzhou,13937681111
5,mark,22,Aanyang,13055882233

二、使用Load Data Infile命令导入数据

命令格式如下：

LOAD DATA [LOCAL] INFILE 'file_name'
INTO TABLE tbl_name
[TERMINATED BY 'string';
fields 
	terminated by '\,' 
	optionally enclosed by '\"'
	escaped by ''
lines
	terminated by '\r\n'

说明：
（1）根据文件的格式指定相应的分隔符；
（2）在非服务端执行Load data需要使用local。比如通过B机器登录A上的mysqld，就需要用到 local 。

1、使用stu.txt文件导入数据

（1）清空stu表中的数据

mysql> truncate stu;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from stu;
Empty set (0.00 sec)

（2）导入数据

mysql> load data infile '/tmp/stu.txt' into table stu;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from stu;
+----+----------+------+-----------+-------------+
| id | name     | age  | address   | phone       |
+----+----------+------+-----------+-------------+
|  1 | zhangsan |   20 | Xinxiang  | 15578941258 |
|  2 | tom      |   20 | Xinxiang  | 13778942222 |
|  3 | jack     |   20 | Zhengzhou | 13675871454 |
|  4 | john     |   21 | Zhengzhou | 13937681111 |
|  5 | mark     |   22 | Aanyang   | 13055882233 |
+----+----------+------+-----------+-------------+
5 rows in set (0.00 sec)

2、使用stu2.txt文件导入数据

（1）清空stu表中的数据

mysql> truncate stu;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from stu;
Empty set (0.00 sec)

（2）导入数据

如果不指定分隔符，则会出现如下错误：

mysql> load data infile '/tmp/stu2.txt' into table stu;
ERROR 1265 (01000): Data truncated for column 'id' at row 1

指定分隔符，导入成功：

mysql> load data infile '/tmp/stu2.txt' into table stu fields terminated by ',';
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from stu;
+----+----------+------+-----------+-------------+
| id | name     | age  | address   | phone       |
+----+----------+------+-----------+-------------+
|  1 | zhangsan |   20 | Xinxiang  | 15578941258 |
|  2 | tom      |   20 | Xinxiang  | 13778942222 |
|  3 | jack     |   20 | Zhengzhou | 13675871454 |
|  4 | john     |   21 | Zhengzhou | 13937681111 |
|  5 | mark     |   22 | Aanyang   | 13055882233 |
+----+----------+------+-----------+-------------+
5 rows in set (0.00 sec)