LOAD DATA FILE为什么每次只导入一半的记录？

Question

本帖最后由 lee_onion 于 2014-12-09 18:02:30 编辑

Answer 1

导出完以后show warnings看看信息

Answer 2

具体思路：
1、对于大的数据量数据最好按照规则分批导入。
2、load data infile

Answer 3

谢谢几位的回复！我试了下，把记录数精简到2条，执行相同的语句，结果只插入了一条记录……很奇怪，之前其他的导入记录操作从来没碰到过这种情况。为了更好的解决问题，我把表结构和测试用的2条记录贴出来，大家帮忙看看有没问题。记录是从access里导出的。表结构：
CREATE TABLE FINRE_INF_201206
( ID VARCHAR(30) NOT NULL PRIMARY KEY
 ,DIST_CHNNL INT(1) DEFAULT NULL
 ,POL_NO VARCHAR(20) DEFAULT NULL
 ,SEQ_NUM INT(5) DEFAULT NULL
 ,CLI_ID VARCHAR(20) DEFAULT NULL
 ,POL_NUM VARCHAR(20) DEFAULT NULL
 ,ORGAN_ID VARCHAR(10) DEFAULT NULL
 ,PLAN_TYPE VARCHAR(3) DEFAULT NULL
 ,PLAN_CODE VARCHAR(5) DEFAULT NULL
 ,EFF_DT DATE DEFAULT NULL
 ,POL_STATUS INT(1) DEFAULT NULL
 ,UNIT DECIMAL(20,5) DEFAULT NULL
 ,FACE_AMT DECIMAL(25,5) DEFAULT NULL
 ,FACE_AMT_RB DECIMAL(25,5) DEFAULT NULL
 ,MAT_DT DATE DEFAULT NULL
 ,SEX_CODE VARCHAR(1) DEFAULT NULL
 ,EFF_AGE INT(3) DEFAULT NULL
 ,OCCP_CODE INT(1) DEFAULT NULL
 ,SMOKE_IND VARCHAR(1) DEFAULT NULL
 ,BPP_TYPE INT(1) DEFAULT NULL
 ,BPP INT(3) DEFAULT NULL
 ,PPP_TYPE INT(1) DEFAULT NULL
 ,PMT_MODE INT(1) DEFAULT NULL
 ,STD_PREM DECIMAL(25,5) DEFAULT NULL
 ,EXTRA_PREM DECIMAL(25,5) DEFAULT NULL
 ,OCCP_PREM DECIMAL(25,5) DEFAULT NULL
 ,MODE_PREM DECIMAL(25,5) DEFAULT NULL
 ,PD_TO_DT DATE DEFAULT NULL
 ,PAYOUT_TYPE INT(1) DEFAULT NULL
 ,PAYOUT_AGE INT(3) DEFAULT NULL
 ,PAYOUT_METHOD INT(1) DEFAULT NULL
 ,SOURCE VARCHAR(20) DEFAULT NULL
 ,ISS_DT DATE DEFAULT NULL
 ,DIST INT(1) DEFAULT NULL
 ,KEY INDEX_FINRE_INF_201206_PLAN_CODE (PLAN_CODE)
 ,KEY INDEX_FINRE_INF_201206_POL_NO (POL_NO)
) default charset=gb2312 auto_increment=1;记录：
"ID_唯一","销售渠道","保单号","序列号","被保险人编号","保单代码","机构代码","险种类型","险种代码","Expr1009","保单状态","份数","保额","增额保额","Expr1014","性别","第一被保人出生年龄","职业分类","吸烟与否","保障类型","保障年期","缴费类型","缴费方式","标准体保费","弱体加费","职业加费","期缴保费","Expr1027","给付开始年期类型","给付开始年限或年龄","领取方式","业务来源","承保日期","渠道"
"000316795390008_1439786_100","1","000316795390008","100","1439786","11064317","102","1","1042","2006-12-06","1","100","100000","2662.24","2070-12-05","M","36","0","N","3","100","2","1","3530.00","0.00","0.00","3530.00","2012-12-06","0",,"0",,,"0"
"000742035315008_1441421_100","1","000742035315008","100","1441421","26397777","10401","1","1081","2009-05-30","1","10000","100000","519.54","2101-05-29","F","8","0","N","3","100","2","1","1840.00","0.00","0.00","1840.00","2013-05-30","0",,"0",,,"0"谢谢各位啦！

Answer 4

还有上传所用的语句以及当时的warning：
LOAD DATA INFILE "E:/TEST.csv" INTO TABLE FINRE_INF_201206 FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES;

Answer 5

谢谢！没找到上传文件的链接，我放在百度云了，版主有时间的话帮忙看看，非常感谢！测试文件百度云地址：http://pan.baidu.com/s/1dDs3rQ5

Answer 6

找到原因了，可能因为access导出的文本文件，换行用的是\r，而LOAD DATA INFILE默认的换行符为\n。原来的导入语句加入LINE TERMINATED BY '\r' 就行了，谢谢各位！