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

Question

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

mysqlexcel

186

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

即使只能将数据存储在.txt文件中，也可以。

- Raja G

如果您正在使用phpmyadmin，那么有一个选项可以将其导出为csv或excel等格式。 - Krish R

@KrishR，我在哪里可以了解？我对工作台还很陌生。 - Raja G

1

你使用的操作系统版本是什么？Linux 还是 Windows？ - Krish R

2

在浏览器中进入PHPmyadmin并选择数据库
在phpMyAdmin的SQL选项卡中执行您的SQL查询或选择表格。
执行/选择表格后，向下滚动页面并查找“查询结果操作”
单击“导出”链接，会出现一个页面以导出所有结果，您可以选择所需格式。

- Krish R

您可以将表格数据格式化为文本表格（Text table），具体方法请参考此链接：http://stackoverflow.com/a/34084279/1045444。 - Somnath Muluk

2个回答

14

您可以编写以下代码来实现此任务：

SELECT ... FROM ... WHERE ... 
INTO OUTFILE 'textfile.csv'
FIELDS TERMINATED BY '|'

它将结果导出到CSV文件，然后将其导出到Excel表格中。

- Charles Stevens

9

错误代码：1045 访问被拒绝。 - user2568374

2

@user2568374，你的MySQL用户没有在指定的文件夹内写入权限。你可以直接从命令行运行以下命令：mysql -u USER -pPASSWORD -e "select ... from database.table where ..." > desired/file.txt - mandza

1

无法创建/写入文件，权限被拒绝!!!（我是root）。更新：我知道如何修复错误，我们需要给文件夹授予权限1777，例如：chmod 1777 /var/lib/mysql-files/。 - Dylan B

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Amarnath Balasubramanian · Accepted Answer

来自将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 application which accepts data in CSV format.

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 /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.

语法

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

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

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

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';指定的目录中。