MySQL入门笔记(五)：另存数据为文件(导出数据)

在select查询数据之后，要把结果数据保存到文件里，则需要另存数据为文件的操作，即导出数据

mysql> select user,host into outfile 'out.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n'  from mysql.user;

解释：

fields terminated by ','                 //字段分隔符，默认没有分隔符

optionally enclosed by '"'          //字段值的开始和结束（字段分隔符之前）的字符，把值包括起来，默认没有隔断符

lines terminated by '\n'         //数据行终止符，此处\n就是数据行结束就换行（分行展示）,默认换行符\n

简写如下：

mysql> select user,host into outfile '/var/tmp/out1.csv' from mysql.user;

报错：

执行上面的数据导出SQL时，报以下错误：

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

意思：MySQL正在执行secure-file-priv的选项，无法执行此语句（SQL里的数据导出路径设置，与secure-file-priv配置冲突）

解决：

Step 1: 查询secure_file_priv对应的值：

mysql>show global variables like '%secure_file_priv%';

此时secure_file_priv的值是: /var/lib/mysql-files/

也就是导出文件的存放路径必须是（看拓展1）secure_file_priv对应的值：/var/lib/mysql-files/  

Step 2: 上面报错SQL的正确写法是：

mysql> select user,host into outfile '/var/lib/mysql-files/out.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from mysql.user;

导出文件如下：

数据导出结果如下（用作对比）：

数据的SQL查询结果如下：

拓展1：secure_file_priv值的作用:

查看官方文档，secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。

● secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。

● secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行

● secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。

拓展2：secure_file_priv值的修改:

可以修改secure_file_priv的对应的值，来更改mysql数据导出文件默认的存放位置，

secure_file_priv 参数是只读参数，不能使用set global命令修改。

正确操作具体如下：

Step 1: 查询mysq的配置文件my.cnf或者my.ini的具体位置

[root@MySQL /]# find -name my.cnf          //找my.cnf文件，找my.ini文件方法一样

Step 2: 打开my.cnf或者my.ini文件，添加一行配置，如下：

[root@MySQL /]# vi /etc/my.cnf           //打开my.cnf并编辑

secure_file_priv=/var/mp/

Step 3: 执行导出数据SQL，如下，已经修改outfile的路径：

mysql> select user,host into outfile '/var/tmp/out.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from mysql.user;

再检查文件是否生成：

最后检查是否导出数据成功：

SQL执行成功，文件也已经生成，数据也正确，说明重新配置的路径已经生效