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.