mysql LOAD DATA INFILE NA到NULL的转换 [英] mysql LOAD DATA INFILE NA to NULL transformation

问题描述

mysql LOAD DATA INFILE命令中是否有一个选项，可以将.tsv文件作为mysql LOAD DATA INFILE的输入，并将该文件中的每个"NA"字段转换为mysql中的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?

此外，还可以采用多个不同的内容，例如'NaN','NA','--'等，并将它们全部转换为"NULL".

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

推荐答案

您可以使用变量:

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

在这种情况下通常用到

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

当条件为true时，它将返回@ col1的实际值，否则返回NULL

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

这篇关于mysql LOAD DATA INFILE NA到NULL的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！