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

有没有一种简单的方法可以从 Linux 命令行运行 MySQL 查询并以CSV格式输出结果?

这是我现在正在做的事情:

mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/        /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ

当有很多列需要用引号引起来时,或者结果中有引号需要转义时,它将变得混乱。

答案

http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

使用此命令,列名将不会导出。

还要注意,/ /var/lib/mysql-files/orders.csv mysql- /var/lib/mysql-files/orders.csv将在运行 MySQL 的服务器上。运行 MySQL 进程的用户必须具有写入所选目录的权限,否则该命令将失败。

如果要从远程服务器(尤其是托管或虚拟化的计算机,例如 Heroku 或 Amazon RDS)将输出写入本地计算机,则此解决方案不适合。

$ mysql your_database --password=foo < my_requests.sql > out.csv

用制表符分隔。像这样通过管道传输以获取真实的 CSV(感谢 @therefromhere):

... .sql | sed 's/\t/,/g' > out.csv

mysql --batch,-B

使用 tab 作为列分隔符打印结果,每一行都换行。使用该选项,mysql 不使用历史文件。批处理模式导致以非表格格式输出格式并转义特殊字符。可以通过使用原始模式来禁用转义。请参阅 --raw 选项的说明。

这将为您提供一个制表符分隔的文件。由于逗号(或包含逗号的字符串)不会被转义,因此将分隔符更改为逗号并非易事。

这是一种相当粗糙的方法。在某个地方找到它,不能功劳

mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv

效果很好。尽管正则表达式再次证明只写。


正则表达式说明:

  • s /// 表示将第一个 // 之间的内容替换为第二个 // 之间的内容
  • 最后的 “g” 是一个修饰符,表示 “所有实例,而不仅仅是第一个”
  • ^(在此上下文中)表示行首
  • $(在这种情况下)表示行尾

因此,将它们放在一起:

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

仅限于 Unix / Cygwin ,将其通过'tr' 传递:

mysql <database> -e "<query here>" | tr '\t' ',' > data.csv

注意:这既不处理嵌入式逗号,也不处理嵌入式制表符。

这节省了我几次。快速且有效!

--batch 使用tab作为列分隔符打印结果,每一行都换行。

--raw 禁用字符转义(\ n,\ t,\ 0 和 \)

例:

mysql -udemo_user -p -h127.0.0.1 --port=3306 \
   --default-character-set=utf8mb4 --database=demo_database \
   --batch --raw < /tmp/demo_sql_query.sql > /tmp/demo_csv_export.tsv

为了完整起见,您可以转换为 csv (但请注意,因为制表符可能位于字段值内 - 例如文本字段)

tr '\t' ',' < file.tsv > file.csv

Paul Tomblin 提供的 OUTFILE 解决方案导致将文件写入 MySQL 服务器本身,因此,只有在您具有FILE访问权,登录访问权或其他从该框中检索文件的方式时,此方法才有效。

如果您没有这种访问权限,并且制表符分隔的输出是 CSV 的合理替代(例如,如果您的最终目标是导入到 Excel),那么 Serbaut 的解决方案(使用mysql --batch和可选的--raw )是要走的路。

MySQL Workbench可以将记录集导出为 CSV,并且似乎可以很好地处理字段中的逗号。 CSV 在 OpenOffice 中可以正常打开。

怎么样:

mysql your_database -p < my_requests.sql | awk '{print $1","$2}' > out.csv

到目前为止,除 MySQL 工作台之外,所有解决方案对于 mysql db 中的至少某些可能内容都是不正确的,甚至很不安全(即安全性问题)。

MYSQL Workbench(和类似的 PHPMyAdmin)提供了一种形式正确的解决方案,但旨在将输出下载到用户的位置。它们对于诸如自动数据导出之类的功能不是那么有用。

无法从mysql -B -e 'SELECT ...'的输出中生成可靠正确的 csv,因为它无法对字段中的回车符和空格进行编码。 mysql 的 '-s' 标志确实进行了反斜杠转义,并可能导致正确的解决方案。但是,使用脚本语言(一种具有不错的内部数据结构而不是 bash 的语言)和已经仔细解决了编码问题的库,要安全得多。

我曾考虑为此编写脚本,但一想到要使用的脚本,就想到以同名名字搜索预先存在的作品。虽然我还没有彻底解决它,但是https://github.com/robmiller/mysql2csv 上的解决方案看起来很有希望。根据您的应用程序,用于指定 SQL 命令的 yaml 方法可能会或可能不会吸引人。我也不为比我的 Ubuntu 12.04 笔记本电脑或 Debian Squeeze 服务器标配的最新版本的 ruby 感到高兴。是的,我知道我可以使用 RVM,但是我不想为了一个简单的目的而维护它。

希望有人会指出一个合适的工具,该工具已经过测试。否则,当我找到或编写一个时,可能会更新它。