mysql命令行导出数据

启动CMD进入 C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin 路径，我电脑mysql安装在此文件夹下。（注意，语句必须以；号结尾）

 

Mysql -uroot -p1avb

 

进入mysql

 

mysql> select * from newsku_outdoor into outfile '/tmp/newsku_outdoor.xls';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

mysql> show variables like '%secure%';

+------------------+------------------------------------------------+

| Variable_name    | Value                                          |

+------------------+------------------------------------------------+

| secure_auth      | ON                                             |

| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 5.6\Uploads\ |

+------------------+------------------------------------------------

2 rows in set (0.00 sec)

 

再用outfile命令导出数据到本地文件夹时如果报错：The MySQL server is running with the --secure-file-priv option so it cannot execute this statement，表示权限不够，可以使用show variables like '%secure%';命令查看有权限的文件夹，把文件导入此文件夹中便可。

 

mysql> select * from newsku_outdoor into outfile '/ProgramData/MySQL/MySQL Server 5.6/Uploads/outdoor_sku.xls';

Query OK, 4170 rows affected (0.03 sec)

这样写，就可以把数据表的数据导入到本地文件夹中，以xls的格式存放