How to Load NULL Values, Not Zeros, from a CSV into MySQL using LOAD DATA INFILE?

MySQL: Loading NULL Values from CSV Data

Problem:

When loading data from a CSV file with empty fields into a MySQL table using the LOAD DATA INFILE command, MySQL assigns 0 to empty fields instead of NULL, even when the column is defined with a NULL default value.

Solution:

To correctly load NULL values for empty fields, read the problematic field into a local variable and then conditionally set the actual field value to NULL if the variable is empty:

LOAD DATA INFILE '/tmp/testdata.txt'
INTO TABLE moo
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
(one, two, three, @vfour, five)
SET four = NULLIF(@vfour,'')
;

Explanation:

• The LOAD DATA INFILE statement reads the data from the CSV file into the moo table.
• The FIELDS clause defines the fields in the CSV file and their corresponding column names in the table.
• The fourth field (@vfour) is read into a local variable instead of directly into the four column.
• The SET statement checks if the @vfour variable is empty ('') using the NULLIF function.
• If @vfour is empty, the four column is set to NULL; otherwise, it retains its original value.

Additional Note:

If all the fields can potentially be empty, read them all into local variables and use multiple SET statements to conditionally assign NULL values.

The above is the detailed content of How to Load NULL Values, Not Zeros, from a CSV into MySQL using LOAD DATA INFILE?. For more information, please follow other related articles on the PHP Chinese website!