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.

Using Windows with MySQL 5.1.

I am running a script that uses mysqldump to backup a database.

The mysqldump syntax used is:

mysqldump --user=root --port=3306 --password=topsecret some_database --tab=C:\ARCHIVE\some_database --triggers --routines --fields-enclosed-by=\"" --fields-terminated-by=, --lines-terminated-by=\r\n

This creates a text file and when I open it all NULL fields show as \n

Is there a setting for mysqldump that would export NULL fields as NULL?

share|improve this question
add comment

1 Answer 1

There are no options for handling the output of NULLs.

You may want to experiment with --hex-blob. This will dump BINARY, VARBINARY, BLOB fields in hexadecimal format. This should make the data portable. You can see what this option produces for NULL values.

mysqldump --user=root --port=3306 --password=topsecret --hex-blob some_database --tab=C:\ARCHIVE\some_database --triggers --routines --fields-enclosed-by=\"" --fields-terminated-by=, --lines-terminated-by=\r\n

Give it a Try and let us all know if this helps.

UPDATE 2012-01-13 18:26 EDT

This may sound very gross but you can pipe the output of mysqldump into sed as follows:

mysqldump --user=root --port=3306 --password=topsecret --hex-blob some_database --tab=C:\ARCHIVE\some_database --triggers --routines --fields-enclosed-by=\"" --fields-terminated-by=, --lines-terminated-by=\r\n | sed 's/\\N/NULL/g'

Of course, you cannot import that into MySQL until you want the string NULL to be the value to be imported.

share|improve this answer
    
I tried the --hex-blob flag but it didn't produce anything different than \n. This may be due that fields in the affected table aren't any of the formats you specified. –  John M Jan 13 '12 at 18:37
    
Excellent, now I have Rolando's password ;) –  atxdba Jan 14 '12 at 1:50
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.