如何将MySQL查询输出保存到excel或.txt文件中?(复制)

This question already has an answer here:

这个问题已经有了答案:

Exporting results of a Mysql query to excel? 5 answers
将Mysql查询的结果导出到excel?5个回答

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 .txt file, it will be okay.

即使只能将数据存储在.txt文件中，也没有问题。

2 个解决方案

#1

132

From Save MySQL query results into a text or CSV file:

将MySQL查询结果保存为文本或CSV文件:

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 applciation which accepts data in CSV format.

MySQL提供了一种简单的机制，可以将select语句的结果写入服务器的文本文件中。使用INTO OUTFILE命名法的扩展选项，可以创建一个逗号分隔值(CSV)，该值可以导入到电子表格应用程序中，例如OpenOffice或Excel，或者任何以CSV格式接受数据的其他应用程序。

Given a query such as

给定一个查询，例如
SELECT order_id,product_name,qty FROM orders
which returns three columns of data, the results can be placed into the file /tmo/orders.txt using the query:

返回三列数据，结果可以放在文件/tmo/orders中。三种使用查询:
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:

在本例中，每个字段将被括在“双引号”中，字段将被逗号分隔，每一行将输出到由换行符分隔的新行(\n)。此命令的输出示例如下:
"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正在运行，因为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 this too

或者你也可以试试这个

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

#2

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表。

#1

132