csv导入mysql报错ERROR 1064 (42000)

[pblh123]

csv导入mysql报错ERROR 1064 (42000)

需求:
表格设计第一列自动生成,自动加一做计数使用.导入数据时需要忽略第一行列名信息.


导入的数据格式
/Documents/mysql/upload/import$ head -n 19 import.csv
观测时间,经度,纬度,风向,风力（m/s）
20151201,60,70,343,3
20151201,61,70,48,4
20151201,62,70,62,5
20151201,63,70,348,1
20151201,64,70,24,2
20151201,65,70,310,2
20151201,66,70,341,3
20151201,67,70,354,5
20151201,68,70,12,4
20151201,69,70,17,2
20151201,70,70,57,2
20151201,71,70,24,3
20151201,72,70,281,6
20151201,73,70,298,4
20151201,74,70,357,3
20151201,75,70,325,11
20151201,76,70,17,8
20151201,77,70,347,6


我建立的表结构

mysql> desc China_weather;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| ID | int(20) | NO | PRI | NULL | auto_increment |
| ts | varchar(10) | YES | | NULL | |
| LONGITUDE | int(11) | YES | | NULL | |
| LATITUDE | int(11) | YES | | NULL | |
| VANE | int(11) | YES | | NULL | |
| wind_power | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

使用的导入脚本
1 LOAD DATA
2 INFILE '/home/lee/Documents/mysql/upload/import/import.csv'
3 INTO TABLE China_weather
4 CHARACTER SET utf8
5 FIELDS TERMINATED BY ',' ENCLOSED BY '"'
6 (
7 ts varchar(10),
8 LATITUDE int(11),
9 ATITUDE int(11),
10 WINDS int(11),
11 SPEED int(11)
12 );

报错:
source /home/lee/Documents/mysql/upload/import_winds.sql
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 'varchar(10),
LATITUDE int(11),
ATITUDE int(11),
WINDS int(11),
SPEED int(11)
)' at line 7

弄了好久,搞不定.请大神帮忙,十分感谢.

[shoppo0505]

是不是第五行单引号出现问题了?

[LongRui888]

FIELDS TERMINATED BY ',' ENCLOSED BY '"' 改成 FIELDS TERMINATED BY ','

[Ginnnnnnnn]

指出你的几个问题 1、你的表并没有指定 Speed 列，为何Load进去的时候指定了Speed列 2、后面导入的时候指定字段，不需要带数据类型 3、行分隔符有问题，你是换行符，用\r\n 或者直接 ' ' 也是可以。 4、参考语句 load data infile 你的文件 into table 你的table fields terminated by ',' enclosed by '\r\n' (列1,列2,....);

[pblh123]

还是不行,修改过后提示问题如下: 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 ',' 代码修改如下: LOAD DATA INFILE '/home/lee/Documents/mysql/upload/import/import.csv' INTO TABLE China_weather ( ts, LONGITUDE, LATITUDE, VANE, wind_power ) FIELDS TERMINATED BY ',' IGNORE 1 LINES;