一、语法

load data local infile "txt文件路径"
into table 表名 
fields terminated by "," 
lines terminated by "\n"; 

1、表字段数>txt字段数

load data local infile "c:/Users/xiaox/Desktop/test.txt" 
into table test
fields terminated by ","
lines terminated by "\n" 
(id,name,@abc,age);

@abc表示变量，使用变量作为占位符，导入时等于丢掉这列数据。

2、表字段数

表字段	id,name,age
txt字段	name,age

load data local infile "c:/Users/xiaox/Desktop/test.txt" 
into table test
fields terminated by ","
lines terminated by "\n" 
(name,age);

二、示例

C:\Users\xiaox>mysql -h localhost -u root -p xiaoxian --local-infile
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.26 MySQL Community Server - GPLCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> load data local infile "c:/Users/xiaox/Desktop/test.txt" into table test (id,area);
Query OK, 2 rows affected, 4 warnings (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 4mysql> load data local infile "c:/Users/xiaox/Desktop/test.txt" into table test fields terminated by ",";
Query OK, 0 rows affected, 2 warnings (0.01 sec)
Records: 2  Deleted: 0  Skipped: 2  Warnings: 2mysql> load data local infile "c:/Users/xiaox/Desktop/test.txt" into table test fields terminated by "," (area,id);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

三、错误

1、ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

解决——》ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

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

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

3、ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

解决——》ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.