Export data to CSV from MySQL

MySQL has a couple of options for exporting data: using the command line tool mysqldump (read my using mysqldump to save data to CSV files post for more details) and using a "SELECT ... INTO OUTFILE" SQL query. This post looks at the latter to export data from MySQL into a CSV file.

To dump all the records from a table called "products" into the file /tmp/products.csv as a CSV file, use the following SQL query:

SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products

Note that the directory must be writable by the MySQL database server. If it's not, you'll get an error message like this:

#1 - Can't create/write to file '/tmp/products.csv' (Errcode: 13)

Also note that it will not overwrite the file if it already exists, instead showing this error message:

#1086 - File '/tmp/products.csv' already exists

If you don't need quotes around all fields (e.g. numeric fields) then change "ENCLOSED BY" to "OPTIONALLY ENCLOSED BY" and MySQL will only put quotes around the fields that need them. Some systems require all fields in a CSV file to have quotes around them so you may need to export the data with quotes around them all depending on your requirements.

To only export a selected set of fields or data, change "SELECT *" to "SELECT field1, field2, etc" and add a WHERE clause after the FROM clause.