How to Save MySQL Query Output to File?

Filed Under: SQL
Save Mysql Query Output To File

We can use the MySQL outfile statement to save the query output into a file. This is very useful when the query result is huge and you want to analyze it by exporting it into a file.

MySQL outfile statement syntax

The outfile syntax is very simple. It should be the last part of the SQL query.

{SQL Query} into outfile '{file_path}';

The SQL query output will be saved to the file in the text format.

Saving MySQL Query Output to File

Let’s look at some examples to save the MySQL query output to a file.

MariaDB [journaldev_wp]> select * from ahm_files into outfile '/tmp/ahm_files_data.txt';
Query OK, 270 rows affected (0.00 sec)

MariaDB [journaldev_wp]>

The output is getting redirected to the file. It’s not being printed on the MySQL console.

If you open the file, you will see the SQL query data is saved as tab-separated.

Let’s run another query that produces small output and compare the console output with the file contents.

MariaDB [journaldev_wp]> select id, title, access from ahm_files limit 5;
+----+--------------------------------------+--------+
| id | title                                | access |
+----+--------------------------------------+--------+
|  1 | Hibernate Log4j Project              | guest  |
|  2 | Hibernate EHCache Project            | guest  |
|  3 | Hibernate HQL Project                | guest  |
|  4 | Hibernate ManyToMany Mapping Project | guest  |
|  5 | Hibernate OneToMany Mapping Project  | guest  |
+----+--------------------------------------+--------+
5 rows in set (0.00 sec)

MariaDB [journaldev_wp]> 

File Content:

Mysql Query Output To File Content
Mysql Query Output To File Content

Error Scenarios

The MySQL user should have permissions to create the file at the specified path.

MariaDB [journaldev_wp]> select * from wp_posts into outfile '/root/wp_posts_data.txt';
ERROR 1 (HY000): Can't create/write to file '/root/wp_posts_data.txt' (Errcode: 13 "Permission denied")
MariaDB [journaldev_wp]>

If the file is already present, then the MySQL query will not rewrite or append to it. It will simply throw error as file is already present.

MariaDB [journaldev_wp]> select * from ahm_files into outfile '/tmp/ahm_files_partial_data.txt';
ERROR 1086 (HY000): File '/tmp/ahm_files_partial_data.txt' already exists
MariaDB [journaldev_wp]>

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages