如何将mysql查询输出保存到excel或.txt文件？ [英] how to save mysql query output to excel or .txt file?

问题描述

我有一个MySQL数据库。

I have a MySQL database.

如何将MySQL查询的输出保存到MS Excel表？

How do you save output of a MySQL query to a MS Excel sheet?

即使它只能将数据存储在一个 .txt 文件中，这样就可以了。

Even its only possible to store the data in a .txt file ,it will be okay.

推荐答案

从将MySQL查询结果保存到文本或CSV文件中：

MySQL提供了一个简单的写入机制在服务器上的文本文件中选择
语句的结果。使用
的扩展选项INTO OUTFILE命名，可以创建一个逗号
分离值（CSV），可以导入到电子表格
应用程序，如OpenOffice或Excel或任何其他应用程序
接受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.

给出一个查询，如

SELECT order_id,product_name,qty FROM orders

它返回三列数据，结果可以放在
文件/tmo/orders.txt中，使用查询：

which returns three columns of data, the results can be placed into the file /tmo/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"

请记住，输出文件不能存在，并且
用户MySQL正在运行，具有对目录MySQL
正试图将文件写入。

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.

语法

   SELECT Your_Column_Name
    FROM Your_Table_Name
    INTO OUTFILE 'Filename.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'

或者你也可以尝试这个

您可以尝试从本地客户端执行查询，
将输出重定向到本地文件目标位置;

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

这篇关于如何将mysql查询输出保存到excel或.txt文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！