Export data to CSV from MySQL
Posted April 8th, 2009 in 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.
- Sending a CSV file to the web browser with PHP (Monday, April 13th 2009)
- Create a CSV file from MySQL with PHP (Sunday, April 12th 2009)
- Selectively dumping data with mysqldump (Friday, October 17th 2008)
- Restore data from a tab delimited file to MySQL (Sunday, December 16th 2007)
- Using mysqldump to save data to CSV files (Saturday, December 15th 2007)
- Backing up MySQL with mysqldump (Friday, December 14th 2007)