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.
|
1 |
{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.
|
1 2 3 4 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
Error Scenarios
The MySQL user should have permissions to create the file at the specified path.
|
1 2 3 |
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.
|
1 2 3 |
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]> |