mysql查询结果输出文件
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语句将查询输出保存到文件中。 当查询结果很大并且您想通过将其导出到文件中进行分析时，这非常有用。
MySQL Outfile语句语法 (MySQL outfile statement syntax)
The outfile syntax is very simple. It should be the last part of the SQL query.
outfile语法非常简单。 它应该是SQL查询的最后一部分。
{SQL Query} into outfile '{file_path}';
The SQL query output will be saved to the file in the text format.
SQL查询输出将以文本格式保存到文件中。
将MySQL查询输出保存到文件 (Saving MySQL Query Output to File)
Let’s look at some examples to save the MySQL query output to a file.
让我们看一些将MySQL查询输出保存到文件中的示例。
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.
输出将被重定向到文件。 它没有在MySQL控制台上打印。
If you open the file, you will see the SQL query data is saved as tab-separated.
如果打开文件，您将看到SQL查询数据另存为制表符分隔。
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:
档案内容：
错误情况 (Error Scenarios)
The MySQL user should have permissions to create the file at the specified path.
MySQL用户应具有在指定路径上创建文件的权限。
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.
如果文件已经存在，则MySQL查询将不会重写或追加到该文件中。 由于文件已经存在，它只会引发错误。
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 existsMariaDB [journaldev_wp]>
翻译自: /40480/mysql-query-output-to-file
mysql查询结果输出文件
