【MySQL】命令行导出csv文件
测试数据库版本：MySQL 5.7
1：查看变量secure_file_priv
针对LOAD DATA, SELECT … OUTFILE, and LOAD_FILE()的导出、导入操作的目录限制。
1）secure_file_priv的值为null ，不允许导入、导出；
2）secure_file_priv的值为/tmp/ ，表示限制导入、导出只能发生在/tmp/目录下；
3）secure_file_priv没有具体值时，表示不对导入、导出做限制。
|
1 2 3 4 5 6 7 8 9 |
mysql> show global variables like '%secure_file%'; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ 1 row in set (0.00 sec) mysql> |
2：创建测试表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> create table t(id int,name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t values(1,'zs'),(2,'ls'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t; +------+------+ | id | name | +------+------+ | 1 | zs | | 2 | ls | +------+------+ 2 rows in set (0.00 sec) mysql> |
3：命令行执行导出csv文件操作
|
1 2 3 4 |
mysql> select id,name from t into outfile '/var/lib/mysql-files/file_name.csv' fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n' ; Query OK, 2 rows affected (0.00 sec) mysql> |
4：验证
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> exit Bye root@xxf:~# root@xxf:~# cd /var/lib/mysql-files/ root@xxf:/var/lib/mysql-files# ls file_name.csv root@xxf:/var/lib/mysql-files# root@xxf:/var/lib/mysql-files# root@xxf:/var/lib/mysql-files# cat file_name.csv 1,"zs" 2,"ls" root@xxf:/var/lib/mysql-files# |
5：当未使用参数secure_file_priv的指定路径，将报如下错误
|
1 2 3 |
mysql> select id,name from t into outfile '/tmp/file_name.csv' fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n' ; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement mysql> |