How to Properly Load MySQL Tables with NULL Values from CSV Files Containing Empty Fields?

Loading MySQL Tables with NULL Values from CSV Data

When importing CSV data into a MySQL table, it's essential to handle NULL values correctly to maintain data integrity. Consider the following situation:

You have a table named "moo" with several numerical columns, all of which allow NULL values. When loading data from a CSV file, you encounter empty fields within the columns. However, instead of populating these fields with NULLs, MySQL assigns zeros to them. This can lead to data inconsistencies when attempting to differentiate between NULLs and zeros.

To resolve this issue, use the following approach:

1. Read the empty fields into local variables.
2. Check if the local variables are empty strings (equivalent to NULL).
3. Set the actual table field values to NULL if the local variables are empty strings.

Here's an example statement:

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,'')
;

In this statement, the fourth field ("four") is read into the local variable @vfour. If @vfour contains an empty string, the fourth field in the table is set to NULL using the NULLIF() function.

If you have multiple columns with potentially empty fields, you can read them all into variables and use multiple SET statements to handle NULL values accordingly.

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