我有一个csv文件的格式如下
nconst,primaryName,birthYear,deathYear,primaryProfession,knownForTitles
nm0000001,Fred,1899,1987,"soundtrack,actor,miscellaneous","tt0072308,tt0045537"
nm0000002,Lauren,1924,2014,"actress,soundtrack","tt0038355,tt0117057,tt0037382"
有些字段用引号括起来 ("")
这不是问题,字段也有逗号 (,)
作为分隔符。
我在mysql命令行中使用以下命令:
load data local infile '/home/ec2-user/sample.csv' into table movies.`sample` fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 lines;
哪个实习生没有给出错误,但数据以错误的格式插入表中:
**nm0000001** Fred 1899 1987 soundtrack,actor,miscellaneous tt0043044,tt0072308,tt0050419,tt0045537"**nm0000002**,Lauren Bacall,1924,2014,"actress,soundtrack
我们可以清楚地看到,来自第二行的数据附加在第一行
提前谢谢
编辑:
表定义:
CREATE TABLE `sample` (
`nconst` text,
`primaryName` text,
`birthYear` int(11) DEFAULT NULL,
`deathYear` text,
`primaryProfession` text,
`knownForTitles` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
暂无答案!
目前还没有任何答案,快来回答吧!