How to handle null values with blanks in mysql load outfile function?

Question

How to handle null values with blanks in MySQL load outfile function?

Below is the query

SELECT * FROM employees WHERE `date` BETWEEN '2016-12-15' AND '2017-01-04' INTO OUTFILE '/tmp/test.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY "" LINES TERMINATED BY ' ';

Null values in columns are shown as NULL in csv. How can i make this to blank?

I cannot use IFNULL(Col,'') as columns are fetched dynamically and a single function is written to extract data from various tables.

if($table == 'employees'){
    $this->Target = ClassRegistry::init( $table );
    $this->Target->useTable = $table;
    $allColumnHeaders = $this->Target->getColumnTypes();
    $header_fields = implode( "','", array_keys( $allColumnHeaders ) );
    $sql = " Select '" . $header_fields . "' UNION ALL SELECT $fields FROM " . $table . " WHERE date BETWEEN '" . $start_date . "' AND '" . $end_date . "'"

    $sql = $sql . " INTO OUTFILE '" . $csvFile . "' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '' LINES TERMINATED BY '\n';";
    $result = $this->db->query( $sql );
}

Answer 1

One of three ways:

IFNULL

IFNULL(Col, '')

COALESCE

COALESCE(Col, '')

CASE Statement

CASE WHEN Col IS NULL THEN '' ELSE Col END AS Col