如何将 MySQL 查询输出保存到 excel 或 .txt 文件?

2021-11-20 00:00:00 excel mysql

如何将 MySQL 查询的输出保存到 MS Excel 工作表中?

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

即使只能将数据存储在 .txt 文件中，也没关系.

Even if it's 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 application which accepts data in CSV format.

给定一个查询，例如

SELECT order_id,product_name,qty FROM orders

其中返回三列数据，结果可以放入使用查询的文件/tmp/orders.txt:

which 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'

在这个例子中，每个字段都用双引号括起来，字段将用逗号分隔，每一行将输出在一个由换行符 (\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'

或者您可以尝试通过客户端获取输出:

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

提示:如果您没有指定绝对路径而是使用诸如 INTO OUTFILE 'output.csv' 或 INTO OUTFILE './output.csv' 之类的内容，它将输出文件存储到由 show variables like 'datadir'; 指定的目录.

Hint: If you don't specify an absoulte path but use something like INTO OUTFILE 'output.csv' or INTO OUTFILE './output.csv', it will store the output file to the directory specified by show variables like 'datadir';.

相关文章