我该如何以CSV格式输出MySQL查询结果？

Question

有没有一种简单的方式可以从Linux命令行运行MySQL查询并以CSV格式输出结果？

这是我现在正在做的：

mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/        /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ

当需要将许多列用引号括起来或结果中有需要转义的引号时，情况会变得混乱。

- MCS

---

您可以在查询中使用 REPLACE() 函数来转义引号。 - dsm

看看我在这个stackoverflow上的回答。 - biniam

这个stackoverflow问题的被采纳的答案可能是最好的方式：https://dev59.com/RVDTa4cB1Zd3GeqPKp5i - Samuel Åslund

我在MariaDB bug跟踪器上编写了一个功能请求（https://jira.mariadb.org/browse/MDEV-12879），您可以对其投票。 - Jared Beck

https://dev59.com/aHNA5IYBdhLWcg3wBpHs - Sunil Rajput

Answer 1

来自将MySQL查询结果保存为文本或CSV文件:

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

注意：该语法可能需要重新排序

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

在MySQL的较新版本中，使用此命令将不导出列名。

另外请注意，/var/lib/mysql-files/orders.csv将位于运行MySQL的服务器上。MySQL进程正在运行的用户必须具有写入所选目录的权限，否则该命令将失败。

如果您想要从远程服务器（特别是像Heroku或Amazon RDS等托管或虚拟化机器）将输出写入本地计算机，则此解决方案不适用。

- Paul Tomblin

---

你可能需要以root权限运行。"该文件是在服务器主机上创建的，所以您必须具有FILE权限才能使用此语法。" - https://dev.mysql.com/doc/refman/8.0/en/select-into.html - Curtis Yallop

是的，它既依赖于MySQL权限，也依赖于对本地文件系统某个位置的写入权限。 - cazort

“LINES TERMINATED BY '\n'” 不是必需的，因为它是默认值。通常，如果省略所有附加参数，则会得到一个分隔符为制表符的文件，其中字段不被封闭。 - mojuba

关于文件保存路径，这与secure-priv-file指令有关。如果您不想（或无法）使用root，则可以在my.ini中禁用它并重新启动数据库，或更改该目录的权限。请参阅https://dev59.com/k1wY5IYBdhLWcg3wVGiB。 - Chris

对于RDS，如果您正在使用Aurora mysql，您可以使用SELECT INTO OUTFILE S3 https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.SaveIntoS3.html。 - Carl

Answer 2

mysql your_database --password=foo < my_requests.sql > out.tsv

这将生成一个以制表符分隔的格式。如果您确定逗号不会出现在任何列数据中（制表符也是如此），则可以使用此管道命令获得真正的CSV格式（感谢用户John Carter）。

... .sql | sed 's/\t/,/g' > out.csv

- Stan

---

sed命令中的‘fix’无法补偿选定数据中可能出现的逗号，这将导致输出的列结果偏差。 - Joey T

大家都说在命令行上拼写密码不安全。 - Rodrigo

Answer 3

mysql --batch, -B

使用制表符作为列分隔符并将每一行显示在新的一行上。通过该选项，mysql不会使用历史文件。批处理模式下的结果以非表格形式输出，并转义特殊字符。可以使用原始模式禁用转义；请参见 --raw 选项的说明。

这将给你一个以制表符分隔的文件。由于逗号（或包含逗号的字符串）未被转义，因此更改分隔符为逗号并不直观。

- serbaut

Answer 4

这里有一种相当复杂的方法^[1]：

mysql --user=wibble --password mydatabasename -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv

它的效果相当不错。但是，正则表达式仍然是难以理解的。

---

正则表达式的解释：

• s///表示用第二个//中的内容替换第一个//中的内容
• "g"在结尾处是一个修饰符，表示“所有实例，而不仅仅是第一个”
• ^（在这个上下文中）表示行的开头
• $（在这个上下文中）表示行的结尾

将所有内容放在一起：

s/'/\'/          Replace ' with \'
s/\t/\",\"/g     Replace all \t (tab) with ","
s/^/\"/          at the beginning of the line place a "
s/$/\"/          At the end of the line, place a "
s/\n//g          Replace all \n (newline) with nothing

^{[1] 我在某个地方找到了这个内容，不能获得任何功劳。}

（注：该段内容无关IT技术）

- Tim Harding

---

命令中有一个拼写错误：“--password=wobble” - avernus

@AydinÖzcan --password 在命令执行后提示用户输入密码。wobble 是数据库名称，但我同意这不够清晰，已经进行了编辑以提高清晰度。谢谢。 - Tim Harding

Answer 5

将其通过“tr”传输（仅限Unix/Cygwin）：

mysql <database> -e "<query here>" | tr '\t' ',' > data.csv

注意：这个处理方式既不涉及嵌入逗号，也不涉及嵌入制表符。

- strickli

---

我很惊讶这个解决方案的点赞数如此之少。目前排名第一的解决方案需要很多数据库用户没有的特权（出于好的原因；管理员分发这些权限会存在安全隐患）。你在这里提供的解决方案不需要任何特殊权限，而且可能可以通过在查询本身中进行替换来解决逗号或制表符的缺陷问题。 - cazort

同意这里的评论，这正是避免 MySQL 权限地狱所需的。 - gordon_freeman

Answer 6

这个对我帮助很大。它速度快且有效！

--batch 使用tab作为列分隔符打印结果，每行一个新行。

--raw 禁用字符转义（\n、\t、\0 和 \）

示例：

mysql -udemo_user -p -h127.0.0.1 --port=3306 \
   --default-character-set=utf8mb4 --database=demo_database \
   --batch --raw < /tmp/demo_sql_query.sql > /tmp/demo_csv_export.tsv

为了完整性，您可以转换为CSV格式（但要小心，因为制表符可能在字段值中 - 例如文本字段）

tr'\t','<'file.tsv'>'file.csv

- hrvoj3e

---

这是对一个完全不同问题的回答。 - undefined

Answer 7

OUTFILE解决方案由Paul Tomblin提供会在MySQL服务器上写入文件，因此只有在您具有FILE访问权限以及登录访问权限或其他检索该框中文件的手段时才有效。

如果您没有这样的访问权限，并且制表符分隔的输出是CSV的合理替代品（例如，如果您的最终目标是导入Excel），那么serbaut的解决方案（使用mysql --batch并可选地使用--raw）是正确的方法。

- Leland Woodbury

Answer 8

MySQL Workbench 可以导出记录集到 CSV 文件，并且它似乎可以很好地处理字段中的逗号。CSV 文件可以在 OpenOffice Calc 中正常打开。

- David Oliver

---

非常感谢你，David。在花费了三个小时让数据中的HTML内容输出换行符后，我使用了MySQL Workbench，在两分钟内就准备好了我的CSV文件。 - mr-euro

我刚发现它也可以保存为XML，这太好了。我希望通过使用XSLT将此XML转换为适合导入目标应用程序的CSV文件来从一个应用程序迁移至另一个应用程序。 - David Oliver

MySQL Workbench是导入和导出功能的最佳选择。 - cijagani

然而，每次工作台限制选择记录最多为1000条，当涉及更多记录时，它的效果就不那么好了。同样的情况也适用于导入，如果我尝试通过工作台将相对较大的CSV文件导入MySQL数据库，则经常会被阻止。 - LancelotHolmes

我刚刚成功地使用mysql workbench导出了超过50万行数据，因此大文件似乎不是问题。在运行查询之前，您只需要确保删除选择限制，并且您还可能需要增加my.ini文件中以下值的大小：max_allowed_packet = 500M，net_read_timeout = 600，net_write_timeout = 600。 - Vincent

MySQL Workbench并不总是一个选项，特别是当远程服务器为了安全目的而阻止外部连接时。 - afilina

Answer 9

迄今为止，除了MySQL Workbench，所有的解决方案都是不正确的，而且对于MySQL数据库中某些可能的内容来说，这些解决方案很可能是不安全的（即存在安全问题）。

MySQL Workbench（以及类似的phpMyAdmin）提供了一个正式正确的解决方案，但它们被设计用于将输出下载到用户的位置。它们对于像自动化数据导出之类的事情并不是那么有用。

从mysql -B -e 'SELECT ...'的输出中可靠地生成正确的CSV内容是不可能的，因为它无法对字段中的换行符和空格进行编码。mysql的'-s'标志确实进行了反斜杠转义，并可能导致正确的解决方案。然而，使用脚本语言（具有良好的内部数据结构，而不是Bash），以及已经仔细解决了编码问题的库会更加安全。

我曾考虑写一个脚本，但一想到它的名字，我就意识到可以搜索同名的预先存在的工作。虽然我还没有彻底研究过它，但mysql2csv看起来很有前途。根据您的应用程序，指定SQL命令的YAML方法可能或可能不会吸引您。我也不喜欢需要比我Ubuntu 12.04（Precise Pangolin）笔记本电脑或Debian 6.0（Squeeze）服务器标准版本更高的Ruby版本的要求。是的，我知道我可以使用RVM，但对于这么简单的目的，我宁愿不维护它。

- mc0e

---

你说得对，在表格中处理复杂字符串时，需要使用一些好的库，而不仅仅是bash。我认为nodejs有更好的解决方案来执行这种操作，就像这个例子一样。 - yeya

你好，很好的回答，我想添加一个链接到下面提出的Python解决方案 https://dev59.com/tHRC5IYBdhLWcg3wROtQ#35123787，它运行良好且非常简单。我尝试编辑您的帖子，但编辑被拒绝了。 - reallynice

Rob Miller的mysql2csv脚本坚持要连接到数据库本身，无论是通过网络还是套接字，不能像Unix风格的管道一样使用。也许这是必需的，但它确实限制了使用。 - chrisinmtown

@chrisinmtown 你想要将什么内容输入其中？也许是mysqldump的输出？正如我所指出的，mysql -B的输出无法修复。 - mc0e

@mc0e 我想将mysqldump的输出作为stdin输入。 - chrisinmtown

这对于将整个表导出为csv可能很有用，但它不允许您获取查询输出，因此您无法充分利用mysql引擎的功能。 - mc0e

Answer 10

这个页面上很多回答都比较薄弱，因为它们不能处理CSV格式中可能出现的一般情况。例如，在字段中嵌入逗号和引号以及其他常见条件。我们需要一个适用于所有有效CSV输入数据的通用解决方案。

以下是Python中简单而强大的解决方案:

#!/usr/bin/env python

import csv
import sys

tab_in = csv.reader(sys.stdin, dialect=csv.excel_tab)
comma_out = csv.writer(sys.stdout, dialect=csv.excel)

for row in tab_in:
    comma_out.writerow(row)

将该文件命名为tab2csv，将其放在您的路径上，并授予执行权限，然后像这样使用：

mysql OTHER_OPTIONS --batch --execute='select * from whatever;' | tab2csv > outfile.csv

Python的CSV处理函数覆盖了所有可能的CSV输入格式情况。

可以通过流式处理方法来改进，使其能够处理超大文件。

- Chris Johnson

---

一种更为可靠的解决方案是使用Python连接到数据库，这样你就可以更轻松地处理更大的数据集（将结果分块、流式传输等）。 - Josh Rumbut

@JoshRumbut，真的很晚了，但我制作了https://dev59.com/tHRC5IYBdhLWcg3wROtQ#41840534来补充你的评论。 - Ben

请参见https://dev59.com/tHRC5IYBdhLWcg3wROtQ#63831142，获取一个具有处理嵌入逗号和双引号字符的输入方言的扩展版本脚本！ - chrisinmtown

它的要点是什么？使用库？内置库？你能否提供文档链接（https://docs.python.org/3/library/csv.html）等？（但***不包括***“编辑：”，“更新：”或类似内容 - 答案应该看起来像今天写的。） - Peter Mortensen

流媒体方法需要什么？您能详细说明一下吗？（但是请不要包含“编辑：”，“更新：”或类似内容 - 答案应该看起来像今天编写的。） - Peter Mortensen