将Mysql查询结果导出到Excel?
将Mysql查询结果导出到Excel?
我的需求是将查询的全部结果存储到Excel文件中。
SELECT * FROM document WHERE documentid IN (SELECT * FROM TaskResult WHERE taskResult = 2429)
一个好的例子是,在查询结束后,如果你有连接或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');
实现此功能的典型方法是将数据导出为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 (制表符) |
, |
记录分隔符 | \r\n |
\n |
\r\n |
非引用文本值中的逗号 | (导致数据表损坏结果) | 不转义 | 如果值包含逗号,则始终将其括在引号之内 |
UTF-8支持 |
|
使用utf8mb4 ,不要指定旧的损坏的utf8 或utf8mb3 编码 |
使用utf8mb4 |
根据上表,MySQL可以生成适用于Excel的CSV文件,唯一的例外是SQL NULL
始终会被Excel解释为文本字面值,尽管使用PowerQuery或仅在Excel中使用查找和替换命令轻松将其替换为空单元格。
Excel和特殊CSV文本标记
Excel和UTF-8编码:
令人惊讶的是,Excel需要31年的时间(Excel2016)才向文件添加了对UTF-8编码的内置支持,而且它仍然默认使用您的系统默认的非Unicode编码(例如Windows-1252
)导入和导出CSV文件。
- 导入CSV到Excel时,请确保选择Codepage
65001
以正确处理UTF-8,因为Excel仍然默认为基于非Unicode的代码页,原因不详。-
请注意,在Excel中打开CSV文件不会显示文本导入向导。(截至Excel2021)您需要将CSV文本复制并粘贴到Excel中,然后使用弹出菜单来使用旧版(1994年停滞不前的)向导,或者使用功能区上的Data > From Text/CSV来使用新版(但我认为不太灵活)PowerQuery-based CSV导入向导:
-
Excel 2007-2013 Excel 2016+
-
-
您的选项:
选项 | SELECT INTO OUTFILE |
mysqldump --tab |
mysqldump > file.csv |
mysqlsh |
MySQL Workbench |
---|---|---|---|---|---|
服务器端CSV | True | True | True | True | 损坏 |
远程(客户端)CSV | False | False | False | True | 损坏 |
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
文件名(例如上述的'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
的文档。 - 不幸的是,
mysqldump
的--tab=
选项不适用于远程MySQL服务器:这是因为--tab="fileName.csv"
只能表示服务器上的路径。- 虽然可以使用stdout重定向生成本地文件(例如
mysqldump --etc > output.csv
),但不能使用--fields-terminated-by
和其他格式选项与stdout,使其对于与Excel兼容的输出无用。因此,如果您是远程用户并且无法通过ssh连接,则需要使用MySQL Shell(mysqlsh
)(见下文)。
- 虽然可以使用stdout重定向生成本地文件(例如
- 请注意,
mysqldump
不支持用于转储数据的SELECT
查询:它支持使用--where=
选项的简单WHERE
样式过滤器,但这不支持使用INNER JOIN
进行过滤(尽管作为一种解决方法,您可以将其SELECT
到新表中,然后在该新表上运行mysqldump
。请注意,您不能在mysqldump
中使用TEMPORARY TABLE
,因为临时表是连接作用域的)。
在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=
(也称为-p
)选项,因为它意味着您的密码将明文保存在您的终端或控制台历史文件中,这是一个明显的巨大安全风险。
如果您正在交互式的命令行会话中需要指定密码,则在程序运行时mysqldump
会立即提示您输入密码,因此它不会保存到您的历史文件中。
如果您想要在非交互式上下文中运行mysqldump
(例如从Web应用程序、守护程序或其他进程内部),则通常没有历史文件需要担心,但是在采用不安全的方式处理密码之前,您仍应考虑其他方法。
如果您没有指定绝对路径,而是使用短(未经确认的)文件名,如INTO OUTFILE 'output.csv'
或INTO OUTFILE './output.csv'
,那么将把输出文件存储到SHOW VARIABLES LIKE 'datadir';
指定的目录中。
选项3:使用MySQL Workbench导出适用于Excel的CSV文件:
不幸的是,除非您的数据中没有任何双引号,否则您无法使用此选项(截至2022年底)。(bug详情:output files never escape double-quote characters in text)因此,几乎所有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。
-
如果您没有使用任何命令行参数启动MySQL Shell(例如,因为您在Windows上使用“开始菜单”快捷方式),则使用
\connect
命令进行连接。-
对于MySQL 5.7,请使用
\connect mysql://username@hostname
-
@
,那么需要对它们进行百分号编码(例如,如果您正在使用 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中,然后导出该TABLE,然后在完成后删除该TABLE。请记住,TEMPORARY TABLE在这里不起作用,因为一个会话中创建的表对于任何其他会话都不可见 - 而mysqlsh会有自己的会话。outputUri
: 要在本地保存文件,请使用file:///
URI。options
: 为确保与Excel兼容,指定{ dialect: "csv", fieldsEscapedBy: ""}
。dialect: "csv"
选项为所有除一个OUTFILE
参数设定了与Excel兼容的默认值,因此必须还指定fieldsEscapedBy: ""
,否则SQLNULL
将被渲染为\ N
(字面上),而文本值中的双引号和换行符将被反斜杠转义,而Excel不支持。