Dump MySQL table or SELECT resultset into CSV or TSV file

Full table select:


SELECT * INTO OUTFILE '/tmp/file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM `table`;

Variations(for instance select from derived table):

SELECT tbl.* INTO OUTFILE '/tmp/file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM (
SELECT t1.category_name, t2.full_name
FROM table1 t1
LEFT JOIN table2 t2 ON t1.category_id = t2.category_id
) AS tbl;

To add a heading row into the exported by MySQL CSV file you can use UNION statement:

SELECT 'Column Name 1', 'Column Name 2'
UNION
(
SELECT tbl.column1, tbl.column2 INTO OUTFILE '/tmp/file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table1 tbl
);

Note: /tmp/file.csv must writable by mysql process

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>