SQL Help

Save query output to a file — with column headers

Updated August 2026 · First published 2007 by David J. Lake

Working on a server over SSH with no GUI, you'll constantly want a query's results in a file — and once more than one table is involved, you want the column headers in there too, so next week you still know what each column was.

The one-liner: mysql -e

Run the query non-interactively with -e and redirect. When output goes to a file or pipe, the client automatically switches to batch mode: tab-separated values, headers included as the first line:

mysql -u rob -p mydb -e "SELECT id, name, created_at FROM customers" > customers.tsv

That's the whole trick. Useful companions:

• --skip-column-names (or -N) — when you don't want the header line (feeding ids into a shell loop, say).
• --batch (-B) — force batch mode explicitly.
• --table (-t) — keep the pretty ASCII grid instead of tabs, headers and all, for pasting into a ticket or email.
• -e "source myquery.sql" — keep a long query in a file rather than wrestling shell quoting.

Prefer CSV? Post-process the tabs:

mysql -u rob -p mydb -e "SELECT ..." | sed 's/\t/,/g' > report.csv

(Good enough for clean data; if fields may contain commas, quotes or newlines, use a proper tool — mysqlsh below, or import the TSV directly, which spreadsheets handle fine.)

Why not SELECT INTO OUTFILE?

SELECT id, name, created_at
FROM customers
INTO OUTFILE '/var/lib/mysql-files/customers.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

It has its place — the server writes the file, fast, with real CSV quoting. But three catches: the file lands on the server's disk, not yours; secure_file_priv restricts (or on many managed/shared hosts, completely blocks) where it may write; and it emits no header row. The traditional workaround is gluing one on with a UNION ALL:

SELECT 'id', 'name', 'created_at'
UNION ALL
SELECT id, name, created_at FROM customers
INTO OUTFILE '/var/lib/mysql-files/customers.csv' ...

— which works, but forces every column to text and puts the header row at the mercy of the ordering. If you're reaching for that hack just to get headings, the mysql -e one-liner is simply better.

MySQL Shell does it natively

If mysqlsh is available, its dump utility produces proper CSV/TSV with headers in one call:

mysqlsh --uri rob@localhost/mydb --sql \
  -e "util.exportTable('customers', '/tmp/customers.csv', {dialect: 'csv'})"

For everyday "get this query into a file with its headings", though, the humble mysql -e ... > file remains the fastest thing you can type.