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'