mysql8.0.22导出数据错误

布满荆棘的人生 2022-12-24 10:00 96阅读 0赞

环境:windows10+mysql8.0.22+navicat15

### 事出有因: ###

### mysql8.0.22导出数据: ###

执行命令:

select \* from customers into outfile 'd:/mysql/out.csv';

提示错误:

### 1290 - The MySQL server is running with the \--secure-file-priv option so it cannot execute this statement ###

如下图:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FobWN3dA_size_16_color_FFFFFF_t_70][]

解决办法参考: [https://blog.csdn.net/qq\_33317586/article/details/89415882][https_blog.csdn.net_qq_33317586_article_details_89415882]

1.查看与提示中"secure"有关的变量:

show global variables like "%secure%";

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FobWN3dA_size_16_color_FFFFFF_t_70 1][]

2.尝试改变这个secure\_file\_priv变量的值:

SET GLOBAL secure\_file\_priv='';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FobWN3dA_size_16_color_FFFFFF_t_70 2][]

3.失败,再试另法:

a.修改mysql的启动配置文件my.ini:

\[mysqld\]

secure\_file\_priv=''     \# 直接两个单引号相连,意即不对mysqld的导入导出做限制

(选项说明：

secure\_file\_prive=null   限制mysqld 不允许导入导出

secure\_file\_priv=/var/lib/mysql-files/   限制mysqld的导入导出只能发生在/var/lib/mysql-files/目录下

secure\_file\_priv=''     不对mysqld的导入导出做限制)

b.中止+重新启动mysql服务  
C:\\Windows\\system32>net stop mysql

mysql 服务正在停止..

mysql 服务已成功停止。

C:\\Windows\\system32>**net start mysql**

mysql 服务正在启动 .

mysql 服务已经启动成功。

c.再次查看变量情况:

mysql> show global variables like "%secure%";

![20201127171803939.png][] ![20201127173855540.png][]

secure-file-priv值不再是NULL了 ,OK

d.再执行数据导出命令:

select \* from customers into outfile 'd:/mysql/out.csv';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FobWN3dA_size_16_color_FFFFFF_t_70 3][]

ok,导出成功

以上操作在navicat15查询窗口中操作成功,在cmd下,也可以.注意导出前,要先指定数据库,比如

mysql> select \* from customers into outfile 'd:/mysql/out.csv';  
ERROR 1046 (3D000): No database selected    \#没指定数据库  
mysql> use erp  
Database changed  
mysql> select \* from customers into outfile 'd:/mysql/out2.csv';   \#导出数据  
Query OK, 20 rows affected (0.00 sec)

![20201127174235383.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FobWN3dA_size_16_color_FFFFFF_t_70]: /images/20221120/507bc337419249e0a27efece85b3ae57.png
[https_blog.csdn.net_qq_33317586_article_details_89415882]: https://blog.csdn.net/qq_33317586/article/details/89415882
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FobWN3dA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201127171618782.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FobWN3dA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FobWN3dA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201127171618945.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FobWN3dA==,size_16,color_FFFFFF,t_70
[20201127171803939.png]: https://img-blog.csdnimg.cn/20201127171803939.png
[20201127173855540.png]: https://img-blog.csdnimg.cn/20201127173855540.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FobWN3dA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201127171618949.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FobWN3dA==,size_16,color_FFFFFF,t_70
[20201127174235383.png]: https://img-blog.csdnimg.cn/20201127174235383.png