在MySQL 8.0中正确使用 LOAD DATA

从 MySQL 8.0 开始， LOCAL出于安全原因，默认情况下禁用加载功能。

mysql> system ls
1.txt      cookbook.sql Documents       limbs.html   Names_2010Census.csv Public     swap_query.txt  Videos
2.txt      db_test.sql  Downloads       Music      openssl-1.1.1k    Python-3.11.4 sysbench-1.0.20
anaconda-ks.cfg Desktop    initial-setup-ks.cfg mysqld.service Pictures       safaribooks  Templates

mysql> LOAD DATA LOCAL INFILE 'Names_2010Census.csv' into table top_names FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

解决方法:如报错提到的both条件，需要在客户端和服务端同时开始local_infile参数

mysql> SET GLOBAL local_infile = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

[root@mydb01 ~]# mysql -uroot -proot --local-infile
mysql> use db01
mysql> LOAD DATA LOCAL INFILE 'Names_2010Census.csv' into table top_names FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Query OK, 162255 rows affected, 65535 warnings (1.49 sec)
Records: 162255 Deleted: 0 Skipped: 0 Warnings: 444813

mysql> commit;

其他：如果只在服务端或者客户端开启这个参数同样会遇到问题

[root@mydb01 ~]# mysql -uroot -proot --local-infile
mysql> show variables like '%local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> use db01
mysql> LOAD DATA LOCAL INFILE 'Names_2010Census.csv' into table top_names FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides


mysql> SET GLOBAL local_infile = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye

[root@mydb01 ~]# mysql -uroot -proot 
mysql> use db01
mysql> LOAD DATA LOCAL INFILE 'Names_2010Census.csv' into table top_names FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.