How to Properly Load NULL Values from CSV Files into MySQL Tables?

MySQL: Loading NULL Values from CSV Data

Challenge:

CSV data files often contain empty fields, which can be problematic when loading them into MySQL tables defined with NULLABLE columns. By default, MySQL assigns zero (0) to empty fields, even when the column default is NULL. This can lead to confusion when attempting to distinguish between NULL and zero values.

Solution:

To ensure that empty fields are interpreted as NULL values during data loading, consider using the following methods:

1. Use the NULLIF() Function:

Modify the LOAD DATA INFILE statement to read the empty fields into a local variable (@vfour) and then set the actual field value to NULL if the local 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,'')
;

This ensures that empty fields in the CSV file are interpreted as NULL in the MySQL table.

2. Read All Variables into Local Variables:

If all fields in the CSV file can be potentially empty, use multiple SET statements to assign NULL values to empty fields.

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

This approach allows for explicit handling of empty fields and ensures that they are interpreted as NULL values.

By utilizing these methods, you can effectively load NULL values from CSV data into MySQL tables, resolving the issue of misinterpretation of empty fields as zero values.

The above is the detailed content of How to Properly Load NULL Values from CSV Files into MySQL Tables?. For more information, please follow other related articles on the PHP Chinese website!