Search code examples
mysqlsqlsetload-data-infile

Mysql Load Data: SET IF for all or given subset of columns?

Got working import code for csv files into my 8-column database here:

Load Data LOCAL InFile 'file.csv' into table myTable
Fields Terminated by ',' 
OPTIONALLY Enclosed by '"'
Lines Terminated by '\n'
IGNORE 1 Lines
(col1, col2, @var3, @var4, col5, col6, col7, col8)
Set 
col3 = if(@var3 = '', NULL, @var3),
col4 = if(@var4 = '', NULL, @var4)
;

It's working fine in changing empty entries to NULL values, but.... Is there any way to shorten the Set part so I don't have to specify a condition for each and every column? I actually need this for 7 of the 8 columns above, and this particular table is rather small.

Solution

    1. Is there any way to shorten the Set part

      Yes, MySQL provides a shorthand function NULLIF():

      col3 = NULLIF(@var3, '') -- etc

    2. so I don't have to specify a condition for each and every column?

      Sadly not, although it should be fairly trivial to generate the desired SQL dynamically in your application code.