实现此目的的典型方法是导出为 CSV,然后将 CSV 加载到 Excel 中。
TL;DR:
-
For a 服务器端来自 Excel 的 CSV 文件SELECT
查询,运行这个:
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 服务器端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
-
For a 客户端Excel 友好的 CSV 文件使用mysqlsh
(MySQL Shell)像这样:
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 格式:
Remember that Excel has its own underdocumented ideas about how CSV files should be formatted and these stand in-contrast to MySQL's own ideas about CSV files; though Excel is largely compliant with RFC 4180 you still need to prod and poke MySQL and its associated tooling to generate CSV files that Excel won't misinterpret:
|
Excel |
MySQL (default) |
MySQL (when configured) |
SQL NULL
|
Zero-length value |
Literal \N
|
Literal NULL
|
Text values that don't contain commas, quotes, or line-breaks |
Not enclosed |
Not enclosed |
Enclosed in "
|
Text values that contain commas, quotes, or line-breaks |
Enclosed in "
|
Not enclosed |
Enclosed in "
|
Non-text values |
Not enclosed |
Not enclosed |
Not enclosed |
Line-breaks and tabs in text values |
Literal |
Escaped as [\r]\n
|
Literal |
Double-quotes in text values |
Doubled-up ""
|
Escaped as \"
|
Doubled-up ""
|
Field separator |
, |
\t (Tab) |
, |
Record separator |
\r\n |
\n |
\r\n |
Commas inside non-quoted text values |
(Results in broken table data) |
Not escaped |
Will always be quoted if the value contains a comma |
UTF-8 support |
-
Excel 2007-2013:- 需要前导 UTF-8 BOM
-
Excel 2016+: - Handles BOM-less UTF-8 with some prodding
|
Use utf8mb4 . Do not specify the older broken utf8 or utf8mb3 encodings. |
Use utf8mb4
|
根据上表,MySQLcan生成 Excel 友好的 CSV 文件,但 SQL 除外NULL
s 始终会被 Excel 解释为文字文本,尽管使用 PowerQuery 甚至只是简单地使用查找和替换在 Excel 中将其替换为空单元格。
Excel 和specialCSV 文本标记
-
如果您的任何文本值开头为=那么你需要手动捏造这些,因为即使引用 Excel 也会解释下面的文本=作为公式而不是文字文本.
- 还要留意前导文本如sep= too.
Excel 和 UTF-8 编码:
令人惊讶的是,直到 Excel 31 岁(Excel 2016),Excel 才添加了对文件中 UTF-8 编码的内置支持无需 BOM,但它仍然默认使用系统默认的非 Unicode 编码(例如Windows-1252
).
- When importing CSV into Excel, be sure to select Codepage
65001
for correct UTF-8 handling as Excel still defaults to non-Unicode-based codepages for some reason.
-
Note that opening a CSV file in Excel won't display the Text Import Wizard. (As of Excel 2021) you need to copy-and-paste CSV text into Excel and use the popup menu to use the legacy (frozen-in-1994) wizard, or use Data > From Text/CSV on the ribbon to use the newer (but less flexible, imo) PowerQuery-based CSV import wizard:
Excel 2007-2013 |
Excel 2016+ |
![enter image description here](https://i.stack.imgur.com/v8iJa.png) |
![enter image description here](https://i.stack.imgur.com/rf7gx.png) |
您的选择:
Option |
SELECT INTO OUTFILE |
mysqldump --tab |
mysqldump > file.csv |
mysqlsh |
MySQL Workbench |
Server-side CSV |
True |
True |
True |
True |
Broken |
Remote (client-side) CSV |
False |
False |
False |
True |
Broken |
MySQL Server version support |
All versions |
All versions |
All versions |
Only 5.7 and later |
All versions |
选项 1:使用导出 Excel 友好的 CSVINTO OUTFILE
:
- You can do a server-side CSV export by using the INTO OUTFILE clause of a SELECT query.
- 因为这是由 MySQL 服务器执行的“正常”SQL,所以无论您使用什么 MySQL 客户端工具,它都可以工作,因此您不需要安装 MySQL Workbench。
- ...但是因为这是服务器端导出,您需要拥有写入服务器文件系统的权限,而您可能没有权限,在这种情况下,请考虑使用专业导出工具,例如
mysqldump
(见下文)。
- MySQL's
OUTFILE
clause has a number of optional subclauses that must be specified for some-level of compatibility with Excel's own CSV reader:
-
FIELDS...
-
TERMINATED BY
(默认:'\t'
, 用于 Excel','
)
-
[OPTIONALLY] ENCLOSED BY
(默认:''
, 应该'"'
with the OPTIONALLY
关键词)
-
ESCAPED BY
(默认:'\\'
, 用于 Excel''
)
-
LINES...
-
TERMINATED BY
(默认:'\n'
, 用于 Excel'\r\n'
)
-
STARTING BY
(默认:''
,对于 Excel,您可以省略此项或使用 MySQL 默认值)。
-
Do not use
ENCLOSED BY
(没有前面的OPTIONALLY
关键字),因为这将引用所有值,无论类型如何(即它将引用int
值,这将导致 Excel(默认情况下)将它们解释为文本(字符串)而不是数字)。
- 注意,没有选项指示MySQL输出SQL
NULL
s 作为空字段,因此 Excel 会将它们解释为不带引号的文本字符串(即"NULL"
),因此您需要在导入文件后在 Excel 中执行查找并替换。
- If your
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:使用导出 Excel 友好的 CSVmysqldump
:
- 使用以下命令将转储存储到 CSV 文件中
--tab
命令行选项,as per mysqldump的文档.
-
Unfortunately
mysqldump
's --tab=
option won't work for remote MySQL servers: this is because --tab="fileName.csv"
can only represent a path on the server.
- 当你can use
stdout
重定向以生成本地文件(即mysqldump --etc > output.csv
)你不能使用--fields-terminated-by
和其他格式选项stdout
,使其对于 Excel 兼容的输出毫无用处。因此,如果您身处偏远地区且无法ssh
-那么你将需要使用MySQL 外壳 (mysqlsh
)代替(见下文)。
- 注意
mysqldump
不支持SELECT
转储数据的查询:itdoes支持简单WHERE
式过滤器--where=<expr>
选项,但这不支持使用过滤之类的东西INNER JOIN
(尽管作为解决方法,你可以SELECT
进入一个新表,然后运行mysqldump
在那张新桌子上。请注意,您不能使用TEMPORARY TABLE
with mysqldump
因为临时表是连接范围的).
在OP的情况下,由于固有的局限性--where=
命令行选项有效,他们会想要导出两个表(document
and 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
, and bash
在 Linux 上 - 提供mysqldump
在你的PATH
.
-
使用十六进制编码的字符意味着避免了弄清楚如何在 shell 和终端中将双引号和换行符作为文字传递的麻烦(0x22
is "
, 0x2C
is ,
, and 0x0D0A
is \r\n
).
-
Avoid使用--password=<value>
(aka -p<value>
) 选项
这mysqldump
命令行,因为这意味着您的密码将以明文形式保存到您的终端或控制台历史文件中,这是一个明显的巨大安全风险.
- 因此,如果您处于交互式命令行会话中需要指定密码,那么
mysqldump
程序运行时会立即提示您输入密码,因此不会将其保存到您的历史文件中。
- 如果你想跑步
mysqldump
在非交互式上下文中(例如,在 Web 应用程序、守护进程或其他进程中),那么(通常)