解决——》ERROR 1064 (42000)You have an error in your SQL syntax； check the manual that corresponds
发布时间
阅读量：
阅读量
Java
Java
Java
Java
Java
[@TOC](解决方法→ ERROR 1064 (42000)请注意您的SQL语法错误；参考对应的MySQL服务器版本的手册以获取更多信息。)
1、操作
将data从local infile内部文件中导入到table test中，并命名为test表（包含id和area字段），使用逗号作为分隔符。
2、现象
ERROR 1064 (42000): A bug exists in your SQL structured query language syntax; refer to the documentation corresponding to your MySQL server version for correct structured query language syntax near 'fields terminated by “,”’ at line 2.
3、原因
把指定字段的部分放到了表名后面。
4、解决
将数据从本地文件c:/Users/xiaox/Desktop/test.txt导入到表test中，并将字段分隔于逗号处，并指定字段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>全部评论 (0)
还没有任何评论哟~