题 如何以CSV格式输出MySQL查询结果?


有没有一种简单的方法可以从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

当有很多列需​​要用引号括起来,或者结果中有引号需要转义时,它会变得混乱。


934
2017-12-10 15:59


起源


您可以使用 REPLACE() 在您的查询中有引号转义。 - dsm
看看[这个stackoverflow] [1] [1]中的答案: stackoverflow.com/questions/12242772/... - biniam_Ethiopia
您可以 将表格数据格式化为文本表格。 - Somnath Muluk
这个stackoverflow问题的接受答案可能是最好的方法: stackoverflow.com/questions/3760631/mysql-delimiter-question - Samuel Åslund
我在MariaDB错误跟踪器上写了一个功能请求(jira.mariadb.org/browse/MDEV-12879你可以投票。 - Jared Beck


答案:


http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

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

使用此命令将不会导出列名称。

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

如果要从远程服务器(尤其是托管或虚拟机,如Heroku或Amazon RDS)将输出写入本地计算机,则此解决方案不适用。


1428
2017-12-10 16:07



@Tomas如果你有权访问远程文件系统和MySQL,你必须能够在某处写。而不是/ tmp,请尝试/home/yourusername/file.csv - 如果失败,并且结果集不是那么大,您可以从SSH客户端复制输出并粘贴到本地计算机。 - Michael Butler
问题指定MySQL,而不是“符合标准”。 - Paul Tomblin
如何包含标题? - Bogdan Gusiev
@BogdanGusiev,您可以通过在真实查询之前添加“SELECT'order_id','product_name','qty'UNION”来包含标题。首先选择查询返回头,第二个查询返回实数据; UNION将它加入到一起。 - petrkotek
@Michael Butler:嗯,不,实际上。如果您使用的是Amazon RDS,Heroku或任何其他托管解决方案,则您不太可能只能写入其他人的服务器。 - Ken Kinder


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

哪个是标签分隔。像这样管道以获得真正的CSV(感谢@therefromhere):

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

359
2018-04-08 16:53



不仅如此,它还允许从远程数据库主机创建内容,这与写入本地文件系统的其他内容不同。 - tmarthal
它是以制表符分隔的,而不是逗号分隔的。 - Flimm
@Flimm,假设您在字段中没有嵌入的逗号/制表符,您可以通过将结果汇总到其中来转换它 | sed 's/\t/,/g' - John Carter
sed'fix'不会补偿可能出现在任何所选数据中的逗号,并会相应地输出您的列 - Joey T
否定性是有效的......它现在可能对你有用,但是当你的数据包含标签或逗号等时,它可能会在将来咬你。 - John Hunt


mysql --batch,-B

使用制表符作为列分隔符打印结果,每行包含一个   新队。使用此选项,mysql不使用历史记录文件。   批处理模式导致非表格输出格式和转义   特殊字符。使用原始模式可以禁用转义;看到   --raw选项的描述。

这将为您提供一个制表符分隔文件。由于逗号(或包含逗号的字符串)未被转义,因此将分隔符更改为逗号并不简单。


170
2017-09-30 10:51



这是一个首选解决方案:1)在UNIX中常见的制表符分隔值列表2)大多数导入系统本身都支持TSV导入,包括Excel,OpenOffice Spreadsheets等.3)无需转义文本字段的引号字符4 )使命令行导出变得轻而易举 - Joey T
这是最好的解决方案,因为与第一个不同的是,不需要在RDS等服务器上拥有权限 - muayyad alsadi
一个巧妙的技巧:如果您将制表符分隔文件保存为.xls而不是.csv,它将在Excel中打开,无需“文本到数据”转换,也不会出现任何区域设置问题。 - serbaut
@Joey T - 你仍然需要逃脱标签和回车。此外,如果字段的内容看起来像带引号或转义字段,则导入的内容可能与原始内容不同。 - mc0e
谢谢,这将在未来帮助我很多! CLI - 帮助只说: -B, - block:不要使用历史文件。禁用交互行为。 (启用--silent。)  所以当我寻找“普通”导出格式时,我总是忽略这个选项。 - dennis


这是一种相当粗糙的方式。发现它在某个地方,不能拿任何功劳

mysql --user=wibble --password wobble -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

112
2018-03-22 17:31



-e标志正是我想要的!谢谢! - Gaurav Gupta
这个正则表达式非常简单;像这个页面上的很多其他答案一样,它只是清理输出 mysql -B。如果你将正则表达式分成单独的行中的单独语句,那么对于了解正则表达式的人来说,这将是非常简单的。 - Mei
它很棒!重要的是要说已经包含了标题(不需要额外的步骤)。 - lepe
乍一看,这看起来很破碎。内容中的标签和回车将被错误处理。 “S / '/ \'/;”什么都不做,因为shell命令中的双引号会使用反斜杠。许多其他类似的错误与反斜杠丢失。不在db字段中处理反斜杠。 - mc0e
如果你有一个包含制表符,反斜杠的文本字段,这将会中断 ",",以及其他一些事情。正则表达式是 不 解决这个问题的方法 - aidan


在Unix /Cygwin的 只通过'tr'管道:

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

N.B。:它既不处理嵌入式逗号,也不处理嵌入式选项卡。


68
2017-10-11 15:19



也没有嵌入反斜杠或回车 - mc0e


Paul Tomblin给出的OUTFILE解决方案导致文件写在MySQL服务器本身上,所以这只有在你有 文件 访问,以及登录访问或从该框中检索文件的其他方法。

如果您没有此类访问权限,并且制表符分隔的输出是CSV的合理替代(例如,如果您的最终目标是导入到Excel),那么Serbaut的解决方案(使用 mysql --batch 并且可选地 --raw)是要走的路。


33
2018-06-22 13:48



这可能是我的问题(我对生产数据库有只读权限)但我无法得到 --batch 上班。 - Sridhar-Sarnobat


怎么样:

mysql your_database -p < my_requests.sql | awk '{print $1","$2}' > out.csv

30
2017-11-10 18:41



我真的很喜欢这一个。它更干净,我喜欢使用awk。但是,我可能会这样做: mysql -uUser -pPassword your_database < my_requests.sql | awk 'BEGIN{OFS="=";} {print $1,$2}' > out.csv - Josh
纯粹的命令行解决方案,完美运行 - chawkinsuf
对于带空格的字段值,这会失败。 - Barun Sharma