解决——》ERROR 1064 (42000)You have an error in your SQL syntax; check the manual that corresponds
@[TOC](解决——》ERROR 1064 (42000)You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version)
1、操作
load data local infile “c:/Users/xiaox/Desktop/test.txt” into table test (id,area) fields terminated by “,”;
2、现象
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘fields terminated by “,”’ at line 2
3、原因
把指定字段的部分放到了表名后面。
4、解决
load data local infile “c:/Users/xiaox/Desktop/test.txt” into table test fields terminated by “,” (area,id);
C:\Users\xiaox>mysql -h localhost -u root -p xiaoxian
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.26 MySQL Community Server - GPL
Copyright (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);
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set, 1 warning (0.02 sec)
mysql> set global local_infile=1;
Query OK, 0 rows affected (0.00 sec)
mysql> load data local infile "c:/Users/xiaox/Desktop/test.txt" into table test (id,area);
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
mysql> exit
Bye
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 - GPL
Copyright (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: 4
mysql> load data local infile "c:/Users/xiaox/Desktop/test.txt" into table test (id,area) fields terminated by ",";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fields terminated by ","' at line 2
mysql> 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: 2
mysql> load data local infile "c:/Users/xiaox/Desktop/test.txt" into table test fields terminated by "," (area,id);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> load data local infile "c:/Users/xiaox/Desktop/test2.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
mysql>