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'