Export table to csv in mysql

It is possible to export the contents of a table directly to csv format from within mysql. It is useful when migrating large tables. CSV export import is much faster for large tables compared to mysqldump/mysql command.

To generate csv from the table use the following command

SELECT id,name INTO OUTFILE '/tmp/result.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY 'n' FROM students WHERE 1;

Make sure that the path to the output file is writable by mysql.
If the output file already exists, it would say

ERROR 1086 (HY000): File '/tmp/result.csv' already exists

Hence first delete any older version of the file if it exists.

On windows the outfile path has to be mentioned in a different style

SELECT id,name INTO OUTFILE 'd:\backups\php\export\data.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY 'n' FROM students WHERE 1;

Rest is pretty much same.







Last Updated On : 12th March 2013

Subscribe to get updates delivered to your inbox

Leave a comment