Exporting MySQL Results to CSV

Before exporting the query results to a CSV file, you must firstly determine if the secure_file_priv option is set by running the following SQL query:

SHOW VARIABLES LIKE 'secure_file_priv';

If this value is set, the CSV file in the next step must be written to this directory.

To avoid issues with NULL values skipping a column in the CSV file, I replace NULL values to empty strings in the SELECT part of the query—e.g.

IFNULL(`your_table_name`.`your_column_name`, '')

At the very end of the query, the following SQL should be added to format the CSV file properly:

INTO OUTFILE '/secure_file_priv/path/csv-name.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Here is an example of how your query might look:

SELECT
    `customer`.`id` AS `customer_id`,
    IFNULL(`customer`.`name` '') AS `customer_name`
FROM
    `customer`
INTO OUTFILE '/secure_file_priv/path/customer-export.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';