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'