将 Mysql 查询的结果导出为 Excel？

我的要求是存储查询的整个结果

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

到一个 Excel 文件。

205790 次浏览
小开
最佳答案

实现这一点的典型方法是导出到 CSV，然后将 CSV 加载到 Excel 中。

译者:

  • 对于来自 SELECT查询的、对 Excel 友好的 CSV 文件，请运行以下命令:

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

  • 对于一个 服务器端 Excel 友好的 CSV 文件，使用 mysqldump如下:

    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

  • 对于使用 mysqlsh(MySQL Shell)的 客户端 Excel 友好的 CSV 文件，如下所示:

    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
    • < li > Excel 2016 + :-用一些刺激处理 BOM-less UTF-8 < sub >
使用 utf8mb4. < br/> 不要指定 旧的 < em > 破碎的 ABC1或 utf8mb3编码。 使用 utf8mb4

如上表所示，MySQL 可以生成了对 Excel 友好的 CSV 文件，但是 Excel 总是将 SQL NULL解释为文本，尽管在 Excel 中使用 PowerQuery 或者甚至仅仅使用 查找和替换将其替换为空单元格是很琐碎的。

Excel 和 特别的 CSV 文本标记

Excel 和 UTF-8编码:

令人惊讶的是，直到 Excel 诞生31年(Excel 2016) ，Excel 才在文件 而不需要 BOM中添加了对 UTF-8编码的内置支持，但它仍然默认使用系统默认的非 Unicode 编码(例如 Windows-1252)导入和导出 CSV 文件。

  • 将 CSV 导入 Excel 时，一定要选择 Codepage65001以进行正确的 UTF-8处理，因为出于某种原因，Excel 仍然默认为非基于 Unicode 的代码页。

    • 注意，Excel 中的 打开 CSV 文件不会显示 文本导入向导。(从 Excel 2021开始)你需要将 CSV 文本复制粘贴到 Excel 中，然后使用弹出菜单来使用遗留的(1994年冻结的)向导，或者使用带子上的 Data > From Text/CSV来使用更新的(但不那么灵活的)基于 PowerQuery 的 CSV 导入向导:

      • Excel2007-2013 Excel 2016 +
        enter image description here enter image description here

你的选择:

选择 SELECT INTO OUTFILE mysqldump --tab mysqldump > file.csv mysqlsh MySQL 工作台
服务器端 CSV 没错 没错 没错 没错 坏了
远程(客户端) CSV 假的 假的 假的 没错 坏了
MySQLServer 版本支持 所有版本 所有版本 所有版本 只有5.7及以上 所有版本

备选案文1: 使用 INTO OUTFILE出口方便 Excel 的 CSV:

  • 可以使用 ABC1查询的 INTO OUTFILE子句导出 服务器端 CSV。
    • 因为这是由 MySQL 服务器执行的“正常”SQL，所以不管您使用的是什么 MySQL 客户端工具，都可以正常工作，所以您不需要安装 MySQL 工作台。
    • ... 但是因为这是一个服务器端导出，所以您需要具有写入服务器文件系统的权限，而您可能没有这个权限，在这种情况下，可以考虑使用专门的导出工具，如 mysqldump(见下文)。
  • MySQL 的 OUTFILE子句 有许多 < strong > 可选 子句 必须指定在某种程度上与 Excel 自己的 CSV 阅读器兼容:
      • TERMINATED BY(默认值: '\t'，用于 Excel 使用 ',')
      • [OPTIONALLY] ENCLOSED BY(默认值: ''，应该是 '"' 和的 OPTIONALLY关键字)
      • ESCAPED BY(默认值: '\\'，用于 Excel 使用 '')
      • TERMINATED BY (default: '\n', for Excel use '\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命令行选项 根据 mysqldump的文档将 dump 存储到 CSV 文件中。
  • 遗憾的是，用于远程 MySQL 服务器的 mysqldump的 --tab=选项 没用的: 这是因为 --tab="fileName.csv"只能表示服务器上的路径。
    • 虽然 可以使用 stdout重定向生成本地文件(即 mysqldump --etc > output.csv) ，但是不能使用 --fields-terminated-by和 stdout的其他格式选项，这使得它对于与 Excel 兼容的输出没有用处。因此，如果你是远程和不能 ssh-在那么你将需要使用 MySQL Shell(mysqlsh)代替(见下文)。
  • 请注意，mysqldump不支持 SELECT查询转储数据: 它的 是的支持简单的 WHERE风格的过滤器与 --where=<expr>选项，但这不支持像过滤使用 INNER JOIN(虽然作为一个变通方法，你可以 SELECT到一个新的表，然后运行 mysqldump对新的表。请注意，不能将 TEMPORARY TABLE与 mysqldump SELECT0一起使用)。

在 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)。

  • 上使用 --password=<value>(又名 -p<value>)选项避免 mysqldump命令行，因为这意味着您的密码将以明文形式保存到终端或控制台历史文件 这显然是一个巨大的安全风险中。

    • 因此，如果您在一个交互式命令行会话需要指定密码，那么 mysqldump会在程序运行时立即提示您输入密码，这样它就不会被保存到您的历史文件中。
    • 如果你想在非交互的环境下运行 mysqldump(例如在 web 应用程序、守护进程或其他进程中) ，那么在以不安全的方式处理密码之前，(通常)没有需要担心的历史文件 但你仍应考虑其他方法。

  • 如果您没有指定一个绝对路径，而是使用一个短的(非限定的)文件名，如 INTO OUTFILE 'output.csv'或 INTO OUTFILE './output.csv' theb，它将把输出文件存储到 SHOW VARIABLES LIKE 'datadir';指定的目录。

备选方案3: 使用 MySQL 工作台出口方便 Excel 的 CSV:

不幸的是，你不能(除非你的数据中没有任何双引号) : 到2022年底，几乎所有的 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 Server 本地运行 mysqldump(当然，你可以使用 ssh会话)。
    • MySQL 5.7不支持新的“ MySQL X”协议(mysqlx://user@host/schema) ，但是 mysqlsh支持带有旧式命令行参数的非 X 连接。
  1. 如果还没有安装 MySQL Shell ，请安装它。
  2. 如果启动 MySQL Shell时没有任何命令行参数(例如，因为在 Windows 上使用了开始菜单快捷方式) ，那么使用 \connect命令进行连接。

    • 对于 MySQL 5.7，使用 \connect mysql://username@hostname

    • 对于 MySQL 8.0 + ，有多种连接方式，包括“ MySQL X”协议和“经典”连接。更多信息请查阅文件.

    • 如果您的用户名包含字面 @字符，那么您需要百分比编码(例如，如果您使用 Azure MySQL，那么您的 满了用户名将类似于 username%40servername@servername.mysql.database.azure.com)。

    • 在您提交 \connect命令后，将立即以交互方式提示您输入密码。

  3. 如果可以使用参数启动 mysqlsh，那么可以不使用 \connect命令直接运行 mysqlsh --user="userName" --host="hostName" --port=3306 --schema="dbName"。
  4. 一旦连接，运行 util.exportTable(tableName, outputUri, options)命令带有以下参数:
    • 表名。
      • 不幸的是，似乎没有一种方法可以应用 WHERE过滤器或导出 SELECT查询的结果，(虽然与 mysqldump一样，您总是可以将查询结果保存到一个新的 TABLE，然后导出该表，然后导出 DROP TABLE，当您完成它。请记住，TEMPORARY TABLE不会在这里工作，因为在一个会话中创建的表不能从任何其他会话中看到-而且 mysqlsh将有自己的会话。
    • outputUri: 要在本地保存文件，请使用 file:/// URI。
      • 在 Windows 中，可以使用正斜杠作为目录名分隔符，而不是反斜杠。
    • 为了确保与 Excel 的兼容性，请指定 { dialect: "csv", fieldsEscapedBy: ""}。
      • dialect: "csv"选项为除了一个 OUTFILE参数之外的所有参数设置了与 Excel 兼容的默认值，因此您还必须指定 fieldsEscapedBy: ""，否则 SQL NULL将被呈现为 \N(字面意思) ，而文本值中的双引号和换行符将被反斜杠转义，这是 Excel 不支持的。
小开

好的例子可以是在查询结束后写入它，如果你有连接或关闭:

 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');
小开

使用以下查询:

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

在我的示例中，我需要将 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 _ Cancat (foo SEPARATER0x09)的代码是 在输出文件中。0x09字符是 ASCII TAB。但是这个问题并不是特定于我们将 sql 结果转储到文件的方式。可能跟我的传呼机有关。当你找到这个问题的答案时告诉我。我会更新这篇文章。

小开

这是一个古老的问题，但它仍然是谷歌的首批搜索结果之一。最快的方法是使用 ODBC 查询或 MySQLForExcel 将 MySQL 直接链接到 Excel。后者是在对 OP 的评论中提到的，但我觉得它确实值得自己回答，因为向 CSV 导出并不是实现这一点的最有效方法。

ODBC 查询 -设置起来有点复杂，但是更加灵活。例如，MySQLForExcel 外接程序不允许在查询表达式中使用 WHERE子句。此方法的灵活性还允许您以更复杂的方式使用数据。

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

小开

将 mysql 输出导出到文件的快捷方法是

$mysql < database _ name > —— tee = < file _ path >

然后在我想要的任何地方使用导出的输出(可以在 <file_path>中找到)。

注意，这是避免使用 secure-file-priv选项运行数据库的唯一方法，因为 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
小开

对于 SHOW DATABASES和 SHOW TABLES

INTO OUTFILE对于 SHOW TABLES和 SHOW DATABASES查询不起作用。

在这种情况下，你可以做:

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

提示: -e标志代表 execute

参考文献:

小开

在我的例子中，INTO OUTFILE不起作用，因为 MySQL 用户与登录用户不同。另外，我不能使用内联变量，因为我的查询相当大。

最后我用了这个，这样简单多了。这可能不支持 CSV 或任何一种输出，但是如果您需要实际的输出，则可以使用这种方法。

mysql> tee /tmp/my.out;

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


提交答案