在 MySQL Workbench 中导出超过 1000 条记录的查询结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10288716/
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
Exporting query results in MySQL Workbench beyond 1000 records
提问by think.arthur
I'm trying to save a query result of about 1,000,000 records in MySQL Workbench.
我正在尝试在 MySQL Workbench 中保存大约 1,000,000 条记录的查询结果。
When I run the SELECT, only 1000 records shows up (MySQL Workbench's default limit). I know I can change the limit, or remove the limit, but I don't want 1,000,000 records to be loaded into the result panel (which might crash my computer?), but I do want to save the results to a file.
当我运行 SELECT 时，只显示 1000 条记录（MySQL Workbench 的默认限制）。我知道我可以更改限制或删除限制，但我不希望将 1,000,000 条记录加载到结果面板中（这可能会使我的计算机崩溃？），但我确实想将结果保存到文件中。
Does MySQL Workbench let you save the results of a query directly to a file? Or save the whole result set instead of the 1,000?
MySQL Workbench 是否允许您将查询结果直接保存到文件中？还是保存整个结果集而不是 1,000？
采纳答案by user86614
It is possible to change the query result row limit, or remove the limit entirely.
可以更改查询结果行限制，或完全删除限制。
Go to Edit → Preferences → SQL Editor (tab). If you can't find Query Results, go to SQL Queries(tab)instead.
Locate the Query Resultssection and untick the Limit Rowscheckbox
Click OK.
Re-run your query.
转到编辑 → 首选项 → SQL 编辑器（选项卡）。如果找不到Query Results，请转至SQL Queries(tab)。
找到查询结果部分并取消选中限制行复选框
单击“确定”。
重新运行您的查询。
回答by vbence
LOAD DATA INFILEhas a sibling called SELECT ... INTO OUTFILE. You can use it like:
LOAD DATA INFILE有一个名为SELECT ... INTO OUTFILE的兄弟。你可以像这样使用它：
SELECT * FROM mytable
INTO OUTFILE '/tmp/mytable.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
This method will not use unnecessary resources in the UI.
此方法不会在 UI 中使用不必要的资源。
As @florianpointed out: It will, however, create the output file on the database server, not on the client machine.
正如@florian指出的那样：但是，它将在数据库服务器上创建输出文件，而不是在客户端计算机上。
Also note that security frameworks like SELinuxor AppArmormight prevent MySQL from writing files outside the standard databasesfolder. If you experience permission deniederrors, even though the directory is writable by the mysqluser, it is likely be one of these.
另请注意，SELinux或AppArmor等安全框架可能会阻止 MySQL 在标准数据库文件夹之外写入文件。如果您遇到权限被拒绝错误，即使该目录可由mysql用户写入，它也可能是其中之一。
回答by Tonvin
Just add 'limit $number' append the SQL clause. if you do not add limit,the default returned lines is 1000.
只需添加 'limit $number' 附加 SQL 子句。如果不加限制，默认返回的行数为 1000。
回答by ToolmakerSteve
MySQL WorkBenchhas a "Data Export"command; this is used to export table(s) to your client computer. [A similar command exists in other tools such as phpmyadmin.]
MySQL WorkBench有一个“数据导出”命令；这用于将表导出到您的客户端计算机。[类似的命令存在于其他工具中，例如phpmyadmin.]
We simply need the output of the query to be held in a table:
我们只需要将查询的输出保存在一个表中：
DROP TABLE IF EXISTS temptable;
CREATE TABLE temptable select ID FROM tbltees WHERE Revision=2;
Now use GUI:
现在使用图形界面：
Server / Data Export / ... select
temptable, and desired export options ...
服务器/数据导出/...选择
temptable，以及所需的导出选项...
Cleanup when done:
完成后清理：
DROP TABLE IF EXISTS temptable;
