You can save a query result to a .CSV file by using the
SELECT ... INTO OUTFILE statement.
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.
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 BY clause.
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';
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';