使用MySQL導(dǎo)出數(shù)據(jù)的時(shí)候報(bào)錯(cuò)

mysql> select * from exit_tran into outfile '1.csv';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

答：mysql 默認(rèn)對(duì)導(dǎo)出的目錄有權(quán)限限制，也就是說使用命令行進(jìn)行導(dǎo)出的時(shí)候，需要指定目錄進(jìn)行操作；

我們可以使用show global variables like '%secure%';來查看你的系統(tǒng)設(shè)置

mysql> show global variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.00 sec)

說明我們只能將數(shù)據(jù)存放到/var/lib/mysql-files/目錄下。大家可以看一下這個(gè)目錄該文件只有MySQL用戶有權(quán)操作

解決方法將代碼更改為如下方式：

mysql> select * from exit_tran into outfile '/var/lib/mysql-files/1.csv'；
Query OK, 329694 rows affected (0.34 sec)