☰

ubuntu - 使用 mysql 上传 csv 文件

tag-icon • tag-icon mysql
ubuntu - 使用 mysql 上传 csv 文件

我正在尝试将csv文件上传到mysql表中，但一直出现错误。我正在mysql使用要上传文件的数据库。我在命令行尝试了两次查询。

  LOAD DATA LOCAL  '/home/susan/ahref.csv' replace INTO TABLE ahrefRealtor FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

我收到这个错误

  LOAD DATA linkProfile '/home/susan/ahref.csv' replace INTO TABLE ahrefRealtor;
  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 'linkProfile '/home/susan/ahref.csv' replace INTO TABLE ahrefRealtor' at line 1

第二个查询

  LOAD DATA LOCAL  '/home/susan/ahref.csv' replace INTO TABLE ahrefRealtor FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

同样的错误

答案1

你似乎缺少INFILE关键字 - 语法应该是这样的

LOAD DATA LOCAL INFILE './table1.csv' REPLACE INTO TABLE table1 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

此外，LOAD DATA LOCAL默认情况下可能会禁用 - 尽管您应该能够使用选项在 mysql 客户端命令行上明确启用它--local-infile，即

$ mysql -u root -p test --local-infile
Enter password: 

mysql> SELECT * FROM table1;
+----+-----------+--------+
| Id | name      | value  |
+----+-----------+--------+
|  1 | "Alice"   | 12.345 |
|  2 | "Bob"     | 34.560 |
|  3 | "Carol"   | 89.100 |
+----+-----------+--------+
3 rows in set (0.00 sec)

mysql> LOAD DATA LOCAL INFILE './table1.csv' REPLACE INTO TABLE table1 
    -> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
Query OK, 3 rows affected (0.04 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM table1;
+----+-----------+--------+
| Id | name      | value  |
+----+-----------+--------+
|  1 | "Alice"   | 12.345 |
|  2 | "Bob"     | 34.560 |
|  3 | "Charlie" | 33.330 |
|  4 | "Drew"    | 42.000 |
|  5 | "Edward"  |  0.100 |
+----+-----------+--------+
5 rows in set (0.00 sec)

相关内容