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

2021-11-20 00:00:00 csv mysql quotes

是否有一种简单的方法可以从 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] 我在某处找到了它，不能相信任何功劳.

相关文章