blocks|key|3011839|text|您可以尝试从本地cli执行查询，并将输出重定向到本地文件目标；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3011840|mysql+-user+-pass+-e"select+cols+from+table+where+cols+not+null"+>+/tmp/output|code-block|syntax|javascript|3011841|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You could try executing the query from the your local cli and redirect the output to a local file destination;

<pre><code>mysql -user -pass -e"select cols from table where cols not null" &gt; /tmp/output
</code></pre>

blocks|key|2726110|text|这取决于您用来与数据库交互的SQL客户端。例如，您可以将mysql命令行界面与"tee“运算符结合使用，以输出到本地文件：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2726111|http://dev.mysql.com/doc/refman/5.1/en/mysql-commands.html|offset|length|2726112|tee+[file_name],+\T+[file_name]+|code-block|syntax|javascript|2726113|在执行SQL之前执行上面的命令，查询结果将输出到文件中。|2726114|这里有一篇关于Execute+Query+to+Text+Output的文章，专门针对MySQL工作台。虽然我没有看到任何文档，但有迹象表明Query下也应该有一个"Export“选项，尽管这几乎可以肯定是依赖于版本的。|2726115|entityMap|0|LINK|mutability|MUTABLE|url|1|http://wb.mysql.com/?p=677^0|0|0|1M|0|0|0|0|7|S|1|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@$D|Z|E|10|1|11]]|A|$]]|$1|F|3|G|5|H|7|12|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|13|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|14|8|@]|9|@$D|15|E|16|1|17]]|A|$]]|$1|O|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|C]]|V|$5|R|S|T|A|$U|W]]]]

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:

<a href="http://dev.mysql.com/doc/refman/5.1/en/mysql-commands.html">http://dev.mysql.com/doc/refman/5.1/en/mysql-commands.html</a>

<pre><code>tee [file_name], \T [file_name] 
</code></pre>

Execute the command above before executing the SQL and the result of the query will be output to the file.

Specifically for MySQL Workbench, here's an article on <a href="http://wb.mysql.com/?p=677">Execute Query to Text Output</a>. 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.

blocks|key|4799145|text|如果您想在一个文件中编写MySQL查询结果，您可以尝试这样做。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4799146|此示例将MySQL查询结果写入逗号分隔格式的csv文件|offset|length|style|CODE|4799147|SELECT+id,name,email+FROM+customers
INTO+OUTFILE+'/tmp/customers.csv'
FIELDS+TERMINATED+BY+','
ENCLOSED+BY+'"'
LINES+TERMINATED+BY+'\n'|code-block|syntax|javascript|4799148|entityMap^0|0|M|3|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You could try this, if you want to write MySQL query result in a file.

This example write the MySQL query result into a <code>csv</code> file with comma separated format

<pre><code>SELECT id,name,email FROM customers
INTO OUTFILE '/tmp/customers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
</code></pre>

blocks|key|3012032|text|如果您在命令行上运行mysql查询。在这里，我假设您有一个文本文件中的查询列表，并且希望输出到另一个文本文件中。然后你就可以使用这个了。test_2是数据库名称|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3012033|命令1|3012034|mysql+-vv+-u+root+-p+test_2++<+query.txt+>++/root/results.txt+2>&1|code-block|syntax|javascript|3012035|其中-vv表示详细输出。|3012036|如果您将上述语句用作|3012037|命令2|3012038|mysql+-vv+-u+root+-p+test_2++<+query.txt++2>&1+>++/root/results.txt|3012039|它会将标准错误重定向到正常位置(即在终端上)，并将标准错误重定向到输出文件，在我的例子中是results.txt|3012040|第一个命令执行query.txt，直到遇到错误并停止。|3012041|这就是重定向的工作方式。你可以试试|3012042|#ls+key.pem+asdf+>+/tmp/output_1+2>&1+/tmp/output_2|3012043|这里key.pm文件存在，而asdf不存在。因此，当您猫文件时，您会得到以下内容|3012044|#+cat+/tmp/output_1
key.pem
#cat+/tmp/output_2
ls:+cannot+access+asdf:+No+such+file+or+directory|3012045|但是如果您用下面的代码修改前面的语句|3012046|ls+key.pem+asdf+>+/tmp/output_1+>+/tmp/output_2+2>&1|3012047|然后在output_2中得到错误和输出|3012048|cat+/tmp/output_2

ls:+cannot+access+asdf:+No+such+file+or+directory
key.pem|3012049|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1D|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1E|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1F|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1G|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1H|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|1I|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|1J|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1K|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1L|8|@]|9|@]|A|$]]|$1|W|3|X|5|F|7|1M|8|@]|9|@]|A|$G|H]]|$1|Y|3|Z|5|6|7|1N|8|@]|9|@]|A|$]]|$1|10|3|11|5|F|7|1O|8|@]|9|@]|A|$G|H]]|$1|12|3|13|5|6|7|1P|8|@]|9|@]|A|$]]|$1|14|3|15|5|F|7|1Q|8|@]|9|@]|A|$G|H]]|$1|16|3|17|5|6|7|1R|8|@]|9|@]|A|$]]|$1|18|3|19|5|F|7|1S|8|@]|9|@]|A|$G|H]]|$1|1A|3|-4|5|6|7|1T|8|@]|9|@]|A|$]]]|1B|$]]

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 ]

COMMAND 1

<pre><code>mysql -vv -u root -p test_2 &lt; query.txt &gt; /root/results.txt 2&gt;&amp;1
</code></pre>

Where -vv is for the verbose output.

If you use the above statement as

COMMAND 2

<pre><code>mysql -vv -u root -p test_2 &lt; query.txt 2&gt;&amp;1 &gt; /root/results.txt
</code></pre>

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

The first command executes the query.txt until is faces an error and stops there.

That's how the redirection works. You can try

<pre><code>#ls key.pem asdf &gt; /tmp/output_1 2&gt;&amp;1 /tmp/output_2
</code></pre>

Here key.pm file exists and asdf doesn't exists. So when you cat the files you get the following

<pre><code># cat /tmp/output_1
key.pem
#cat /tmp/output_2
ls: cannot access asdf: No such file or directory
</code></pre>

But if you modify the previous statement with this

<pre><code>ls key.pem asdf &gt; /tmp/output_1 &gt; /tmp/output_2 2&gt;&amp;1
</code></pre>

Then you get the both error and output in output_2

<pre><code>cat /tmp/output_2

ls: cannot access asdf: No such file or directory
key.pem
</code></pre>

blocks|key|4799215|text|mysql+-v+-u+-c根-p+<+/media/sf_Share/Solution2.sql+2>&1+>+/media/sf_Share/results.txt这对我很有效。因为我希望脚本中的注释也能反映在报告中，所以我添加了一个标志-c|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4799216|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

mysql -v -u -c root -p &lt; /media/sf_Share/Solution2.sql 2&gt;&amp;1 &gt; /media/sf_Share/results.txt
This worked for me. Since I wanted the comments in my script also to be reflected in the report I added a flag -c

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).

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.

Any help on this matter would be greatly appreciated.

write results of sql query to a file in mysql

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我正在尝试使用mysql将查询结果写入文件。我在一些地方看到了关于outfile结构的一些信息，但似乎只将文件写入运行MySQL的机器(在本例中是一台远程机器，即数据库不在我的本地机器上)。或者，我还尝试运行查询并从mysql工作台结果窗口中抓取(复制/粘贴)结果。这对一些较小的数据集有效，但最大的数据集似乎太大，并导致内存溢出异常/错误/崩溃。在这件事上的任何帮助都将非常感谢。

问将sql查询的结果写入mysql中的文件
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将sql查询的结果写入mysql中的文件EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将sql查询的结果写入mysql中的文件
EN