天池查漏补缺:mysql:导入csv只有一行的问题

练习尝试:

创建表:

CREATE TABLE test_csv(id INT,aname VARCHAR(11)); 
 INSERT INTO test_csv VALUES(1,'ni');
 INSERT INTO test_csv VALUES(2,'uii');
 INSERT INTO test_csv VALUES(3,'nfghi');
 SELECT * FROM test_csv;
 

根据某博客  windows 用'\r\n':

load data infile 'C:/test1.csv' 
into table table_name 
fields terminated by ',' 
optionally enclosed by '"' 
escaped by ',' 
lines terminated by '\r\n'
ignore 1 lines;
发现结果如此

显示只有1行处理了

根据B站教程https://www.bilibili.com/video/av37053137?from=search&seid=16285842889801785382

使用简介版  

 SELECT * FROM test_csv
   INTO OUTFILE 'H:/item_sql/test_csv.csv'
   FIELDS TERMINATED BY ','
   OPTIONALLY ENCLOSED BY ''
   LINES TERMINATED BY '\n';

达成效果

开始导入待处理的CSV文件

第一步.建立表格

不要设置key,此处设置key报错,后面会报错

 

第二步:

尝试过博客的复杂用法,出问题,目前没有找到原因

LOAD DATA INFILE 'H:/item_sql/Antai_AE_round1_train_20190626.csv' 
INTO TABLE item_user.train
fields terminated by ',' 
optionally enclosed by '"' 
escaped by ',' 
lines terminated by '\n'
ignore 1 lines;

最终方案:(解决耗子的就是好猫,砸门不要逼格)

LOAD DATA INFILE 'H:/item_sql/Antai_AE_round1_train_20190626.csv' 
INTO TABLE item_user.train_copy
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
   
SELECT  * FROM train_copy LIMIT 0,10;

与原始数据对比:达到要求