How to Use SELECT INTO OUTFILE in MySQL: Syntax and Examples

Use SELECT ... INTO OUTFILE 'file_path' in MySQL to export query results directly to a file on the server. Specify the file path and optional formatting options like FIELDS TERMINATED BY and LINES TERMINATED BY to control the output format.

Syntax

The basic syntax of SELECT INTO OUTFILE exports query results to a file on the MySQL server. You must specify the file path and can customize field and line separators.

• SELECT ...: The query to run.
• INTO OUTFILE 'file_path': Path where the file will be saved on the server.
• FIELDS TERMINATED BY 'char': Character to separate fields (default is tab).
• ENCLOSED BY 'char': Character to enclose fields (optional).
• LINES TERMINATED BY 'char': Character to separate lines (default is newline).

sql

SELECT column1, column2
INTO OUTFILE '/tmp/output.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM your_table;

Example

This example exports the id and name columns from the employees table into a CSV file named /tmp/employees.csv on the server. Fields are separated by commas and enclosed in double quotes.

sql

SELECT id, name
INTO OUTFILE '/tmp/employees.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM employees;

Output

File '/tmp/employees.csv' created with query results.

Common Pitfalls

Common mistakes when using SELECT INTO OUTFILE include:

• Trying to write to a file path where MySQL does not have write permission.
• Using a file path on the client machine instead of the server.
• Not specifying unique file names, which causes errors if the file already exists.
• Forgetting to set proper field and line terminators for the desired file format.

MySQL will throw an error if the file already exists, so you must delete or rename it before running the query again.

sql

/* Wrong: File path on client machine */
SELECT * INTO OUTFILE 'C:/Users/you/output.csv' FROM employees;

/* Right: File path on MySQL server */
SELECT * INTO OUTFILE '/var/lib/mysql-files/output.csv' FROM employees;

Quick Reference

Clause	Description
SELECT ...	The query to select data.
INTO OUTFILE 'file_path'	File path on the MySQL server to save output.
FIELDS TERMINATED BY 'char'	Character to separate fields (default tab).
ENCLOSED BY 'char'	Character to enclose fields (optional).
LINES TERMINATED BY 'char'	Character to separate lines (default newline).

Key Takeaways

SELECT INTO OUTFILE exports query results to a file on the MySQL server.

You must specify a file path where MySQL has write permission.

Customize output format with FIELDS TERMINATED BY and LINES TERMINATED BY.

The file must not already exist or MySQL will return an error.

File paths are relative to the server, not the client machine.