How to export data from Mysql to CSV/TSV

Export all data from table

In order to export all data from table and save it into /tmp/dump.csv:

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

Make sure /tmp dir (or other you pick) is available for writing for Mysql.

Export data from custom select

This is easy, just use standard SELECT query:

SELECT col1, col2 INTO OUTFILE '/tmp/dump.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'
FROM table WHERE col1 > 1 ORDER BY col2;

This will store col1, col2 data from a result set of a query SELECT...FROM table WHERE col1 > 1 ORDER BY col2.

Export data into TSV

TSV is a tab separated format, so all we have to do is to set \t as a field termination symbol:

SELECT * INTO OUTFILE '/tmp/dump.csv'
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
FROM table;

Load CSV data back into table

Let's load data from /tmp/dump.csv into table:

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE table
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'

This is a great way to load large amounts of data into Mysql tables.