如何将 MySQL 查询输出保存到 excel 或 .txt 文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21253704/
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
How to save MySQL query output to excel or .txt file?
提问by rɑ?d?ɑ
How do you save output of a MySQL query to a MS Excel sheet?
如何将 MySQL 查询的输出保存到 MS Excel 工作表？
Even if it's only possible to store the data in a .txtfile, it will be okay.
即使只能将数据存储在.txt文件中，也没关系。
回答by Amarnath Balasubramanian
From Save MySQL query results into a text or CSV file:
MySQL provides an easy mechanism for writing the results of a select statement into a text file on the server. Using extended options of the INTO OUTFILE nomenclature, it is possible to create a comma separated value (CSV) which can be imported into a spreadsheet application such as OpenOffice or Excel or any other application which accepts data in CSV format.
Given a query such as
SELECT order_id,product_name,qty FROM orderswhich returns three columns of data, the results can be placed into the file /tmp/orders.txt using the query:
SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.txt'This will create a tab-separated file, each row on its own line. To alter this behavior, it is possible to add modifiers to the query:
SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'In this example, each field will be enclosed in double quotes, the fields will be separated by commas, and each row will be output on a new line separated by a newline (\n). Sample output of this command would look like:
"1","Tech-Recipes sock puppet","14.95" "2","Tech-Recipes chef's hat","18.95"Keep in mind that the output file must not already exist and that the user MySQL is running as has write permissions to the directory MySQL is attempting to write the file to.
MySQL 提供了一种简单的机制来将 select 语句的结果写入服务器上的文本文件。使用 INTO OUTFILE 命名法的扩展选项，可以创建逗号分隔值 (CSV)，可以将其导入到电子表格应用程序（例如 OpenOffice 或 Excel）或任何其他接受 CSV 格式数据的应用程序中。
给定一个查询，例如
SELECT order_id,product_name,qty FROM orders它返回三列数据，可以使用查询将结果放入文件 /tmp/orders.txt 中：
SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.txt'这将创建一个制表符分隔的文件，每一行都占一行。要改变这种行为，可以向查询添加修饰符：
SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'在这个例子中，每个字段都用双引号括起来，字段用逗号分隔，每一行都将输出到由换行符 (\n) 分隔的新行上。此命令的示例输出如下所示：
"1","Tech-Recipes sock puppet","14.95" "2","Tech-Recipes chef's hat","18.95"请记住，输出文件必须不存在，并且用户 MySQL 正在运行，因为它对 MySQL 试图将文件写入到的目录具有写入权限。
Syntax
句法
SELECT Your_Column_Name
FROM Your_Table_Name
INTO OUTFILE 'Filename.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
Or you could try to grab the output via the client:
或者您可以尝试通过客户端获取输出：
You could try executing the query from the your local client and redirect the output to a local file destination:
您可以尝试从本地客户端执行查询并将输出重定向到本地文件目标：
mysql -user -pass -e "select cols from table where cols not null" > /tmp/output
回答by Charles Stevens
You can write following codes to achieve this task:
您可以编写以下代码来完成此任务：
SELECT ... FROM ... WHERE ...
INTO OUTFILE 'textfile.csv'
FIELDS TERMINATED BY '|'
It export the result to CSV and then export it to excel sheet.
它将结果导出到 CSV，然后将其导出到 Excel 表。
