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