replace \N in csv dump of a sql file

mysql, php, sql

Solution

If the FIELDS ESCAPED BY character is empty (in your query), no characters are escaped and NULL is output as NULL, not \N.

So following should give you desired result:

$sql = "SELECT 'id','username','name','email',
    'phone_number','city',
    'batch_code','course_type' UNION ALL
    SELECT id, username, name, email,
    phone_number, city, batch_code, course_type
    FROM users INTO OUTFILE '".$file_path."' ".
        "FIELDS TERMINATED BY ','
        ENCLOSED BY '\"'
        ESCAPED BY ''
        LINES TERMINATED BY '\r\n'";

It's just one way to do it and you might not want to use this if you need other characters escaped.

Problem

I use the following to dump a sql file in csv format : $file_path = "/tmp/app_users.csv"; ``` $sql = "SELECT 'id','username','name','email', 'phone_number','city', 'batch_code','course_type' UNION ALL SELECT id, username, name, email, phone_number, city, batch_code, course_type FROM users INTO OUTFILE '".$file_path."' ". "FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n'"; $db = Zend_Registry::get('db'); $stmt = $db->query($sql, array()); ``` But certain fields in the users table are null, which in the csv output is shown as \N. I want it to be replaced with a string "NULL". I tried to use str_replace, but it mysteriously inserts an extra blank line after every line.

Original source