Load Data Infile 跳过NULL值记录

虽然无法完全跳过某些有NULL值的行，但是可以将NULL值转换成0或者某些值，在进行插入。

LOAD DATA ...

LINES TERMINATED BY "\n"
(@col1, col2,col3,colN)
SET
real_col1_name = ifnull(@COL1,""),
real_col2_name = ifnull(@COL2,0),
etc

利用这种方式，也可以实现对某些列的重排，或者只插入某些列的目的

LOAD DATA ...

LINES TERMINATED BY "\n"
(@col1,@col2,@col3,@col4)
SET
real_col2_name = @col2,
real_col4_name = @col4)

we can also use this method to manipulate the data before we insert into table.


LOAD DATA INFILE 'xxx.txt' into table tbl_name
(@col1, col2,col3,colN)
set 
col1 = round(@col1/1000)