How Can I Efficiently Export MySQL Query Results as a CSV File?

Querying MySQL and Exporting Results as CSV

Exporters often require MySQL query results in CSV format for further processing or data analysis. However, piping query results through sed can become cumbersome and error-prone, especially when dealing with quoted data.

MySQL provides a more straightforward and efficient method to output query results as CSV using the INTO OUTFILE statement. For instance, the following command generates a CSV file with properly quoted data:

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

Note that the INTO OUTFILE statement may need to be reordered in newer MySQL versions.

This approach has several advantages:

• CSV Output: The results will be directly written to a CSV file, removing the need for post-processing.
• Proper Quoting: Columns with quoted data will be automatically escaped.
• Local Export (if necessary): The CSV file will be located on the MySQL server by default. To export results to your local machine from a remote server, consider alternative methods such as using the LOAD DATA INFILE statement.

The above is the detailed content of How Can I Efficiently Export MySQL Query Results as a CSV File?. For more information, please follow other related articles on the PHP Chinese website!