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

Mysql 专栏收录该内容
121 篇文章 0 订阅

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

1.报错

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

2.原因

什么是secure-file-priv,按照字面意思,就是安全的文件权限(pri -> privileges,那么,这个secure-file-priv又是什么玩意儿呢?查看mysql referman,如下:
在这里插入图片描述

This option sets the secure_file_priv system variable, which is used to limit the effect of data
import and export operations, such as those performed by the LOAD DATA and SELECT … INTO
OUTFILE statements and the LOAD_FILE() function.

【译:这个选项设置系统变量: secure_file_priv,这个变量被用于限制数据导入的导出操作,诸如执行LOAD DATA以及SELECT ... INTO OUTFILE操作以及LOAD_FILE()函数。 】
那么这个secure_file_priv可以设置的值有哪些呢?其有三个值可以配置,如下:

  • If empty, the variable has no effect. This is not a secure setting. 【如果此配置项值为空,则表示没有安全设置】
  • If set to the name of a directory, the server limits import and export operations to work only with files in that directory. The directory must exist; the server will not create it.
  • If set to NULL, the server disables import and export operations. This value is permitted as of MySQL 5.7.6

查看mysql的该变量值,如下:

mysql> show variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)

根据上述的NULL值,可以看到是不允许导出(入)到文件。

3.解决办法

在配置文件中,设置secure-file-priv为某个路径即可,如下:
在这里插入图片描述

mysql> show variables like '%secure_file_priv%';
+------------------+--------------------------------------------------+
| Variable_name    | Value                                            |
+------------------+--------------------------------------------------+
| secure_file_priv | D:\Program Files\MySQL\MySQL Server 5.7\outfile\ |
+------------------+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

4. 结果

再次执行上述的SQL,如下:

mysql> select * from student into outfile 'D:/Program Files/MySQL/MySQL Server 5.7/outfile/student.csv';
Query OK, 2 rows affected (0.00 sec)

查看执行结果如下:
在这里插入图片描述

  • 4
    点赞
  • 0
    评论
  • 9
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值