mysql load data null_mysql LOAD DATA INFILE NA转换为NULL

Is there an option in the mysql LOAD DATA INFILE command, to take a .tsv file as input to mysql LOAD DATA INFILE, and transform every 'NA' field in that file to NULL in mysql?

And as a bonus, also to be able to take multiple different ones, like 'NaN','NA','--', etc. and transform all of them into 'NULL'.

解决方案

You can use variables:

LOAD DATA LOCAL INFILE 'file.tsv' INTO TABLE my_table

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

(@col1, @col2, @col3)

SET

col1 = CASE WHEN @col1 NOT IN ('NA', 'NaN', '--') THEN @col1 END,

col2 = CASE WHEN @col2 NOT IN ('NA', 'NaN', '--') THEN @col2 END,

col3 = CASE WHEN @col3 NOT IN ('NA', 'NaN', '--') THEN @col3 END

usin CASE WHEN like this:

CASE WHEN @col1 NOT IN ('NA', 'NaN', '--') THEN @col1 END

when the condition is true it will return the actual value of @col1, or NULL otherwise