MySQL load data infile ("NULL" String in CSV for datetime)

Question

Hi I am have the following CSV format

id   (tab)  creation_date
-------------------------
1           2012-05-04 15:26:45.000
2           NULL

For the importing I am using:

load data local infile './test.csv' into table TEST fields terminated by '\t'
  lines terminated by '\n'
  IGNORE 1 LINES
    (id,@creation_date)
  SET
    creation_date = nullif(@creation_date,'NULL');

But unfortunately the NULL value (String) is not interpreted and set to a NULL in the column. Instead I get a warning about data truncation and a default date is inserted 0000-00-00....

How can I check: Is String = "NULL" then insert NULL into column?

Answer 1

The documentation has rules to how NULL is interpreted in the file depending on you query fields and lines used, in simple terms:

• If FIELD and LINES is default values in your query use \N (NO QUOTES).
  
• If FIELD ENCLOSED BY and/or FIELD ESCAPED BY is used, use the word NULL (NO QUOTES).
  
• With fixed-row format (which is used when FIELDS TERMINATED BY and FIELDS ENCLOSED BY are both empty), NULL is written as an empty string i.e. "".
  

Your use case is the first bullet.

**Read more from the docs in the bottom paragraph Handling of NULL values varies according to the FIELDS and LINES options in use: of the section link above.