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.
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';