How to Export MySQL Query Results to a CSV File?

Outputting MySQL Query Results in CSV Format

Often, you may need to export MySQL query results into a comma-separated values (CSV) format for further analysis or integration with other systems. To simplify this process, consider the following solution:

Execute the following command at the Linux terminal, replacing the appropriate values:

SELECT order_id,product_name,qty
INTO OUTFILE '/path/to/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

In more recent versions of MySQL, the query may need to be reordered as follows:

SELECT order_id,product_name,qty
INTO OUTFILE '/path/to/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

This command exports the results to the specified local file path in CSV format, with columns enclosed in double quotes and separated by commas. Note that column names are not included in the exported file.

If you need to export data from a remote MySQL server to your local machine, this solution is not suitable. Consider alternative methods, such as using a database migration tool or a third-party API.

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