Mysql 報The MySQL server is running with the --secure-file-priv option so it cannot execute this stat...

場景:

mysql> load data infile 'C:\data.txt' into tab_load_data;
ERROR 1290 (HY000): The MySQL server is
on so it cannot execute this statement

排查:

mysql> select @@global.secure_file_priv;

裡面肯定有預設設定的資料夾,
可以有兩種方案解決
1.把要上傳的檔案放到你設定的檔案目錄內,

2. Disable secure-file-priv.
   必須修改配置檔案my.ini.

#secure-file-priv="datadir="C:\AppServ/MySQL/Uploads"

修改後重啟mysql服務

mysql> select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

3,此時在此執行

mysql> load data infile "c:loads\tab_load_data.txt" i
    -> table tab_load_data;
ERROR 29 (HY000): File 'C:\loads\tab_load_data.txt (Errcode: 2 - No such file or directory)
## 此時 報無此目錄error, 不使用絕對路徑,把要上傳的檔案複製到資料庫對應data目錄,C:\MySQL\data\data_name\tab_load_data.txt
> a
mysql> load data infile "tab_load_data.txt" int
    -> table tab_load_data;
Query OK, 6 rows affected, 1 warning (0.03 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 1

mysql> select * from tab_load_data;
+----+-----------+------+-----------+------+
| id | name      | sex  | jiguan    | f5   |
+----+-----------+------+-----------+------+
|  1 | zhagnsan    | 男   | 江西      |    1 |
|  2 | 韓順平    | 男   | 四川      |    2 |
+----+-----------+------+-----------+------+