MySQL SELECT to a CSV file

Lets use the following “SELECT” statement as an example

SELECT id,product_name,qty FROM orders

which returns three columns of data, the results can be placed into the file /orders/order_list.csv using the query:

SELECT id,product_name,qty
FROM orders
INTO OUTFILE '/orders/order_list.csv'

The result will be a tab-separated file, each row a new line. You can change the output behavior by doing the following:

SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/orders/order_list.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

This will result in the following output

"1","Tech-Recipes sock puppet","14.95"
"2","Tech-Recipes chef's hat","18.95"

Make sure the output file does not exit and that the mysql user have write permissions