How to Output MySQL Query Results as a CSV File?

Output MySQL Query Results as CSV

To retrieve data from MySQL in CSV format, modify your query using the following syntax, as recommended by the "Save MySQL query results into a text or CSV file" documentation:

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

Alternatively, you can reorder the syntax in more recent versions of MySQL as follows:

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:

• Column names will not be included in the exported results.
• The CSV file will be saved on the server running MySQL.
• The user running the MySQL process must have write permissions to the chosen directory.
• This solution may not be suitable for writing output to a local machine from a remote server, especially if it's hosted or virtualized.

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