MySQL - Exporting data as CSV or TSV

The example below shows how to export the entire data from a table into a CSV/TSV file.

# Generate CSV file

use database_name;

SELECT *
FROM table_name
INTO OUTFILE ‘/path/to/dumpfile.csv’
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

# Generate TSV file

use database_name;

SELECT *
FROM table_name
INTO OUTFILE ‘/path/to/dumpfile.tsv’
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

If you need customized/filtered data (filtering data with where condition) then the SQL command is as follows


# Generate CSV file

use database_name;

SELECT *
FROM table_name
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

INTO OUTFILE ‘/path/to/dumpfile.csv’
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

# Generate TSV file

use database_name;

SELECT *
FROM table_name
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
INTO OUTFILE ‘/path/to/dumpfile.tsv’
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';