SQL OUTFILE

What is SQL OUTFILE used for?

OUTFILE writes the result of a SELECT query (or exported table data) directly to a file on the database server.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL OUTFILE: Supported: MySQL, MariaDB. Partially supported: Percona Server. Not supported: PostgreSQL, SQL Server, Oracle, SQLite, Snowflake, BigQuery.

SQL OUTFILE Full Explanation

OUTFILE is a MySQL and MariaDB clause used with SELECT ... INTO OUTFILE and LOAD DATA OUTFILE statements. In SELECT ... INTO OUTFILE it streams the query's result set to a file that resides on the database server's filesystem. The file is created by the server process, so the path must be local to the server, writable by the server OS user, and not already exist (unless the OVERWRITE option in MySQL 8.0.22+ is used). Users must possess the FILE privilege. By default rows are written with tab-separated columns and newline-terminated rows, but export_options let you change field and line delimiters, quoting rules, and whether to generate an optional header row. The clause is frequently paired with CHARACTER SET to control encoding. The complementary LOAD DATA [LOCAL] INFILE clause can later re-import the produced file. OUTFILE happens entirely server-side, making it faster than client-side export for large datasets but requiring careful path and permission management. It is not part of the ANSI SQL standard and is unsupported in PostgreSQL, SQL Server, Oracle, or SQLite.

SQL OUTFILE Syntax

SELECT column_list
INTO OUTFILE 'absolute_or_relative_path'
[CHARACTER SET charset_name]
[export_options]
FROM table_name
[WHERE conditions];

SQL OUTFILE Parameters

column_list (list) - one or more columns or expressions to export
'file_path' (string) - destination file created on the server host
CHARACTER SET (keyword) - optional target character set for the output file
export_options (list) - optional clauses such as

Example Queries Using SQL OUTFILE

-- Export the whole users table to a CSV file
SELECT *
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM users;

-- Export orders from last month with UTF8 encoding and custom header
SELECT 'order_id','user_id','total','created_at'
UNION ALL
SELECT order_id,user_id,total,created_at
FROM orders
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
INTO OUTFILE '/var/lib/mysql-files/recent_orders.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

Expected Output Using SQL OUTFILE

A new file is created in the specified directory
Each row of the SELECT result appears as a line in the file, with columns separated and optionally quoted according to the chosen export options
No result set is returned to the client

Use Cases with SQL OUTFILE

Quickly dump large query results without client-side bandwidth limitations
Generate CSV extracts for downstream ETL jobs or reporting tools
Produce backup snapshots of lookup tables
Feed data into another MySQL instance using LOAD DATA INFILE

Common Mistakes with SQL OUTFILE

Using a path the MySQL server cannot write to or that already contains a file of the same name
Lacking the FILE privilege and receiving ER_DBACCESS_DENIED_ERROR
Attempting to place the file on the client machine instead of the server
Forgetting to quote fields that contain delimiters, leading to malformed CSVs
Assuming OUTFILE works in non-MySQL databases

First Introduced In

MySQL 3.23

Frequently Asked Questions

Does INTO OUTFILE write to the client or the server machine?

It always writes on the server host. To obtain the file locally you must copy or download it after creation.

What privilege is required to use OUTFILE?

The executing account needs the global FILE privilege, otherwise MySQL returns an access denied error.

Can OUTFILE overwrite an existing file?

Yes if you append the OVERWRITE keyword (MySQL 8.0.22+). Older versions require manually deleting the file first.

How do I include column headers in the exported file?

Either UNION ALL a literal header row with the result set or, in MySQL 8.0.20+, use the HEADER export option.