How to Save a MySQL Query Result to a .CSV File

You can save a query result to a .CSV file by using the SELECT ... INTO OUTFILEstatement.

You specify the name/location of the file as well as other options, such as field terminators, line terminators, etc.

Here’s a basic example.

SELECT * FROM Customers
INTO OUTFILE '/tmp/customers.csv';

This selects all columns from the Customers table and puts them into a .CSV file called customers.csv in the /tmp directory.

Optional Extras

You can specify how fields and lines are terminated by using the FIELDS TERMINATED and LINES TERMINATED clauses.

You can also specify the characters to enclose each value by using the ENCLOSED BYclause.

And you can use the FIELDS ESCAPED BY clause to control how to write special characters.

Here’s the query again, this time using some of those extra clauses:

SELECT * FROM Customers
INTO OUTFILE '/tmp/customers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Adding Headers

The previous examples will generate a .CSV file with the results of the query, but not with the column names. This means the .CSV file won’t have headers on the first line.

To include the column names (so that the .CSV file contains headers on the first line), you can hardcode them in another SELECT statement, prepended to the rest of the query by a UNION ALL operator.

Here’s an example of another (slightly more complex) query. In this example, we add column headers to the .CSV file:

/* Add column headers */
SELECT 'OrderId','CustomerID','EmployeeID','OrderDate','RequiredDate','ShippedDate','ShipVia','Freight','ShipName','ShipAddress','ShipCity','ShipRegion','ShipPostalCode','ShipCountry','OrderID','ProductId','UnitPrice','Quantity','Discount'

UNION ALL

/* Now the actual query */
SELECT o.OrderId, o.CustomerID, o.EmployeeID, o.OrderDate, o.RequiredDate, o.ShippedDate, o.ShipVia, o.Freight, o.ShipName, o.ShipAddress, o.ShipCity, o.ShipRegion, o.ShipPostalCode, o.ShipCountry, od.OrderID, od.ProductId, od.UnitPrice, od.Quantity, od.Discount 

FROM `Orders` o  LEFT JOIN `Order Details` od ON od.OrderID = o.OrderID 

/* Save the query results to a file */
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';