MySQL - MySqlDump uses \n instead of null

Question

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?

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.