练习尝试:
创建表:
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;
与原始数据对比:达到要求