場景:
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;
里面肯定有默認(rèn)設(shè)置的文件夾,
可以有兩種方案解決
1.把要上傳的文件放到你設(shè)置的文件目錄內(nèi),
- Disable secure-file-priv.
必須修改配置文件my.ini.
#secure-file-priv="datadir="C:\AppServ/MySQL/Uploads"
修改后重啟mysql服務(wù)
mysql> select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| NULL |
+---------------------------+
1 row in set (0.00 sec)
3,此時(shí)在此運(yùn)行
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)
## 此時(shí) 報(bào)無此目錄error, 不使用絕對路徑,把要上傳的文件復(fù)制到數(shù)據(jù)庫對應(yīng)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 |
+----+-----------+------+-----------+------+
