Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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

share|improve this answer
    
@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. –  RolandoMySQLDBA Jul 18 '11 at 17:20
    
Hey guys, Thanks for the responses. It was exactly what I needed. –  Chad M Jul 27 '11 at 11:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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