更改默认的outfile位置以避免错误代码1290-my.ini不可用 [英] Change default outfile location to avoid Error Code 1290 - my.ini not available

问题描述

我正在尝试使用以下命令将查询结果导出到.csv:

I am trying to export the result of a query to a .csv using:

SELECT 'BAT_POSITION_IN_SERIES', 'NUMBER_OF_PITCHES', 'PCT_BALLS', 'PCT_CALLED_STRIKE', 'STRIKEOUTS_PER_PITCH', 'WALKS_PER_PITCH', 'HITS_PER_PITCH', 'RUNS_PER_PITCH'
UNION
SELECT *
FROM per_pitch_summary
INTO OUTFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\per_pitch_summary.csv'
FIELDS ENCLOSED BY '"' 
TERMINATED BY ','
ESCAPED BY '"'
LINES TERMINATED BY '\n'
;

以上执行，但是我想更改目标文件夹.将文件路径替换为所需的目标位置时，我收到消息:错误代码:1290.MySQL服务器正在使用--secure-file-priv选项运行，因此它无法执行此语句.

The above executes, however I would like to change the destination folder. When replacing the file path with my desired destination, I receive the message: Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement.

我看到的大多数故障排除解决方案都涉及在my.ini文件中注释掉一行，但是我的MySQL版本中没有此文件.相反，可执行文件的路径是C:\ Program Files \ MySQL \ MySQL Server 8.0 \ bin \ mysqld.exe.

Most of the troubleshooting solutions I have seen involve commenting out a line in the my.ini file, but I do not have this file in my version of MySQL. Instead, the Path to Executable is C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe.

任何建议将不胜感激！

推荐答案

我很确定您在某个地方有一个默认配置文件.但是，您可以在启动sql时指定自己的配置.以下将加载默认选项，但会覆盖您在配置中设置的选项，这是更安全的选项.

I'm pretty sure you have a default config file somewhere. But you can specify your own config when launching sql. The following will load the default options but override the ones set in your config, this is the safer option.

mysql --defaults-extra-file=/home/my.cnf

然后在您的 my.cnf 中:

[client]
user="root"
password="mypassword"
host="localhost"
database="placeToStart"

[mysqld]
secure-file-priv = ""

通过将其设置为空路径，您将禁用该变量.要确认这一点，请查看全局变量值:

By setting it to an empty path you'll disable the variable. To confirm this, look at the global variable value:

SHOW VARIABLES LIKE "secure_file_priv";

这篇关于更改默认的outfile位置以避免错误代码1290-my.ini不可用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！