Export data to CSV from MySQL

0 Shares
0
0
0
0
0
0
0

To dump all the records from a table into the file /tmp/[tabelname].csv as a CSV file, use the following SQL query:
SELECT *

INTO OUTFILE '/tmp/[tabelname].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/[tabelname].csv' (Errcode: 13)

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

#1086 - File '/tmp/[tabelname].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.