将MySQL命令行结果的输出格式更改为CSV [英] Change output format for MySQL command line results to CSV
问题描述
我想在命令行上从查询的输出中获取无头的CSV数据到MySQL。我在MySQL服务器的不同机器上运行此查询，因此所有这些Google答案与INTO OUTFILE是不好的。
I want to get headerless CSV data from the output of a query to MySQL on the command line. I'm running this query on a different machine from the MySQL server, so all those Google answers with "INTO OUTFILE" are no good.
所以我运行 mysql -e选择人物，从事物的地方。输出看起来有点像这样的东西：
So I run mysql -e "select people, places from things". That outputs stuff that looks kinda like this:
+--------+-------------+
| people | places |
+--------+-------------+
| Bill | Raleigh, NC |
+--------+-------------+
好吧，这不好。但嘿，看！如果我只是管道到任何，它会变成一个制表符分隔的列表：
Well, that's no good. But hey, look! If I just pipe it to anything, it turns it into a tab-separated list:
people places
Bill Raleigh, NC
这更好 - 至少它是可编程解析的。但我不想要TSV，我想要CSV，我不想要的标题。我可以摆脱头文件 mysql< stuff> | tail -n +2 ，但这是一个麻烦，我想避免，如果MySQL只是有一个标志省略它。我不能用逗号替换所有的标签，因为它不处理内容中的逗号。
That's better- at least it's programmatically parseable. But I don't want TSV, I want CSV, and I don't want that header. I can get rid of the header with mysql <stuff> | tail -n +2, but that's a bother I'd like to avoid if MySQL just has a flag to omit it. And I can't just replace all tabs with commas, because that doesn't handle content with commas in it.
所以，我怎么能让MySQL省略头
So, how can I get MySQL to omit the header and give me data in CSV format?
推荐答案
我结束写作我自己的命令行工具来照顾这一点。它类似于 cut ，除了它知道如何处理引用的字段等。这个工具，配合@ Jimothy的答案，允许我从远程MySQL服务器我没有文件系统访问到本地机器上使用此命令：
I wound up writing my own command-line tool to take care of this. It's similar to cut, except it knows what to do with quoted fields, etc. This tool, paired with @Jimothy's answer, allows me to get a headerless CSV from a remote MySQL server I have no filesystem access to onto my local machine with this command:
$ mysql -N -e "select people, places from things" | csvm -i '\t' -o ','
Bill,"Raleigh, NC"
这篇关于将MySQL命令行结果的输出格式更改为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
