MySQL 5.7.26 does not accept \N for nullable int column using Load Data Infile

Question

MySQL does not accept the \N value as null when I'm sure I've used it in the past for the same purpose.

Steps:

CREATE TABLE t (i INT NULL, name varchar(50));

Create a file called test.txt with tab separated fields:

1   John
\N  Jim

In MySQL:

LOAD DATA INFILE '/var/lib/mysql-files/test.txt'
INTO TABLE t
Fields terminated BY "\t"
Escaped by ''
Lines terminated BY "\r\n";

Error:

ERROR 1366 (HY000): Incorrect integer value: '\N' for column 'i' at row 2

`'\N'` to me looks like a string. don't you want to use `NULL` — FalcoGer, May 29 '19 at 08:38
This might be a formatting problem of your text file. use utf-8 with an editor like notepad++, not windows notepad. — FalcoGer, May 29 '19 at 08:42
@FalcoGer I don't think encoding is the problem. I've just tested using a file created on Ubuntu using vi and I get the same error. The file is UTF-8 according to this [answer](https://stackoverflow.com/a/11021413/318807) — AJ Dhaliwal, May 29 '19 at 09:33

score 0 · Accepted Answer · answered May 29 '19 at 09:45

0

I should have done this:

LOAD DATA infile '/var/lib/mysql-files/test.txt'
INTO TABLE t
fields terminated BY "\t"
Escaped by '\\'
lines terminated BY "\r\n";

answered May 29 '19 at 09:45

AJ Dhaliwal

1 Answers1