MySQL INTO OUTFILE vs. INTO DUMPFILE

I haven’t had a MySQL post for awhile, so it’s time to add some variety to the blog.

There are a couple of different ways to export data with a SELECT query in MySQL: INTO OUTFILE and INTO DUMPFILE. Let’s use the MySQL Sakila sample database and walk through some examples to compare these two options.

INTO OUTFILE

INTO OUTFILE is good for when you want to export a table or set of query results. For example, you may want to export query results to a CSV file. If we wanted to save the entire actor table from the Sakila database, we could do that by running the following:

SELECT *
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/outfile_test.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM actor;

Following the path in our query, we’ll find a CSV file with the results:

INTO DUMPFILE

INTO DUMPFILE also works to export data but is more suited for exporting a row or value at a time. The exported file with INTO DUMPFILE isn’t going have delimited values in any way. What you’ll get as a result will be all values running together. If you’re only working with one value, that’s perfect. If you’re working with multiple rows of data, not so much.

If I try to use INTO DUMPFILE to get information from the actor table, I’ll get a file but also a message stating, “Result consisted of more than one row”

SELECT *
INTO DUMPFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/dumpfile_test.txt'
FROM actor;

MySQL tried to export data, but what’s in the file is not exactly readable:

If I make a change to my query so that I only select the last name where actor_id equals 1, I won’t get an error and will get that value:

SELECT last_name
INTO DUMPFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/dumpfile_test.txt'
FROM actor
WHERE actor_id = 1;

In a Nutshell

Your choice of INTO OUTFILE or INTO DUMPFILE will depend on the type of data you’re exporting. INTO OUTFILE is better for rows or tables, while INTO DUMPFILE is better suited for a single value.

Thanks for reading!