你如何以 csv 格式输出 MySQL 查询结果（到屏幕，而不是文件）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/707473/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you output MySQL query results in csv format (to the screen, not to a file)?
提问by RyanW
I'm trying to output query results in comma delimited format using the mysql command line tool. My mysql user does not have access to use the "INTO OUTFILE" option referenced in this question:
我正在尝试使用 mysql 命令行工具以逗号分隔格式输出查询结果。我的 mysql 用户无权使用此问题中引用的“INTO OUTFILE”选项：
How to output MySQL query results in CSV format?
I'm also aware of the -H and -X options to format output in html and xml respectively, but is there no way to output csv format directly to the screen?
我也知道 -H 和 -X 选项分别用于在 html 和 xml 中格式化输出，但是没有办法将 csv 格式直接输出到屏幕吗？
I found this method using sed - http://tlug.dnho.net/?q=node/209. But, I'm curious to find a straight mysql solution.
我使用 sed - http://tlug.dnho.net/?q=node/209找到了这种方法。但是，我很想找到一个直接的 mysql 解决方案。
Any ideas?
有任何想法吗？
采纳答案by RyanW
I ended up just taking the tab delimited output and copy pasting it to a spreadsheet and then exporting that to csv. Also realized that I could have used the concat or concat_ws function to do the job and will do that next time.
我最终只是采用制表符分隔的输出并将其复制粘贴到电子表格中，然后将其导出到 csv。还意识到我可以使用 concat 或 concat_ws 函数来完成这项工作，并且下次会这样做。
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws
SELECT CONCAT_WS(',', field1, field2, field3) FROM table;
SELECT CONCAT_WS(',', field1, field2, field3) FROM table;
回答by Bill Karwin
I've been using MySQL for years, and I don't know of any way to use the mysqlcommand-line client alone to produce CSV output, except for the solutions you've already found.
我已经使用 MySQL 多年了mysql，除了您已经找到的解决方案之外，我不知道有什么方法可以单独使用命令行客户端来生成 CSV 输出。
There are a couple of feature requests on file for CSV output support:
CSV 输出支持文件中有几个功能请求：
The only other solution I would suggest is to write a script in Perl or PHP, to fetch data from MySQL and output it in CSV format or any other format you want. This is not a difficult script to write.
我建议的唯一其他解决方案是用 Perl 或 PHP 编写脚本，从 MySQL 获取数据并以 CSV 格式或您想要的任何其他格式输出。这不是一个难写的脚本。
回答by Nevaar
mysql --raw --skip-column-names -u someuser -psomepass -h somehost -b somedatabase -e "select * from somedatabase.sometable;"| awk -F\t '{print ","","}'
-F\t tells awk to use a tab as the delimeter on its input, and we print out the desired columns with commas as delimeters using the "," bit. Replace "," with ":" or "|" or whatever to suit your needs.
If you need to quote an ouput field, lets say $1, your awk print would look like this:
如果您需要引用一个输出字段，例如 $1，您的 awk 打印将如下所示：
awk -F\t '{print "\"""\","","}'
回答by Alekc
If you have access to mysqldump you can use something like this
如果你有权访问 mysqldump 你可以使用这样的东西
mysqldump -u [username] -p -t -T/path/to/directory [database] --fields-enclosed-by=\; --fields-terminated-by=,
回答by Dom Storey
Pipe the answer to tr, like this:
将答案传送到tr，如下所示：
mysql <blah blah> -B | tr '\t' ','
回答by John
If you have MySQL Workbench installed, you can run a query then click "export" above the results. It will give you the option of saving as .CSV.
如果您安装了 MySQL Workbench，则可以运行查询，然后单击结果上方的“导出”。它将为您提供另存为 .CSV 的选项。
回答by Artur Siara
following the Change output format for MySQL command line results to CSV
mysql --skip-column-names --batch -e 'select * from dump3' t | awk -F'\t' '{ sep=""; for(i = 1; i <= NF; i++) { gsub(/\t/,"\t",$i); gsub(/\n/,"\n",$i); gsub(/\\/,"\",$i); gsub(/"/,"\"\"",$i); printf sep"\""$i"\""; sep=","; if(i==NF){printf"\n"}}}'
回答by staticsan
The CLI can output in tab-delimited format, which is nearly good enough (I speak from experience). Use the -B option. The biggest problem with tab-delimited is that I couldn't make Excel import that format. However, OpenOffice does.
CLI 可以以制表符分隔的格式输出，这几乎足够好（我是根据经验说的）。使用 -B 选项。制表符分隔的最大问题是我无法让 Excel 导入该格式。但是，OpenOffice 确实如此。
回答by Paul Ericson
If you are using mysql interactively, you can set your pager to use sed like this:
如果您以交互方式使用 mysql，您可以将您的寻呼机设置为使用 sed，如下所示：
$ mysql -u <user> p<passwpd>
mysql> pager sed 's/,/\n/g'
PAGER set to 'sed 's/,/\n/g''
mysql> SELECT blah FROM blah WHERE blah = blah
.
.
.
"blah":"blah"
"blah":"blah"
"blah":"blah"
If you don't use see as the pager the output is like this:
如果您不使用 see 作为寻呼机，则输出如下所示：
"blah":"blah","blah":"blah","blah":"blah"
