如何以csv格式输出MySQL查询结果（到屏幕，而不是文件）？

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?

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

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?

我也知道分别在html和xml中格式化输出的-H和-X选项，但是没有办法直接将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?

有任何想法吗？

9 个解决方案

#1

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-ws

SELECT CONCAT_WS(',', field1, field2, field3) FROM table;

SELECT CONCAT_WS（'，'，field1，field2，field3）FROM表;

#2

mysql --raw --skip-column-names -u someuser -psomepass -h somehost -b somedatabase -e "select * from somedatabase.sometable;"| awk -F\\t '{print $1","$2","$3}'

-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 "\""$1"\","$2","$3}'

#3

I've been using MySQL for years, and I don't know of any way to use the mysql command-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输出支持的功能请求：

http://bugs.mysql.com/bug.php?id=5707
http://bugs.mysql.com/bug.php?id=5707
http://bugs.mysql.com/bug.php?id=25243
http://bugs.mysql.com/bug.php?id=25243

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格式或任何其他格式输出。这不是一个难以编写的脚本。

#4

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的选项。

#5

Pipe the answer to tr, like this:

将答案传递给tr，如下所示：

mysql <blah blah> -B | tr '\t' ','

#6

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=,

#7

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确实如此。

#8

following the Change output format for MySQL command line results to CSV

遵循将MySQL命令行结果的更改输出格式设置为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"}}}'

#9

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"

#1