我的需求是存储查询的全部结果
SELECT * FROM document
WHERE documentid IN (SELECT * FROM TaskResult WHERE taskResult = 2429)
导出到Excel文件。
通常实现这一目标的方法是先导出为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有自己未充分记录的想法,关于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支持 |
| 使用utf8mb4 。不要指定较旧的破碎的 utf8 或utf8mb3 编码。 | 使用utf8mb4 |
NULL
将始终被Excel解释为文本,不过使用PowerQuery或者只需要在Excel中使用查找和替换将其替换为空单元格即可。
令人惊讶的是,直到Excel 31岁(Excel 2016)时,Excel才为文件添加了内置的UTF-8编码支持,而无需BOM,但它仍然默认使用您系统默认的非Unicode编码(例如Windows-1252
)导入和导出CSV文件。
65001
以正确处理UTF-8,因为Excel仍然默认使用基于非Unicode的代码页,原因不明。
请注意,打开CSV文件并不会显示文本导入向导。(截至2021年),您需要将CSV文本复制并粘贴到Excel中,并使用弹出菜单使用传统(“冻结在1994年”)向导,或者使用功能区上的Data > From Text/CSV来使用更新(但灵活性较差)的PowerQuery基础CSV导入向导:
选项 | SELECT INTO OUTFILE | mysqldump --tab | mysqldump > file.csv | mysqlsh | MySQL Workbench |
---|---|---|---|---|---|
服务器端 CSV | 是 | 是 | 是 | 是 | 有问题 |
远程(客户端)CSV | 否 | 否 | 否 | 是 | 有问题 |
MySQL 服务器版本支持 | 所有版本 | 所有版本 | 所有版本 | 仅限 5.7 及更高版本 | 所有版本 |
INTO OUTFILE
导出 Excel 友好的 CSV:INTO OUTFILE
子句执行服务器端CSV导出。
mysqldump
(请参见下文)。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(默认情况下)将其解释为文本(字符串)而不是数字)。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';
mysqldump
导出Excel友好的CSV:--tab
命令行选项将转储存储到CSV文件中,可参考mysqldump
的文档。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
因为临时表是连接范围的)。--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';
指定的目录中。
不幸的是,如果您的数据中有任何双引号,那么您无法这样做:截至 2022 年底,MySQL Workbench 存在一个关于其自身 CSV 导出功能的已知问题:输出文件永远不会转义文本中的双引号字符,因此几乎所有处理 CSV 文件的软件都会报告格式不正确的 CSV 文件或将数据导入错误的列 - 这使得它完全不适用于 Excel。
mysqlsh
)导出适用于 Excel 的 CSV 文件:mysqldump
(但您当然可以使用ssh
会话)。
mysqlx://user@host/schema
)不受MySQL 5.7支持,但mysqlsh
支持旧式命令行参数的非X连接。\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。
file:///C:/Users/Me/Desktop/export.csv
。options
:为确保与Excel兼容,请指定{ dialect: "csv", fieldsEscapedBy: ""}
。
dialect: "csv"
选项为除一个OUTFILE
参数之外的所有参数设置了与Excel兼容的默认值,因此您还必须指定fieldsEscapedBy: ""
,否则SQL NULL
将被呈现为\N
(字面上),而文本值内部的双引号和换行符将被反斜杠转义,Excel不支持。一个好的例子是,如果你在查询结束后有连接或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');
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_concat(foo SEPARATOR 0x09)会在输出文件中写成'\t'。0x09字符是ASCII制表符。但这个问题不特定于我们将SQL结果转储到文件的方式。它可能与我的分页器有关。如果您找到解决此问题的答案,请告诉我。我会更新这篇文章。
mysql -h hn -u un -p databasename < sql_script.sql > sql_output.tab
也可以工作。 - SafwanINTO OUTFILE
不起作用,因为 MySQL 用户与已登录的用户不同。此外,由于我的查询相当复杂,我无法使用内联变体。最终我使用了这个方法,它更加容易。这可能不支持 CSV 或其他输出格式,但如果您需要原样输出,这将起作用。
mysql> tee /tmp/my.out;
来源:https://alvinalexander.com/mysql/how-save-output-mysql-query-file/
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
标志代表执行
参考资料:
我通常使用的快速且简单的方法将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
我只有客户端访问权限,但无法运行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
这是一个老问题,但它仍然是谷歌上的前几个搜索结果之一。最快的方法是使用ODBC查询或MySQL For Excel直接将MySQL链接到Excel。后者在OP的评论中提到过,但我认为它真的应该有自己的答案,因为导出CSV不是实现此目的效率最高的方式。
ODBC Queries - 这需要设置得更复杂一些,但它更加灵活。例如,MySQL For Excel插件不允许您在查询表达式中使用WHERE
子句。这种方法的灵活性还允许您以更复杂的方式使用数据。
MySQL For Excel - 如果您不需要对查询执行任何复杂操作,或者需要快速轻松地完成任务,请使用此插件。您可以在数据库中创建视图以解决某些查询限制。
代码风格
等方面的要求? - sp00m