MySQL 将sql查询结果写入mysql中的文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6332994/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
write results of sql query to a file in mysql
提问by Ramy
I'm trying to write the results of a query to a file using mysql. I've seen some information on the outfile construct in a few places but it seems that this only writes the file to the machine that MySQL is running on (in this case a remote machine, i.e. the database is not on my local machine).
我正在尝试使用 mysql 将查询结果写入文件。我在几个地方看到了一些关于 outfile 构造的信息，但似乎这只会将文件写入运行 MySQL 的机器（在这种情况下是远程机器，即数据库不在我的本地机器上）。
Alternatively, I've also tried to run the query and grab (copy/paste) the results from the mysql workbench results window. This worked for some of the smaller datasets, but the largest of the datasets seems to be too big and causing an out of memory exception/bug/crash.
或者，我还尝试运行查询并从 mysql 工作台结果窗口中获取（复制/粘贴）结果。这适用于一些较小的数据集，但最大的数据集似乎太大并导致内存不足异常/错误/崩溃。
Any help on this matter would be greatly appreciated.
对此事的任何帮助将不胜感激。
回答by eroomydna
You could try executing the query from the your local cli and redirect the output to a local file destination;
您可以尝试从本地 cli 执行查询并将输出重定向到本地文件目标；
mysql -user -pass -e"select cols from table where cols not null" > /tmp/output
回答by dolphy
This is dependent on the SQL client you're using to interact with the database. For example, you could use the mysql command line interface in conjunction with the "tee" operator to output to a local file:
这取决于您用来与数据库交互的 SQL 客户端。例如，您可以将 mysql 命令行界面与“tee”运算符结合使用以输出到本地文件：
http://dev.mysql.com/doc/refman/5.1/en/mysql-commands.html
http://dev.mysql.com/doc/refman/5.1/en/mysql-commands.html
tee [file_name], \T [file_name]
Execute the command above before executing the SQL and the result of the query will be output to the file.
在执行 SQL 之前执行上面的命令，查询结果将输出到文件中。
Specifically for MySQL Workbench, here's an article on Execute Query to Text Output. Although I don't see any documentation, there are indications that there should be also be an "Export" option under Query, though that is almost certainly version dependent.
专门针对 MySQL Workbench，这里有一篇关于Execute Query to Text Output的文章。虽然我没有看到任何文档，但有迹象表明 Query 下也应该有一个“导出”选项，尽管这几乎可以肯定取决于版本。
回答by geekdev
You could try this, if you want to write MySQL query result in a file.
如果您想将 MySQL 查询结果写入文件，您可以试试这个。
This example write the MySQL query result into a csvfile with comma separated format
本例将 MySQL 查询结果csv以逗号分隔格式写入文件
SELECT id,name,email FROM customers
INTO OUTFILE '/tmp/customers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
回答by Himanshu Chauhan
If you are running mysql queries on the command line. Here I suppose you have the list of queries in a text file and you want the output in another text file. Then you can use this. [ test_2 is the database name ]
如果您在命令行上运行 mysql 查询。在这里，我假设您在一个文本文件中有查询列表，并且您希望在另一个文本文件中输出。然后你可以使用这个。[ test_2 是数据库名称]
COMMAND 1
命令 1
mysql -vv -u root -p test_2 < query.txt > /root/results.txt 2>&1
Where -vv is for the verbose output.
其中 -vv 用于详细输出。
If you use the above statement as
如果你使用上面的语句作为
COMMAND 2
命令 2
mysql -vv -u root -p test_2 < query.txt 2>&1 > /root/results.txt
It will redirect STDERR to normal location (i.e on the terminal) and STDOUT to the output file which in my case is results.txt
它将 STDERR 重定向到正常位置（即在终端上）并将 STDOUT 重定向到输出文件，在我的情况下是 results.txt
The first command executes the query.txt until is faces an error and stops there.
第一个命令执行 query.txt 直到遇到错误并停止。
That's how the redirection works. You can try
这就是重定向的工作原理。你可以试试
#ls key.pem asdf > /tmp/output_1 2>&1 /tmp/output_2
Here key.pm file exists and asdf doesn't exists. So when you cat the files you get the following
这里 key.pm 文件存在而 asdf 不存在。因此，当您对文件进行分类时，您会得到以下信息
# cat /tmp/output_1
key.pem
#cat /tmp/output_2
ls: cannot access asdf: No such file or directory
But if you modify the previous statement with this
但是如果你用这个修改前面的语句
ls key.pem asdf > /tmp/output_1 > /tmp/output_2 2>&1
Then you get the both error and output in output_2
然后你在 output_2 中得到错误和输出
cat /tmp/output_2
ls: cannot access asdf: No such file or directory
key.pem
