Oftentimes, you need to save and export query results to a file for storage, reporting, or additional processing. Commonly, CSV file types are a great choice for portability among other reasons. This blog post will cover using MySQL SELECT INTO syntax to generate these types of files.

Photo by Samuel Zeller on Unsplash

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
OS and DB used:

Saving query results into ‘flat-files’ is easily accomplished in MySQL using another variant of SELECT INTO syntax. However, there is some built-in resistance from the server when writing the file.

In MySQL SELECT INTO Syntax Part 1 – Uses With Variables. , we learned of but one use for SELECT INTO syntax within MySQL. In this second part of the SELECT INTO series of posts, we will know the caveats commonly associated with this version of the syntax and how to circumvent such.

Up first, here is an example of a simple SELECT INTO OUTFILE statement saving all returned query results from table demo :

With the above error, MySQL is basically letting me know that there is only a certain allowable location I can write files to.
This SHOW VARIABLES LIKE statement will reveal the writable location according to my install:

Visit, Determine secure-file-priv variable path in MySQL with these 2 commands. I wrote for an additional command you can use and determine the location.

Admittedly, I am unfamiliar with this particular error in MySQL. Which led me to numerous searches where I located suggestions targeting how to circumvent this ‘issue’.

However, I have decided (for now) to devote time and focus, for this blog post, on SELECT INTO OUTFILE syntax versus covering the necessary server configuration to properly configure an optimal setting for this type of error. Thoughts, suggestions, and any corrections are welcomed from the community and readers in the comments below.

Honestly, I am not well-versed in DBA type tasks , as I tend to concentrate on more of a Developer role and related duties. Yet, as my knowledge-base grows perhaps this topic can be a future blog post.

With that being said, for any user to successfully execute, SELECT INTO OUTFILE , they must have the FILE privilege.
The current logged in user has this needed privilege seen with the below SHOW GRANTS command:
(Note: I have not included all available output resulting from this command indicative to this user, for on-screen viewing and brevity.)

Knowing the writable directory, I’ll reissue the same command, including FIELDS and LINES clauses, to a file I name select1.csv (Note: The destination file should not already exist on the server prior to writing.):

Once completed, I’ll head over to a Linux terminal to view the contents of the created file (Note: To enter the directory and view the contents of the file, is executed as the root user of my Linux install, so you may be required to as well. CAUTION – Exercise extreme caution using root privileges and account in Linux. ):

Works no problem, producing a CSV file with all query results as intended.
In the next example, I include a WHERE clause and filter the results being written to the designated file for demonstration purposes that the chosen query can be more complex than a generic SELECT ‘everything’ (although this query is far from complex):

In-depth information on the given clauses in the command can be found at, 13.2.6 LOAD DATA INFILE Syntax in the official documentation. I will list out the defaults for each should you not include these clauses in a SELECT INTO OUTFILE command, yet their coverage is beyond the scope of this blog post.

Continued Reading and Supporting Documentation

This blog post has covered a second syntax for the SELECT INTO command in MySQL. In a forthcoming blog post, I will explore a third form so be sure and look for that post as well.

Like what you have read? See anything incorrect? Please share your thoughts and comments below. Thanks for reading!!!

Explore the official MySQL 5.7 Online Manual for more information.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.