mysqlimport – Fix NULL Values Imported as Int 0

exportimportMySQLmysql-5.7

I would like to understand how to create a decent back-up file by using mysql and mysqlimport. I know that I can use mysqldump and mysql, but I'm learning to use the combination: mysql and mysqlimport.

I'm on mysql5.7.21 in Windows 10.
I have test table: aircrafts that looks like this:

enter image description here

I would like to export this table to a textfile trough the mysql client tool.
So I do this: 

mysql -h localhost -u root -D julian -p -e "SELECT * FROM `aircrafts` " > aircrafts.txt

This creates the back-up file, and content of it is: 

id  countryFactoryID    name    type    price
1   1   Cessna 172 Skyhawk  GA  60000
2   43  Diamond DA40    GA  NULL
3   1   Cirrus SR20 GA  NULL
4   1   Piper PA-28 Cherokee    GA  NULL
5   1   Beechcraft A23 Musketeer    GA  NULL
6   1   Grumman American AA-5 Traveller GA  NULL

What I noticed about the back-up file is that it uses NULL instead of \N.
But that's ok I guess.

Next, I import the back-up file like this:

mysqlimport --local -l -v --ignore-lines=1 -p -u root myDB aircrafts.txt

The output in CLI is: 

Connecting to localhost
Selecting database myDB
Locking tables for write
Loading data from LOCAL file: aircrafts.txt into aircrafts
myDB.aircrafts: Records: 6  Deleted: 0  Skipped: 0  Warnings: 6
Disconnecting from localhost

Ok, so far so good.

When I look in phpmyadmin, then I see this:

enter image description here

As you can see, the price is 0, but I would like to have a NULL-value, like in the first image. It's strange because my default value for column price is NULL.

What am I doing wrong?

Best Answer

This issue is caused by the way MySQL LOAD DATA handles NULLs:

Handling of NULL values varies according to the FIELDS and LINES options in use:

For the default FIELDS and LINES values, NULL is written as a field value of \N for output, and a field value of \N is read as NULL for input (assuming that the ESCAPED BY character is ).

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'.

If FIELDS ESCAPED BY is empty, NULL is written as the word NULL.

With fixed-row format (which is used when FIELDS TERMINATED BY and FIELDS ENCLOSED BY are both empty), NULL is written as an empty string. This causes both NULL values and empty strings in the table to be indistinguishable when written to the file because both are written as empty strings. If you need to be able to tell the two apart when reading the file back in, you should not use fixed-row format.

In your case NULL is considered as word 'NULL' and if you try to cast 'NULL' to number it will return 0. You need to change NULL to \N or enclose strings using some character.

Related Question