将Mysql查询结果导出到Excel？

Question

将Mysql查询结果导出到Excel？

75

我的需求是存储查询的全部结果

SELECT * FROM document 
WHERE documentid IN (SELECT * FROM TaskResult WHERE taskResult = 2429)

导出到Excel文件。

- Priya

1

嗯...有点含糊不清！有没有技术、编程语言、限制、礼貌、代码风格等方面的要求？ - sp00m

1

您可以使用Toad for MySQL (免费版) 来达到这个目的。 - DocJones

2

@Priya，根据您的使用情况，还可以查看官方的MySQL for Excel。 - Pacerier

2

你可以简单地使用键盘上的 Ctrl 键并点击第一行，然后按住 Shift 和 Ctrl 键并滚动到最后一行并单击它。现在你已经选中了所有行，你可以右键点击复制行，然后到 Excel 中粘贴。你也可以右键点击标题并复制列名称。 - Shady Mohamed Sherif

1

@shadysherif - 也许可以，但最好只是作为评论。这有点假设某人正在使用GUI，例如 - 这并未在问题或标签中指定。但由你决定！ - rinogo

显示剩余4条评论

9个回答

阿里云服务器只需要99元/年，新老用户同享，点击查看详情

8

一个好的例子是，如果你在查询结束后有连接或where语句，可以在其后编写：

 select 'idPago','fecha','lead','idAlumno','idTipoPago','idGpo'
 union all
(select id_control_pagos, fecha, lead, id_alumno, id_concepto_pago, id_Gpo,id_Taller,
id_docente, Pagoimporte, NoFactura, FacturaImporte, Mensualidad_No, FormaPago,
Observaciones from control_pagos
into outfile 'c:\\data.csv' 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n');

- Nickromancer

OP指的是Excel文件而不是CSV文件。 - vishless

这可能是graham.reeds在寻找的东西。 - MattBianco

7

请使用以下查询：

 SELECT * FROM document INTO OUTFILE 'c:/order-1.csv' FIELDS TERMINATED BY ','  
 ENCLOSED BY '"' LINES TERMINATED BY '\n';

- Aditya Dwivedi

5

在我的情况下，我需要将SQL查询结果转储到客户端的文件中。这是最常见的用例，可以从数据库卸载数据。在许多情况下，您无法访问服务器或不想将结果写入服务器。

mysql -h hostname -u username -ppwd -e "mysql simple sql statement that last for less than a line" DATABASE_NAME > outputfile_on_the.client

当你有一个复杂的查询需要多行来完成时，你不能轻易地使用命令行将结果转储到文件中。在这种情况下，你可以将复杂的查询放入文件中，例如longquery_file.sql，然后执行命令。

mysql -h hn -u un -ppwd < longquery_file.sql DBNAME > output.txt

这对我很有用。唯一困难的是制表符；有时我使用group_concat(foo SEPARATOR 0x09)会在输出文件中写成'\t'。0x09字符是ASCII制表符。但这个问题不特定于我们将SQL结果转储到文件的方式。它可能与我的分页器有关。如果您找到解决此问题的答案，请告诉我。我会更新这篇文章。

- Kemin Zhou

1

mysql -h hn -u un -p databasename < sql_script.sql > sql_output.tab 也可以工作。 - Safwan

0

在我的情况下，INTO OUTFILE 不起作用，因为 MySQL 用户与已登录的用户不同。此外，由于我的查询相当复杂，我无法使用内联变体。最终我使用了这个方法，它更加容易。这可能不支持 CSV 或其他输出格式，但如果您需要原样输出，这将起作用。

mysql> tee /tmp/my.out;

来源：https://alvinalexander.com/mysql/how-save-output-mysql-query-file/

- Ganesh Satpute

0

对于`SHOW DATABASES`和`SHOW TABLES`

INTO OUTFILE无法用于SHOW TABLES和SHOW DATABASES查询。

在这种情况下，您可以执行以下操作：

SHOW DATABASES

mysql -u <user> -p <password> -e 'SHOW DATABASES' > <path_to_file>

SHOW TABLES

mysql -u <user> -p <password> -e 'SHOW TABLES FROM <db_name>' > <path_to_file>

提示：-e标志代表执行

参考资料：

- Deepam Gupta

0

我通常使用的快速且简单的方法将mysql输出导出到文件中是：

$ mysql <database_name> --tee=<file_path>

然后在任何需要的地方使用导出的输出（可以在<file_path>中找到）。

请注意，这是您避免使用secure-file-priv选项运行数据库的唯一方法，该选项会阻止使用先前答案中建议的INTO OUTFILE。

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

- OmarOthman

0

我只有客户端访问权限，但无法运行util.exportTable，所以我使用了：

mysql --default-character-set=utf8mb4 -uUSERNAME -pSECRET -P3306 -hHOSTNAME -Ddatabase_name -e "SELECT * FROM table_name;" | sed 's/\t/","/g;s/^/"/;s/$/"/;' | sed 's/""""/""/g;' > output.csv

- h q

-1

这是一个老问题，但它仍然是谷歌上的前几个搜索结果之一。最快的方法是使用ODBC查询或MySQL For Excel直接将MySQL链接到Excel。后者在OP的评论中提到过，但我认为它真的应该有自己的答案，因为导出CSV不是实现此目的效率最高的方式。

ODBC Queries - 这需要设置得更复杂一些，但它更加灵活。例如，MySQL For Excel插件不允许您在查询表达式中使用WHERE子句。这种方法的灵活性还允许您以更复杂的方式使用数据。

MySQL For Excel - 如果您不需要对查询执行任何复杂操作，或者需要快速轻松地完成任务，请使用此插件。您可以在数据库中创建视图以解决某些查询限制。

- Anthony

Excel的链接已损坏。 - cmcodes

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

可以查看英文原文，

- Roland Bouman · Accepted Answer

通常实现这一目标的方法是先导出为CSV文件，然后将CSV文件加载到Excel中。

简短概括：

For a server-side Excel-friendly CSV file from a SELECT query, run this:

SELECT ... FROM someTable WHERE etc
INTO OUTFILE 'someTableExport.csv' CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
LINES TERMINATED BY '\r\n';

For a server-side Excel-friendly CSV file use mysqldump like so:

mysqldump -h serverHostName -u mysqlUserName -p --tab="someTableExport.csv" --fields-optionally-enclosed-by=0x22 --fields-escaped-by='' --fields-terminated-by=0x2C --lines-terminated-by=0x0D0A --databases databaseName --tables table1 table2 table3

For a client-side Excel-friendly CSV file using mysqlsh (MySQL Shell) like so:

mysqlsh --user="mysqlUserName" --host="serverHostName" --port=3306 --schema="databaseName"
# Once connected, run this:
util.exportTable("tableName", "file:///C:/Users/You/Desktop/test.csv", { dialect: "csv", fieldsEscapedBy: ""})

首先，关于Excel的警告：

Excel与MySQL默认的CSV格式差异：

请记住，Excel有自己_{未充分记录}的想法，关于CSV文件应该如何格式化，这与MySQL对CSV文件的想法形成对比；尽管Excel基本符合RFC 4180，但您仍然需要操纵MySQL及其相关工具来生成Excel不会误解的CSV文件：

	Excel	MySQL (默认)	MySQL (配置后)
SQL `NULL`	零长度值	字面量`\N`	字面量`NULL`
文本值不包含逗号、引号或换行符	未封闭	未封闭	封装在`"`中
文本值包含逗号、引号或换行符	封装在`"`中	未封闭	封装在`"`中
非文本值	未封闭	未封闭	未封闭
文本值中的换行符和制表符	字面量	转义为`[\r]\n`	字面量
文本值中的双引号	双倍`""`	转义为`\"`	双倍`""`
字段分隔符	`,`	`\t` (Tab键)	`,`
记录分隔符	`\r\n`	`\n`	`\r\n`
非引用文本值中的逗号	(导致表数据错误)	未转义	如果值包含逗号，将始终被引用
UTF-8支持	Excel 2007-2013: - 需要UTF-8 BOM前缀 Excel 2016+: - 处理省略BOM的UTF-8 _{需要一些推动}	使用`utf8mb4`。不要指定较旧的破碎的`utf8`或`utf8mb3`编码。	使用`utf8mb4`

根据上表，MySQL 可以生成友好于Excel的CSV文件，唯独SQL NULL将始终被Excel解释为文本，不过使用PowerQuery或者只需要在Excel中使用查找和替换将其替换为空单元格即可。

Excel和特殊CSV文本标记

Excel和UTF-8编码：

令人惊讶的是，直到Excel 31岁（Excel 2016）时，Excel才为文件添加了内置的UTF-8编码支持，而无需BOM，但它仍然默认使用您系统默认的非Unicode编码（例如Windows-1252）导入和导出CSV文件。

在将CSV导入Excel时，请务必选择代码页65001以正确处理UTF-8，因为Excel仍然默认使用基于非Unicode的代码页，原因不明。
- 请注意，打开CSV文件并不会显示文本导入向导。（截至2021年），您需要将CSV文本复制并粘贴到Excel中，并使用弹出菜单使用传统（“冻结在1994年”）向导，或者使用功能区上的Data > From Text/CSV来使用更新（但灵活性较差）的PowerQuery基础CSV导入向导：
  - Excel 2007-2013 Excel 2016+

你的选项：

选项	`SELECT INTO OUTFILE`	`mysqldump --tab`	`mysqldump > file.csv`	`mysqlsh`	MySQL Workbench
服务器端 CSV	是	是	是	是	有问题
远程（客户端）CSV	否	否	否	是	有问题
MySQL 服务器版本支持	所有版本	所有版本	所有版本	仅限 5.7 及更高版本	所有版本

选项 1：使用 `INTO OUTFILE` 导出 Excel 友好的 CSV：

您可以使用SELECT查询的INTO OUTFILE子句执行服务器端CSV导出。

由于这是由MySQL服务器执行的“常规”SQL，因此无论您使用什么MySQL客户端工具，都可以使用此方法，因此您不需要安装MySQL Workbench。
但是，由于这是服务器端导出，您需要有写入服务器文件系统的权限，否则考虑使用专业的导出工具，如mysqldump（请参见下文）。

MySQL的OUTFILE子句具有许多可选的子句，必须指定以与Excel自己的CSV读取器兼容:

FIELDS...
- TERMINATED BY（默认：'\t'，对于Excel使用','）
- [OPTIONALLY] ENCLOSED BY（默认：''，应该使用'"' 和 OPTIONALLY关键字）
- ESCAPED BY（默认：'\\'，对于Excel使用''）
LINES...
- TERMINATED BY（默认：'\n'，对于Excel使用'\r\n'）
- STARTING BY（默认：''，对于Excel，您可以省略此项或使用MySQL默认值）。
不要使用ENCLOSED BY（没有前面的OPTIONALLY关键字），因为这将引用所有值，而不考虑类型（即它将引用int值，这将导致Excel（默认情况下）将其解释为文本（字符串）而不是数字）。

请注意，没有选项指示MySQL将SQL NULL输出为空字段，因此Excel将将它们解释为未引用的文本字符串（即"NULL"），因此您需要在导入文件后在Excel中执行查找和替换。如果您的INTO OUTFILE <fileName>文件名（例如上面的'someTableExport.csv'）不是绝对路径，则会保存到数据库的datadir目录中。运行SHOW VARIABLES LIKE 'datadir';以获取该路径。请注意，您可能没有在该目录下读取/写入新文件的权限。

因此，您的查询（SELECT * FROM document WHERE documentid...）应该类似于以下内容：

SELECT
    *
FROM
    document 
WHERE
    documentid IN ( SELECT documentid FROM TaskResult WHERE taskResult = 2429 )
INTO
    OUTFILE 'someTableExport.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', ESCAPED BY ''
    LINES TERMINATED BY '\r\n';

选项2：使用`mysqldump`导出Excel友好的CSV：

要使用--tab命令行选项将转储存储到CSV文件中，可参考mysqldump的文档。
不幸的是，对于远程MySQL服务器，mysqldump的--tab=选项无法使用：这是因为--tab="fileName.csv"只能表示服务器上的路径。
- 虽然您可以使用stdout重定向生成本地文件（例如mysqldump --etc > output.csv），但是您无法使用--fields-terminated-by和其他格式选项与stdout一起使用，因此对于兼容Excel的输出来说毫无用处。因此，如果您处于远程且无法通过ssh进行连接，则需要改用MySQL Shell（mysqlsh）（请参见下文）。
请注意，mysqldump不支持用于转储数据的SELECT查询：它支持带有--where=<expr>选项的简单WHERE-样式过滤器，但是这种方法不支持使用INNER JOIN进行过滤（尽管可以通过在新表中运行SELECT，然后在该新表上运行mysqldump来解决此问题。请注意，您无法使用TEMPORARY TABLE与mysqldump 因为临时表是连接范围的）。

在OP的情况下，由于--where=命令行选项的固有限制，他们需要同时导出两个表（document和TaskResult），并在Excel PowerQuery或类似工具中应用过滤逻辑。执行以下导出操作：

mysqldump -h serverHostName -u mysqlUserName -p --tab="someTableExport.csv" --fields-optionally-enclosed-by=0x22 --fields-escaped-by='' --fields-terminated-by=0x2C --lines-terminated-by=0x0D0A --databases databaseName --tables document TaskResult

上述命令行应该在Windows的cmd.exe，macOS的zsh和Linux上的bash中无需修改即可运行-前提是mysqldump在您的PATH中。
使用十六进制编码的字符意味着避开了在shell和终端中传递双引号和换行符作为文字的麻烦（0x22是"，0x2C是,，而0x0D0A是\r\n）。
避免在mysqldump命令行上使用--password=<value>（也称为-p<value>）选项，因为这将意味着您的密码将以明文形式保存在您的终端或控制台历史记录文件中，这是一个显而易见的巨大安全风险。
- 因此，如果您处于交互式命令行会话中需要指定密码，则mysqldump将在程序运行时立即提示您输入密码，因此不会将密码保存到历史记录文件中。
- 如果您想在非交互式上下文中运行mysqldump（例如从Web应用程序，守护程序或其他进程中），则通常没有历史记录文件需要担心，但在采取不安全的方式处理密码之前，仍应考虑替代方法。
如果您没有指定绝对路径，但使用短（未经验证的）文件名，如INTO OUTFILE 'output.csv'或INTO OUTFILE './output.csv'，那么它将把输出文件存储到由SHOW VARIABLES LIKE 'datadir';指定的目录中。

选项 3：使用 MySQL Workbench 导出适用于 Excel 的 CSV 文件:

不幸的是，如果您的数据中有任何双引号，那么您无法这样做：截至 2022 年底，MySQL Workbench 存在一个关于其自身 CSV 导出功能的已知问题：输出文件永远不会转义文本中的双引号字符，因此几乎所有处理 CSV 文件的软件都会报告格式不正确的 CSV 文件或将数据导入错误的列 - 这使得它完全不适用于 Excel。

选项 4：使用 MySQL Shell（也称为 `mysqlsh`）导出适用于 Excel 的 CSV 文件：

这可能是最简单的选择，但您可能需要安装MySQL Shell，因为它在大多数MySQL安装中不自带。
MySQL Shell支持连接到MySQL Server 5.7及更高版本（但不支持旧版本）。如果您仍在使用MySQL Server 5.6或更早版本（那么您真的应该更新到5.7或更高版本），则必须坚持在MySQL服务器本身上运行mysqldump（但您当然可以使用ssh会话）。
- 新的“MySQL X”协议（mysqlx://user@host/schema）不受MySQL 5.7支持，但mysqlsh支持旧式命令行参数的非X连接。

如果尚未安装，请安装MySQL Shell。
如果您没有使用任何命令行参数启动MySQL Shell（例如，因为您在Windows上使用了“开始”菜单快捷方式），则使用\connect命令进行连接。
- 对于MySQL 5.7，请使用\connect mysql://username@hostname
- 对于MySQL 8.0+，有多种连接方式可供选择，包括“MySQL X”协议和“Classic”连接。请参阅文档以获取更多信息。
- 如果您的用户名包含实际的@字符，则需要对它们进行百分比编码（例如，如果您正在使用Azure MySQL，则您的完整用户名将类似于username%40servername@servername.mysql.database.azure.com）。
- 在提交\connect命令后立即会提示您交互式输入密码。
如果您可以带参数启动mysqlsh，则可以直接运行mysqlsh --user="userName" --host="hostName" --port=3306 --schema="dbName"而不使用\connect命令。
连接后，使用以下参数运行util.exportTable(tableName, outputUri, options)命令：
- tableName：您的表名。
  - 不幸的是，似乎没有办法应用WHERE过滤器或导出SELECT查询的结果（尽管与mysqldump一样，您可以将查询结果保存到新的TABLE中，然后导出该表，然后在完成后DROP TABLE。请记住，这里无法使用TEMPORARY TABLE，因为在一个会话中创建的表对任何其他会话都不可见-而mysqlsh将有自己的会话。
- outputUri：要保存本地文件，请使用file:/// URI。
  - 在Windows上，您可以使用正斜杠作为目录名称分隔符，而不是反斜杠。例如：file:///C:/Users/Me/Desktop/export.csv。
- options：为确保与Excel兼容，请指定{ dialect: "csv", fieldsEscapedBy: ""}。
  - dialect: "csv"选项为除一个OUTFILE参数之外的所有参数设置了与Excel兼容的默认值，因此您还必须指定fieldsEscapedBy: ""，否则SQL NULL将被呈现为\N（字面上），而文本值内部的双引号和换行符将被反斜杠转义，Excel不支持。

将Mysql查询结果导出到Excel？

简短概括：

首先，关于Excel的警告：

Excel与MySQL默认的CSV格式差异：

Excel和特殊CSV文本标记

Excel和UTF-8编码：

你的选项：

选项 1：使用 INTO OUTFILE 导出 Excel 友好的 CSV：

选项2：使用mysqldump导出Excel友好的CSV：

选项 3：使用 MySQL Workbench 导出适用于 Excel 的 CSV 文件:

选项 4：使用 MySQL Shell（也称为 mysqlsh）导出适用于 Excel 的 CSV 文件：

对于SHOW DATABASES和SHOW TABLES

选项 1：使用 `INTO OUTFILE` 导出 Excel 友好的 CSV：

选项2：使用`mysqldump`导出Excel友好的CSV：

选项 4：使用 MySQL Shell（也称为 `mysqlsh`）导出适用于 Excel 的 CSV 文件：

对于`SHOW DATABASES`和`SHOW TABLES`