Introduction

MySQL SELECT ... INTO OUTFILE writes query results to a server-side file, but it does not include column headers automatically. If you want a header row, you have to generate it yourself in the query or export the data with a client-side tool that prints column names.

What INTO OUTFILE Does by Default

The command exports rows only. It does not inspect your result set and prepend the column names.

Basic example:

1SELECT id, name, email
2INTO OUTFILE '/tmp/users.csv'
3FIELDS TERMINATED BY ','
4OPTIONALLY ENCLOSED BY '"'
5LINES TERMINATED BY '\n'
6FROM users;

The file contains data rows, not:

id,name,email
...

That behavior is normal. If you need headers, you must add them explicitly.

The Standard SQL Workaround: UNION ALL

The usual solution is to union a first row of literal column names with the real result set.

1(SELECT 'id', 'name', 'email')
2UNION ALL
3(SELECT
4    CAST(id AS CHAR),
5    name,
6    email
7 FROM users)
8INTO OUTFILE '/tmp/users.csv'
9FIELDS TERMINATED BY ','
10OPTIONALLY ENCLOSED BY '"'
11LINES TERMINATED BY '\n';

This produces a header row followed by the data rows.

The CAST on id matters because a UNION combines columns into one result shape. When your first row is text literals, explicitly casting numeric or date columns to character types avoids type surprises and keeps the output predictable.

A Cleaner Example with More Than One Type

Suppose the query includes integers, dates, and nullable fields:

1(SELECT 'order_id', 'customer', 'created_at', 'total')
2UNION ALL
3(SELECT
4    CAST(order_id AS CHAR),
5    customer_name,
6    DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s'),
7    CAST(total AS CHAR)
8 FROM orders)
9INTO OUTFILE '/tmp/orders.csv'
10FIELDS TERMINATED BY ','
11OPTIONALLY ENCLOSED BY '"'
12LINES TERMINATED BY '\n';

Formatting each exported value deliberately is often better than relying on MySQL's default text representation, especially when the CSV will be consumed by another system.

When Client-Side Export Is Better

If you do not specifically need the server to write the file, a client-side export is often simpler because the client can include column names naturally.

For example:

mysql -u app -p --batch --raw \
  -e "SELECT id, name, email FROM users" mydb > users.tsv

By default, the MySQL client includes a header row unless you pass -N or --skip-column-names. This is often easier than constructing a UNION ALL query, especially when exporting ad hoc reports.

The tradeoff is that the output is written by the client, not the MySQL server, so file permissions and paths are handled on the client machine instead.

Server-Side Constraints You Still Need to Respect

SELECT ... INTO OUTFILE writes on the database server host, not on your laptop or application host unless they are the same machine.

You also need to account for:

• the MySQL FILE privilege
• the secure_file_priv restriction
• the fact that MySQL will not overwrite an existing file

To check the restricted export directory:

SHOW VARIABLES LIKE 'secure_file_priv';

If MySQL allows exports only into a specific directory, your outfile path must be inside that directory.

CSV Is Easy to Get Slightly Wrong

If values may contain commas, quotes, or newlines, use delimiter and quoting options that match the consumer's expectations.

A common CSV pattern is:

1FIELDS TERMINATED BY ','
2OPTIONALLY ENCLOSED BY '"'
3ESCAPED BY '"'
4LINES TERMINATED BY '\n'

But do not assume one set of options works for every downstream tool. Some consumers expect tabs, some expect no quote escaping, and some are better served by TSV instead of CSV.

Common Pitfalls

The biggest mistake is expecting INTO OUTFILE to have a built-in "include headers" switch. It does not.

Another mistake is forgetting that the file is written on the MySQL server host. Developers often look for /tmp/users.csv on the wrong machine.

A third common issue is not casting or formatting columns in the UNION ALL version. Mixed column types can lead to confusing output or implicit conversions you did not intend.

Finally, remember that MySQL will not overwrite an existing outfile. If the file already exists, the query fails until you remove or rename it.

Summary

• 'SELECT ... INTO OUTFILE does not include column headers by default.'
• To add headers, prepend a literal header row with UNION ALL.
• Cast or format non-string columns explicitly when combining them with header strings.
• If server-side export is not required, the mysql client is often an easier way to export with headers.
• Check secure_file_priv, file permissions, and output path location before debugging the query itself.