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)
查看执行结果如下: