How to Export Table to CSV in MySQL Quickly

To export a table to CSV in MySQL, use the SELECT INTO OUTFILE statement with the file path and CSV formatting options. For example, SELECT * FROM table_name INTO OUTFILE '/path/file.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; exports the table data to a CSV file.

Syntax

The basic syntax to export a MySQL table to a CSV file uses the SELECT INTO OUTFILE statement. You specify the file path and how fields and lines are separated.

• SELECT * FROM table_name: selects all data from the table.
• INTO OUTFILE '/path/to/file.csv': writes the output to the specified file on the server.
• FIELDS TERMINATED BY ',': separates columns with commas.
• ENCLOSED BY '"': wraps each field in double quotes.
• LINES TERMINATED BY '\n': ends each row with a new line.

sql

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

Example

This example exports all rows from the employees table to a CSV file named employees.csv located in the MySQL server's secure file directory.

sql

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

Output

File '/var/lib/mysql-files/employees.csv' created with table data in CSV format.

Common Pitfalls

Common mistakes when exporting to CSV in MySQL include:

• Trying to write the file to a directory where MySQL does not have write permission.
• Using a file path that is not accessible or allowed by the MySQL server's secure_file_priv setting.
• Not enclosing fields properly, which can cause issues if data contains commas.
• Expecting the file to be created on the client machine instead of the MySQL server.

Always check the MySQL error message if export fails.

sql

/* Wrong: Writing to a path without permission */
SELECT * FROM employees
INTO OUTFILE '/root/employees.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

/* Right: Use MySQL secure file directory */
SELECT * FROM employees
INTO OUTFILE '/var/lib/mysql-files/employees.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Quick Reference

Clause	Description	Example
SELECT * FROM table_name	Selects all data from the table	SELECT * FROM employees
INTO OUTFILE 'file_path'	Saves output to a file on the server	INTO OUTFILE '/var/lib/mysql-files/data.csv'
FIELDS TERMINATED BY ','	Separates columns with commas	FIELDS TERMINATED BY ','
ENCLOSED BY '"'	Wraps each field in double quotes	ENCLOSED BY '"'
LINES TERMINATED BY '\n'	Ends each row with a new line	LINES TERMINATED BY '\n'

Key Takeaways

Use SELECT INTO OUTFILE with proper file path and permissions to export CSV.

Fields should be enclosed and separated correctly to handle special characters.

The output file is created on the MySQL server, not the client machine.

Check MySQL's secure_file_priv setting to know allowed export directories.

Always verify file permissions to avoid export errors.