4

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

1 Answer 1

2

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

2
  • @Chad, this answer makes sense to me. You can also use mysqldump to set \N by using --fields-escaped-by="\N". Using the SELECT INTO OUTFILE keeps control of this data file creation within SQL scripts and not out in Linux, which requires a separate DB Connection. Jul 18, 2011 at 17:20
  • Hey guys, Thanks for the responses. It was exactly what I needed.
    – Chad M
    Jul 27, 2011 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.