into outfile csv问题：

into outfile 问题：

导出数据出现：error 1290 (hy000): the mysql server is running with the --secure-file-priv option so it cannot execute this statement

root@localhost 17:54:  [(none)]>select categorycode '类别编码',categoryname '类别名称',parentcode as '父编码',level as '级别',case isleaf when 1 then '是' else '否' end as '是否末类',case status when 1 then '有效' else '无效' end as '状态' from erp_cms.category into outfile '/tmp/a.csv';

error 1290 (hy000): the mysql server is running with the --secure-file-priv option so it cannot execute this statement

root@localhost 17:55:  [(none)]>show variables like 'secure-file-priv';

root@localhost 17:57:  [(none)]>show variables like '%secure_file%';

+------------------+-----------------------+

| variable_name    | value                 |

| secure_file_priv | /var/lib/mysql-files/ |

1 row in set (0.00 sec)

mysql定义了outfile的导出目录 ，默认是/var/lib/mysql-files/

解决方法有2种 

1. 导出数据到/var/lib/mysql-files/ 中

root@localhost 17:57:  [(none)]>select categorycode '类别编码',categoryname '类别名称',parentcode as '父编码',level as '级别',case isleaf when 1 then '是' else '否' end as '是否末类',case status when 1 then '有效' else '无效' end as '状态' from erp_cms.category into outfile '/var/lib/mysql-files/a.csv';

query ok, 313 rows affected (0.00 sec)

2. 修改my.cnf  加入 [mysqld]  secure-file-priv ##不对mysql导出目录做限制（不建议）

打开后发现csv乱码：

mysql默认编码：

root@localhost 18:00:  [(none)]>show variables like '%char%';

+--------------------------+-------------------------------------+

| variable_name            | value                               |

| character_set_client     | utf8                                |

| character_set_connection | utf8                                |

| character_set_database   | utf8                                |

| character_set_filesystem | binary                              |

| character_set_results    | utf8                                |

| character_set_server     | utf8                                |

| character_set_system     | utf8                                |

| character_sets_dir       | /usr/share/percona-server/charsets/ |

8 rows in set (0.00 sec)

解决方法：

导出时指定编码： gbk 。分隔符可选。

root@localhost 17:57:  [(none)]>select categorycode '类别编码',categoryname '类别名称',parentcode as '父编码',level as '级别',case isleaf when 1 then '是' else '否' end as '是否末类',case status when 1 then '有效' else '无效' end as '状态' from erp_cms.category into outfile '/var/lib/mysql-files/a.csv' character set gbk fields terminated by ',' optionally enclosed by '"' lines terminated by '\n'