MySQL Export Table to CSV

Exporting data from MySQL to a CSV (Comma-Separated Values) file is a common task, useful for data analysis, backup, or transferring data to another system.

1. Export Table to CSV Using `SELECT ... INTO OUTFILE`

The simplest way to export a MySQL table to CSV is by using the SELECT ... INTO OUTFILE statement.

Syntax:

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

Example:

SELECT * INTO OUTFILE '/var/lib/mysql-files/products.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
FROM products;

Key Points:

✔ FIELDS TERMINATED BY ',' → Specifies column separation using commas.
✔ ENCLOSED BY '"' → Wraps each field in double quotes (to handle text with commas).
✔ LINES TERMINATED BY '\n' → Each row is written to a new line.
✔ The file is saved in MySQL's data directory (/var/lib/mysql-files/ by default).

⚠ Permissions Issue?
Ensure MySQL has permission to write to the output directory. Use:
SHOW VARIABLES LIKE 'secure_file_priv';
If secure_file_priv is set to a directory, save your file there.

2. Export Table to CSV Using `mysqldump` Command (CLI)

Another way to export a MySQL table to CSV is by using the mysqldump utility.

Command:

mysqldump -u root -p --tab=/path/to/output/ database_name table_name

This exports the table as .txt and .sql files in the specified folder.

3. Export MySQL Query to CSV Using `INTO OUTFILE`

You can export a custom query result instead of the entire table.

Example:

SELECT id, name, price INTO OUTFILE '/var/lib/mysql-files/products_filtered.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
FROM products WHERE price > 100;

4. Export MySQL Table to CSV Using `UNION ALL` for Column Headers

Since OUTFILE doesn’t include column headers, use UNION ALL to manually add them.

(SELECT 'id', 'name', 'price') 
UNION ALL 
(SELECT id, name, price FROM products)
INTO OUTFILE '/var/lib/mysql-files/products_with_headers.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

5. Export Table to CSV Using MySQL Workbench

Steps:

Open MySQL Workbench.
Go to Server > Data Export.
Select your database and table.
Choose CSV format.
Click Start Export.

6. Export MySQL Table to CSV Using `csv` Command in Linux

For a quick export, use:

mysql -u root -p -e "SELECT * FROM database_name.table_name" | sed 's/\t/,/g' > output.csv

Summary

Method	Pros	Cons
`SELECT ... INTO OUTFILE`	Fast, direct export	Requires file permissions setup
`mysqldump --tab`	Exports multiple tables	Only works on local servers
MySQL Workbench	User-friendly GUI	Slower for large tables
Linux `mysql` command	Quick command-line export	Requires additional formatting

Would you like help automating this process? 🚀

Souy Soeng

MySQL Export Table to CSV