使用mysql中的Load数据

Question

我正在尝试将一些数据从csv文件加载到mysql。这是在树莓派上。

我尝试了使用"--local infile=1"和不使用。

pi data > cat test.csv 
2014-10-30 08-09-08,1
2014-10-30 08-09-13,2
2014-10-30 08-09-18,3
2014-10-30 08-09-23,4
2014-10-30 08-09-28,5
2014-10-30 08-09-33,6
2014-10-30 08-09-38,7
2014-10-30 08-09-43,8
2014-10-30 08-09-48,9
2014-10-30 08-09-53,10

这就是我尝试的：

pi data > mysql --uroot -ppasswd -s solar --local-infile=1
mysql> create table if not exists temp (
     -> time TIMESTAMP,
     -> voltage SMALLINT UNSIGNED,
     -> primary key (time)
     -> );
mysql> LOAD DATA INFILE 'test.csv' INTO TABLE 'temp' FIELDS  TERMINATED BY ','  LINES TERMINATED BY 'rn' (time,voltage);
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 ''temp' FIELDS  TERMINATED BY ','  LINES TERMINATED BY 'rn' (time,voltage)' at line 1
mysql> LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE 'temp' FIELDS  TERMINATED BY ','  LINES TERMINATED BY 'rn' (time,voltage);
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 ''temp' FIELDS  TERMINATED BY ','  LINES TERMINATED BY 'rn' (time,voltage)' at line 1

如有任何帮助，我们将不胜感激。

谢谢。

Answer 1

您在表名周围使用了错误的字符。MySql对字段和表名使用反引号而不是引号。

LOAD DATA INFILE 'test.csv' INTO TABLE `temp` FIELDS  TERMINATED BY ','  LINES TERMINATED BY 'rn' (time,voltage);