How Can I Handle NULL Values Instead of Zeros When Importing CSV Data with Empty Fields into MySQL Using LOAD DATA INFILE?

Handling NULL Values in MySQL LOAD from CSV with Empty Fields

MySQL's LOAD command offers a straightforward method to import data from CSV files into tables. However, when loading numerical data from CSV files containing empty fields, MySQL tends to insert zeros instead of NULL, even when the column is defined asnullable.

Consider the following CSV file (testdata.txt):

1,2,3,4,5
1,2,3,,5
1,2,3

And the following MySQL table (moo):

CREATE TABLE moo (
  one INT(1) NULL,
  two INT(1) NULL,
  three INT(1) NULL,
  four INT(1) NULL,
  five INT(1) NULL
);

When loading the CSV data into the moo table using the LOAD command:

LOAD DATA INFILE '/tmp/testdata.txt' INTO TABLE moo FIELDS TERMINATED BY "," LINES TERMINATED BY "\n";

The resulting table will contain:

+------+------+-------+------+------+
| one  | two  | three | four | five |
+------+------+-------+------+------+
| 1    | 2    | 3     | 4    | 5    |
| 1    | 2    | 3     | 0    | 5    |
| 1    | 2    | 3     | NULL | NULL |
+------+------+-------+------+------+

As you can observe, MySQL assigns zero to empty fields, which is incorrect when columns allow NULL values.

To address this issue and ensure that empty fields are correctly handled as NULL, utilize the NULLIF() function. NULLIF() takes two arguments: the field to be checked and the value to return if the field is empty. Here's how to use it:

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 example, the fourth field is read into a local variable @vfour. The SET statement then assigns four to NULL if @vfour is an empty string.

If multiple fields can be empty, read them all into local variables and utilize multiple SET statements:

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

By leveraging NULLIF(), MySQL LOAD can now correctly handle empty fields and assign NULL values, allowing you to accurately work with data in your MySQL tables.

The above is the detailed content of How Can I Handle NULL Values Instead of Zeros When Importing CSV Data with Empty Fields into MySQL Using LOAD DATA INFILE?. For more information, please follow other related articles on the PHP Chinese website!