Linux系统下MySQL的导出数据语句SELECT … INTO OUTFILE的用法_linux into outfile

操作系统的版本是：CentOS Linux release 8.2.2004 (Core)
数据库的版本是：mysql Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using EditLine wrapper

关于参数 secure_file_priv

在文件 /etc/my.cnf 中，有一个参数叫 secure_file_priv，这个参数的主要目的就是限制 LOAD DATA INFILE 和 SELECT INTO OUTFILE语句的导入导出的目录位置。登录数据库后，可以执行下面的命令语句查看该参数的值：

mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| /                  |
+--------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

从结果可以看到参数 secure_file_priv 的值是 /，表示可以在根目录下，即可以在任何目录下导入和导出，实际测试中发现可以在任何目录导入数据，但是根本无法在任何目录下导出数据，只能在目录 /tmp 下导出数据，其它目录则不行。

按默认参数设定导出表数据到文本文件中

数据表 student 存储的数据如下：

mysql> select * from student;
+----------------------+--------------+------+-------+----------+---------------------+
| id                   | name         | age  | score | birthday | insert_time         |
+----------------------+--------------+------+-------+----------+---------------------+
| 00000000000000000001 | liaowenxiong |   18 |  NULL | NULL     | 2021-09-25 10:40:51 |
| 00000000000000000002 | liudehua     |   28 |  NULL | NULL     | 2021-09-25 10:40:51 |
| 00000000000000000003 | zhangxueyou  |   38 |  NULL | NULL     | 2021-09-25 10:40:51 |
+----------------------+--------------+------+-------+----------+---------------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

将数据表 student 的数据导出到文件 /tmp/student.txt 中：

mysql> select * from student into outfile '/tmp/student.txt';
1

在命令终端中查看该文件的内容如下：

[root@htlwk0001host ~]# cat /tmp/student.txt
00000000000000000001	liaowenxiong	18	\N	\N	2021-09-25 10:40:51
00000000000000000002	liudehua	28	\N	\N	2021-09-25 10:40:51
00000000000000000003	zhangxueyou	38	\N	\N	2021-09-25 10:40:51
1
2
3
4

注：\N 表示空值。

使用图形化编辑器打开文件 /tmp/student.txt 看下：

自定义分隔符，将表数据导出到 txt 文件中

用以下命令把 student 表的数据导出到 /tmp/student.txt：

mysql> select * from student into outfile '/tmp/student.txt' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
Query OK, 3 rows affected (0.00 sec)
1
2

参数说明：

into outfile – 指定导出的目录和文件名
fields terminated by – 指定字段间分隔符，即定义字段间的分隔符
optionally enclosed by – 指定字段包围符，即定义包围字段的字符，而参数optionally表示数值型字段无效，即数值类型的值不要加包围符
lines terminated by – 指定行间分隔符，即定义每行的分隔符

使用命令查看文件 /tmp/student.txt 的内容：

[root@htlwk0001host ~]# cat /tmp/student.txt;
00000000000000000001,"liaowenxiong",18,\N,\N,"2021-09-25 10:40:51"
00000000000000000002,"liudehua",28,\N,\N,"2021-09-25 10:40:51"
00000000000000000003,"zhangxueyou",38,\N,\N,"2021-09-25 10:40:51"
1
2
3
4

查看输出结果可以看到，数据类型没有添加双引号，字符串和日期类型的数据有加双引号。

使用图形化编辑器打开文件 student.txt：

如果把参数 optionally 去掉再导出数据：

mysql> select * from student into outfile '/tmp/student.txt' fields terminated by ',' enclosed by '"' lines terminated by '\n';
Query OK, 3 rows affected (0.00 sec)
1
2

你可以看到如下的导出结果：

[root@htlwk0001host ~]# cat /tmp/student.txt
"00000000000000000001","liaowenxiong","18",\N,\N,"2021-09-25 10:40:51"
"00000000000000000002","liudehua","28",\N,\N,"2021-09-25 10:40:51"
"00000000000000000003","zhangxueyou","38",\N,\N,"2021-09-25 10:40:51"
1
2
3
4

不加参数 optionally，那么所有的字段值都会加上双引号。

自定义分隔符，将表数据导出到 csv 文件中

用以下命令把 student 表的数据导出到 /tmp/student.csv：

select * from student into outfile '/tmp/student.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
1

使用命令查看文件 /tmp/student.csv 的内容：

[root@htlwk0001host ~]# cat /tmp/student.csv;
00000000000000000001,"liaowenxiong",18,\N,\N,"2021-09-25 10:40:51"
00000000000000000002,"liudehua",28,\N,\N,"2021-09-25 10:40:51"
00000000000000000003,"zhangxueyou",38,\N,\N,"2021-09-25 10:40:51"
1
2
3
4

我们再使用图形化编辑器打开 csv 文件看下：

导出数据时，提示“拒绝访问”

以 root（这是Linux系统的用户）登录 Linux 系统，再以 root（这是数据库的用户） 登录数据库，再将数据库 test 中的表 student 导出到目录 /root/test 下的文件 student.txt 中

导出结果：

mysql> select * from student into outfile '/root/test/student.txt' fields terminated by ',' enclosed by '"' lines terminated by '\n';
ERROR 1 (HY000): Can't create/write to file '/root/test/student.txt' (Errcode: 13 - Permission denied)
1
2

把目录 /root/test 的权限改成 777，再以导出到目录 /root/test 下的文件 student.txt 中

导出结果：

mysql> select * from student into outfile '/root/test/student.txt' fields terminated by ',' enclosed by '"' lines terminated by '\n';    
Query OK, 3 rows affected (0.00 sec)
1
2

居然导出成功了。

把 /root/test 目录的权限该 744

切换成 mysql（这是Linux系统的用户）登录 Linux 系统，将 /root/test 的拥有者和所属用户组改成 mysql，并且把权限改成 755，再以 root（这是数据库的用户） 登录数据库，再将数据库 test 中的表 student 导出到目录 /root/test 下的文件 student.txt 中。

[root@htlwk0001host ~]# chown -R mysql:mysql /root/test # 将/root/test的拥有者和所属用户组改成mysql
[root@htlwk0001host ~]# ls -l /root
总用量 1284288
...
drwxrwxrwx 3 mysql mysql        272 9月  29 11:02 test
...
[root@htlwk0001host ~]# chmod 755 /root/test # 将/root/test的权限改成所有者7，所属用户组5，其它用户5
[root@htlwk0001host ~]# ls -l /root
总用量 1284288
...
drwxr-xr-x 3 mysql mysql        272 9月  29 11:02 test
...
1
2
3
4
5
6
7
8
9
10
11
12

导出结果：

mysql> select * from student into outfile '/root/test/student.txt' fields terminated by ',' enclosed by '"' lines terminated by '\n';
Query OK, 3 rows affected (0.00 sec)
1
2

居然可以成功导出数据。

总结

确保 Linux 用户 mysql 对导出的目标目录拥有写入和执行两种权限

1.以 mysql 身份登录 Linux 系统，不论目标目录的拥有者是不是用户 mysql，只要用户 mysql 对输出的目标目录有写入和执行的权限，就可以将数据库的数据导出到目标目录下的文件中。
2.以 root 身份登录 Linux 系统，不论 root 对目标目标有没有写入和执行的权限，只要目标目录的 other 的权限没有写入和执行，则无法将数据库的数据导出到目标目录下的文件中。

以上两点证明了一件事，登录 MySQL 数据库使用 SELECT INTO OUTFILE 语句导出数据库的数据，在往硬盘写入数据时，使用的是 mysql 用户身份来执行底层的写入数据的命令，所以只要确保 Linux 用户 mysql 对目标目录有写入和执行两种权限就可以正常导出数据了。

而这个 mysql 用户是在安装数据库时自动创建的一个 Linux 操作系统的用户，默认没有 shell 登录权限，需要在文件 /etc/passwd 或者使用命令 usermod 改成允许登录。

将数据导出到 /tmp 目录下

mysql 用户默认拥有 /tmp 目录的写入和执行的权限，所以只要把数据导出在该目录下即可。

在导出数据时不指定目标文件的绝对路径

在写入的时候不指定绝对路径，这样文件默认会写入 /etc/my.cnf 中参数 datadir 所指定的目录下。默认目录为 /var/lib/mysql。然后移动写入的文件到指定目录下即可。

看下文件 /etc/my.cnf 中的参数 datadir 的值：

mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7