Exporting MySQL Query Results to CSV With INTO OUTFILE

MySQL has a built-in way to write a query result to a file, SELECT ... INTO OUTFILE, and every MySQL tutorial that mentions CSV uses it. It needs no extra software and it has several properties that make it a poor way to produce CSV files for other people. This article exports the same awkward eight-row table that the other export articles use, reads each file back with a CSV parser, and counts how many rows survive. The numbers are not flattering to the recipe you will find first.

What INTO OUTFILE does, and where

The statement runs on the server. The file is written by the MySQL server process, on the server's file system, as the operating system user the server runs as. You do not get it on your machine unless your machine is the server. And the server will only write where secure_file_priv allows:

mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 -e "SELECT * FROM ex_people INTO OUTFILE '/tmp/ex.csv' FIELDS TERMINATED BY ','" 2>&1 | grep -v Warning
mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 -e "SELECT @@secure_file_priv, @@version" 2>&1 | grep -v Warning

ERROR 1290 (HY000) at line 1: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
@@secure_file_priv	@@version
/var/lib/mysql-files/	8.4.11

Writing to /tmp failed with ERROR 1290 ... The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, and the setting on this server is /var/lib/mysql-files/. The error 1290 article covers the loading side of the same setting. On many managed services you have no file system to write to, and INTO OUTFILE is restricted or unavailable.

It also refuses to overwrite:

mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 -e "SELECT * FROM ex_people INTO OUTFILE '/var/lib/mysql-files/twice.csv' FIELDS TERMINATED BY ','" 2>&1 | grep -v Warning
mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 -e "SELECT * FROM ex_people INTO OUTFILE '/var/lib/mysql-files/twice.csv' FIELDS TERMINATED BY ','" 2>&1 | grep -v Warning

ERROR 1086 (HY000) at line 1: File '/var/lib/mysql-files/twice.csv' already exists

The second run to the same path stopped with ERROR 1086 ... File '/var/lib/mysql-files/twice.csv' already exists. That is a safety feature and an annoyance in a scheduled job: the path needs a date or a run id in it, and old files need removing by something that has access to the server's disk.

The recipe from every tutorial

The first answer to "export MySQL to CSV" is a statement like this, with ESCAPED BY '"' to make quotes inside fields come out doubled, as CSV wants them:

cat -A work/export/mysql_v1.csv | sed -n 4,7p
python lab/export/roundtrip.py work/export/mysql_v1.csv

4,"Cy","line one"$
line two",1,"N,"2025-03-04 12:00:00"$
5,"Di","",0,3.14,"2025-03-05 08:15:30"$
6,"Ed","N,"N,100.00,"N$
8 records, field counts [3, 6, 9]
4 of 8 name/note pairs read back exactly
  row 4: wanted ('Cy', 'line one\nline two'), got ('Cy', 'line one')
  row 5: wanted ('Di', ''), got ('1', 'N,2025-03-04 12:00:00"')
  row 6: wanted ('Ed', ''), got ('Di', '')
  row 7: wanted ('Zoë Ünal', 'ünïcode ✓ ñ'), got ('Ed', 'N,N')

I exported the eight rows with exactly that clause and read the file back with Python's csv module. Only 4 of 8 name and note pairs came back right, and the record widths were 3, 6 and 9 instead of 6. The excerpt shows why. Row 4 is 4,"Cy","line one" followed by a line break, line two",1,"N,"2025-.... The ESCAPED BY character is also the escape for other special characters, so the newline in the note was written as a quote followed by a newline, which a CSV parser reads as the end of the field. The NULL in score was written as "N: the escape character followed by an N, with no closing quote. Every record after that point shifts. The file looks plausible at the top and is wrong in the middle.

This is the worst kind of export failure: nothing raised an error, the row count of the file is close to correct and a spreadsheet will open it. The recipe is fine for data that has no newlines and no NULLs, which is probably the data of the people who wrote it. (It handled the quotes in row 3 correctly.)

Without an escape clause

Leave out ESCAPED BY and MySQL uses its default, a backslash. Those conventions are the ones LOAD DATA expects, and they are not RFC 4180:

cat -A work/export/mysql_v2.csv | sed -n 3,7p
python lab/export/roundtrip.py work/export/mysql_v2.csv

3,"Bo \"Bee\" Chen","says \"hi\"",1,7.00,"2025-03-03 00:00:00"$
4,"Cy","line one\$
line two",1,\N,"2025-03-04 12:00:00"$
5,"Di","",0,3.14,"2025-03-05 08:15:30"$
6,"Ed",\N,\N,100.00,\N$
8 records, field counts [6]
5 of 8 name/note pairs read back exactly
  row 3: wanted ('Bo "Bee" Chen', 'says "hi"'), got ('Bo \\Bee\\" Chen"', 'says \\hi\\""')
  row 4: wanted ('Cy', 'line one\nline two'), got ('Cy', 'line one\\\nline two')
  row 6: wanted ('Ed', ''), got ('Ed', '\\N')

Now the record widths are all 6, and 5 of 8 pairs read back correctly. NULL is written as \N, an embedded quote as \" (backslash-quote, not two quotes), and a newline as a backslash followed by a real line break. These are MySQL's own conventions. They are the format that LOAD DATA expects, and no CSV parser understands them. Bo "Bee" Chen read back as Bo \Bee\" Chen". A file written like this is MySQL's private format that happens to use commas. The backslash itself is escaped too:

echo "in the table:"; mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 -N -B -r -e "SELECT id, path FROM ex_paths" 2>/dev/null
echo "default ESCAPED BY:"; mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 -N -B -r -e "SELECT LOAD_FILE('/var/lib/mysql-files/paths_a.csv')" 2>/dev/null
echo "ESCAPED BY '':"; mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 -N -B -r -e "SELECT LOAD_FILE('/var/lib/mysql-files/paths_b.csv')" 2>/dev/null

in the table:
1	C:\temp\new
default ESCAPED BY:
1,"C:\\temp\\new"

ESCAPED BY '':
1,"C:\temp\new"

A path C:\temp\new in the table became C:\\temp\\new in the file. A consumer that does not know the convention has changed the data.

Making it RFC 4180

To get something a CSV parser reads, turn off MySQL's escaping and do the escaping yourself, in the query: double the quotes in every text column, and enclose all the fields.

cat -A work/export/mysql_v3.csv | sed -n 3,7p
python lab/export/roundtrip.py work/export/mysql_v3.csv

"3","Bo ""Bee"" Chen","says ""hi""","1","7.00","2025-03-03 00:00:00"$
"4","Cy","line one$
line two","1",NULL,"2025-03-04 12:00:00"$
"5","Di","","0","3.14","2025-03-05 08:15:30"$
"6","Ed",NULL,NULL,"100.00",NULL$
8 records, field counts [6]
7 of 8 name/note pairs read back exactly
  row 6: wanted ('Ed', ''), got ('Ed', 'NULL')

With ESCAPED BY '' and REPLACE(col, '"', '""') on the text columns, 7 of 8 pairs matched, and the backslash survived ("C:\temp\new" above). Row 4 now has its newline inside quotes, and Bo ""Bee"" Chen is right. The eighth pair differs because a NULL is written as the word NULL: with ESCAPED BY '', MySQL stops using \N and prints NULL, unquoted. A reader sees the text NULL in the note, where the table has a missing value. That is a defensible convention if you document it and your loader knows it. If you do not want it, wrap each nullable column in IFNULL(col, ''), and accept that NULL and the empty string become the same thing.

The price of this is a long SELECT list with a REPLACE on every text column, and a file that quotes numbers as well ("1", "7.00"), since ENCLOSED BY without OPTIONALLY encloses every non-NULL value. Both are cosmetic, and the file is correct.

No header row

INTO OUTFILE writes no column names. The usual workaround is to UNION ALL a row of literal names in front of the data:

mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 -N -B -r -e "SELECT id, name, note FROM (SELECT 0 AS o, 0 AS k, 'id' AS id, 'name' AS name, 'note' AS note UNION ALL SELECT 1, id, id, name, note FROM ex_people) t ORDER BY o, k" 2>&1 | grep -v Warning | head -n 4

id	name	note
1	Ana Silva	plain
2	O'Neil, Pat	has, a comma
3	Bo "Bee" Chen	says "hi"

The statement needs an ordering key, because a UNION gives no promise about where its rows end up. The derived table carries a column o that is 0 for the header row and 1 for data, and a column k that holds the real id for sorting, then sorts by o, k and selects only the data columns. Without k, the ids would be sorted as the strings they became after the union, which puts 10 before 2. Also note that a number in a union with a string column is converted to a string, so the output looks the same but the types in the query are not.

The client-side alternatives

mysql --batch writes tab-separated text, with a header, from the client machine:

mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 --batch -e "SELECT * FROM ex_people ORDER BY id" 2>/dev/null

id	name	note	active	score	joined
1	Ana Silva	plain	1	12.50	2025-03-01 09:30:00
2	O'Neil, Pat	has, a comma	0	0.00	2025-03-02 23:59:59
3	Bo "Bee" Chen	says "hi"	1	7.00	2025-03-03 00:00:00
4	Cy	line one\nline two	1	NULL	2025-03-04 12:00:00
5	Di		0	3.14	2025-03-05 08:15:30
6	Ed	NULL	NULL	100.00	NULL
7	Zoë Ünal	ünïcode ✓ ñ	1	1234.50	2025-03-07 17:45:00
8	Fay	 padded 	1	-5.25	2025-03-08 01:02:03

It is TSV, not CSV: line one\nline two has a backslash and an n instead of a line break, NULL is the word NULL, and booleans are 1 and 0. It read back correctly in 6 of 8 pairs when parsed as TSV. Miller can convert it, and it also unescapes the \n:

mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 --batch -e "SELECT id, name, note FROM ex_people WHERE id IN (3, 4, 6) ORDER BY id" 2>/dev/null | mlr --itsv --ocsv cat

id,name,note
3,"Bo ""Bee"" Chen","says ""hi"""
4,Cy,"line one
line two"
6,Ed,NULL

That is correct CSV for the three rows requested, and the NULL is still the text NULL.

When the output has to be right, I would write a few lines of client code. This script runs the query with a streaming cursor (rows are fetched as they are written, not held in memory), and lets Python's csv module do the quoting:

"""Export a MySQL query to RFC 4180 CSV from the client, streaming, NULL as empty, quoting left to the csv module."""
import csv, os, sys
import pymysql

query, out = sys.argv[1], sys.argv[2]
conn = pymysql.connect(host="mysql", user="root", password="secret", database="lab", charset="utf8mb4",
                       cursorclass=pymysql.cursors.Cursor if os.environ.get("BUFFERED") else pymysql.cursors.SSCursor)
with conn.cursor() as cur, open(out, "w", newline="", encoding="utf-8") as f:
    cur.execute(query)
    w = csv.writer(f, lineterminator="\n")
    w.writerow([d[0] for d in cur.description])
    n = 0
    for row in cur:
        w.writerow(["" if v is None else v for v in row])
        n += 1
print(f"{n} rows written to {out}")

python lab/export/mysql_export.py "SELECT * FROM ex_people ORDER BY id" work/export/mysql_py.csv
python lab/export/roundtrip.py work/export/mysql_py.csv
cat -A work/export/mysql_py.csv | sed -n '5,7p'

8 rows written to work/export/mysql_py.csv
8 records, field counts [6]
8 of 8 name/note pairs read back exactly
4,Cy,"line one$
line two",1,,2025-03-04 12:00:00$
5,Di,,0,3.14,2025-03-05 08:15:30$

8 of 8 pairs read back. The newline is inside a quoted field (the cat -A lines show line one$ and line two"), and NULL and the empty string are both written as an empty field, which is a choice made on the line that replaces None. Change it to write another marker if you need to tell them apart. The file is written on your machine, with no secure_file_priv, no overwrite error and no dependence on the server's file system. For tables of ordinary size it is the approach I would pick. The same code with a different cursor class exports millions of rows, as the large-table article measures.

How to tell it worked

Read the file with a CSV parser and compare the record count and the field-count histogram, as in the counting article, before looking at any values. Then compare the awkward rows. Put at least one row in a test table with each of: a comma, a double quote, a newline, a backslash, a NULL, an empty string and a non-ASCII character, and export that table with your recipe. If you cannot read those rows back exactly, you have found out cheaply.

When INTO OUTFILE is the right tool

When the data is plain (numbers, short strings without quotes or newlines, no NULLs), when the consumer is another MySQL server running LOAD DATA, and when you are on the database host, INTO OUTFILE with its default conventions is the matching pair for LOAD DATA. That is what it was built for. For anything else, the file leaves MySQL and goes to a program that follows RFC 4180, and the recipe has to make that true. Test it with a table that has the awkward rows before you rely on it.