问题描述
是否有一种简单的方法可以从 Linux 命令行运行 MySQL 查询并将结果输出到 CSV 格式?
Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format?
这是我现在正在做的事情:
Here's what I'm doing now:
mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/ /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ
如果有很多列需要用引号括起来，或者结果中有引号需要转义，就会变得很乱.
It gets messy when there are a lot of columns that need to be surrounded by quotes, or if there are quotes in the results that need to be escaped.
推荐答案
这是一个相当粗糙的方法[1]:
Here's a fairly gnarly way of doing it[1]:
mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;";|sed "s/'/'/;s/\t/","/g;s/^/"/;s/$/"/;s/\n//g";>Vehicle_categories.csv
效果很好.不过，正则表达式再次证明是只写的.
It works pretty well. Once again, though, a regular expression proves write-only.
正则表达式解释:
- s///表示将第一个//之间的内容替换为第二个//之间的内容//
- g"最后是一个修饰符，意思是所有实例，而不仅仅是第一个"
- ^(在此上下文中)表示行的开头
- $(在此上下文中)表示行结束
所以，把它们放在一起:
So, putting it all together:
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] 我在某处找到了它，不能相信任何功劳.
这篇关于如何以CSV格式输出MySQL查询结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！