CODEWITHSUNDEEP
mysqlcsvnull
C.Liddell
C.Liddell

Reputation: 1082

MySQL "LOAD DATA INFILE" is importing unquoted "NULL" string as `NULL`

I'm using MySQL 5.7.35. If I use the LOAD DATA INFILE command on a CSV file with NULL as an unquoted string value in the CSV file, the value is imported as NULL in MySQL.

For example, if I import a CSV file with the following content:

record_number,a,b,c,d,e,f
1,1,2,3,4,5,6
2,NULL,null,Null,nUlL,,"NULL"

The imported table will have the following values:

+---------------+------+--------+--------+--------+--------+--------+
| record_number | a    | b      | c      | d      | e      | f      |
+---------------+------+--------+--------+--------+--------+--------+
|             1 | 1    | 2      | 3      | 4      | 5      | 6      |
|             2 | NULL | "null" | "Null" | "nUlL" | ""     | "NULL" |
+---------------+------+--------+--------+--------+--------+--------+

Is there any way to force column a, record 2, to be imported as a string without modifying the CSV file?

Update

@Barmar Pointed out that there's a paragraph in the MySQL documentation on this behavior here:

If FIELDS ENCLOSED BY is not empty, a field containing the literal word NULL as its value is read as a NULL value. This differs from the word NULL enclosed within FIELDS ENCLOSED BY characters, which is read as the string 'NULL'.

Upvotes: 0

Views: 1387

Answers (1)

Barmar
Barmar

Reputation: 780673

This is documented here:

If FIELDS ENCLOSED BY is not empty, a field containing the literal word NULL as its value is read as a NULL value. This differs from the word NULL enclosed within FIELDS ENCLOSED BY characters, which is read as the string 'NULL'.

So you need to specify the quoting character with something like FIELDS ENCLOSED BY '"' and then write "NULL" in the CSV file.

You could check for a NULL value in your code and convert it to a string.

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (record_number, @a, @b, @c, @d, @e, @f)
  SET a = IFNULL(@a, 'NULL'),
      b = IFNULL(@b, 'NULL'),
      c = IFNULL(@c, 'NULL'),
      d = IFNULL(@d, 'NULL'),
      e = IFNULL(@e, 'NULL'),
      f = IFNULL(@f, 'NULL')

However, this can't distinguish between an intentional NULL written as \N and MySQL treating NULL as NULL.

Upvotes: 2

Related Questions