MySQL – LOAD DATA INFILE – Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

MySQLmysqldumpnull

I am selecting data from a database on server A, dumping the output into a file locally, and then doing a LOAD DATA INFILE on my local database. The bash command looks something like this:

mysql -e "SELECT * FROM items WHERE created_at > '2011-05-01';" > items

I was told this was the correct way when moving data from an external server to a local database. I think I would have normally chosen to use mysqldump, but I didn't want to fuss with curling the dumped file from the server to my local machine.

The data that is dumped to the items file with the command line code above contains a lot of NULLs. Those NULLs, when read by the LOAD DATA INFILE command, run locally, ends up loading a bunch of strings of "NULL" into my table. I want those to be properly translated into NULL without having to go into the items file and find/replacing "NULL" with "\N"

I need a way to do one of two things:

1) When selecting data from a mysql table, I would like the output to escape NULLS into the standard \N string that is read by LOAD DATA INFILE. In the interest succinctness and ease of maintenance, I want to avoid the type of code below.

SELECT IFNULL(x, '\N' ) AS x, IFNULL(y, '\N') AS y ...

2) When running a LOAD DATA INFILE, I would like it to treat the string NULL as null.

(SELECT * FROM items WHERE created_at > '2011-01-01')

Any thoughts or alternate methods are greatly appreciated.

Many Thanks,
Chad

Best Answer

Have you looked into the SELECT INTO OUTFILE syntax?

Here is a quote on the NULL string issue:

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

So make sure you indicate a FIELDS ESCAPED BY clause to get NULLs written as \N

Hope this helps

Related Question