MySQL导出查询数据出现--secure-file-priv选项问题

一、问题描述

Windows平台的MySQL5.7.12版本使用以下命令导出查询结果到Excel文件时，会出现问题

select sid, sname, age from student
into outfile 'D:/Files/student.xlsx';

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

二、查明原因

通过查阅官方文档：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html，可以知道导出导入文件的相关操作会受到全局变量secure-file-priv的限制，如load data命令、select ... into outfile ...语句、LOAD_FILE()函数等。

具体怎么影响如文档所说：

secure_file_priv may be set as follows:

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