MySQL在执行create table之后,进行load data:
执行如下代码
create table sale (
year integer,
market varchar(6),
sale long,
profit long
);
load data infile 'C:\SomeDir\mysql_csv\0907\Uploads\sale.csv'
into table sale
CHARACTER SET gb2312
fields terminated by ','
optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n';
导入Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
解决步骤:
第一步 在MySQL 8.0 Command Line Client 中查看环境变量secure_file_priv
仅该变量指定的目录中文件允许导入,故将sql文件和待导入csv文件复制到该目录下。
执行发现问题依旧如故
Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
第二步 将需要执行的的sql文件中形如如下部分:
load data infile 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\sale.csv'
into table sale
CHARACTER SET gb2312
fields terminated by ','
optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n';
其中的文件路径(高亮部分)的"\",改成"\\"或"/"则可以执行无误。
'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/sale.csv'
或者
'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads/sale.csv'
另一个解决:
实际的操作中没有找到my.ini,是因为相关文件和文件夹在Win10系统中为隐藏文件,通过打开隐藏文件可以找到:
C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
进而也可以修改其中的如下信息,为存放文件的地方。
# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"