MySQL导入CSV时secure-file-priv错误（目录正确仍报错）

尝试使用以下SQL命令将CSV文件导入MySQL 8.0：

load data infile 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\inventory.csv'
into table massDepthAngle
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
ignore 1 rows;

查询secure_file_priv变量，确认路径与文件所在目录一致：

mysql> show variables like 'secure_file_priv';
+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
1 row in set (0.00 sec)

但执行导入命令时仍报错：The MySQL server is running with -secure-file-priv option so it cannot execute this statement，多次修改配置文件无效。

• 修正路径转义问题：Windows系统中，SQL字符串里的反斜杠需要转义，或者改用正斜杠。将文件路径修改为以下两种格式之一：

  -- 转义反斜杠
  load data infile 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\inventory.csv'
  into table massDepthAngle
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
  ignore 1 rows;
  

  或者

  -- 使用正斜杠
  load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/inventory.csv'
  into table massDepthAngle
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
  ignore 1 rows;
  

• 确保MySQL服务完全重启：修改my.ini（Windows下的MySQL配置文件）后，必须通过服务管理器或命令行彻底重启服务：

  net stop MySQL80
  net start MySQL80
  

  重启后再次执行SHOW VARIABLES LIKE 'secure_file_priv';，确认配置已生效。

• 检查文件与目录权限：确保MySQL服务的运行账户（通常为Network Service或Local System）对C:\ProgramData\MySQL\MySQL Server 8.0\Uploads目录及inventory.csv文件拥有读取权限。右键目录/文件→属性→安全→添加对应账户并赋予读取权限。

• 尝试添加LOCAL关键字：如果是通过客户端执行导入命令，可添加LOCAL关键字（需确保客户端允许本地文件导入）：

  1. 先在MySQL中开启全局参数：

     SET GLOBAL local_infile = 1;
     

  2. 重新连接客户端时启用local选项（如使用mysql命令行客户端）：

     mysql --local-infile=1 -u username -p
     

  3. 执行导入命令：

     load data local infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/inventory.csv'
     into table massDepthAngle
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\n'
     ignore 1 rows;
     

内容的提问来源于stack exchange，提问作者Averey Johnson