BASH MySQL查询,以逗号分隔的文件 [英] BASH MySQL Query to Comma Separated File

查看:156
本文介绍了BASH MySQL查询,以逗号分隔的文件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有以下bash脚本:

I have the following bash script:

#!/bin/sh

MYSQLHOST="mysql.remote-host.com"
MYSQLDB="mysqldb"
MYSQLTABLE="table"
MYSQLUSER="user"
MYSQLPASS="pass"
MYSQLDUMP="Report/report.csv"
LOG="Report/report.log"

MYSQLOPTS="--user=${MYSQLUSER} --password=${MYSQLPASS} --host=${MYSQLHOST} ${MYSQLDB}"


echo "Report Begin: $(date)"
 echo "MySQL Dump Begin: $(date)" >> ${LOG}
  echo "SELECT ${MYSQLTABLE}.created_at, ${MYSQLTABLE}.product_options FROM ${MYSQLTABLE} WHERE ${MYSQLTABLE}.product_id=1 ORDER BY ${MYSQLTABLE}.created_at" | mysql ${MYSQLOPTS} > ${MYSQLDUMP}
 echo "MySQL Dump End: $(date)" >> ${LOG}
echo "Report Successful: $(date)"

这我OUPUTS MySQL查询到一个标签分隔的文件的 report.csv 的。但是,我需要把它输出到一个逗号分隔的文件。我知道我可以创建另一个脚本此文件从TAB转换为逗号分隔的,但是,我宁愿如果我可以节省的步骤。所以,我怎么能有MySQL的转储逗号分隔格式的文件?

This ouputs my MySQL Query into a "TAB" separated file report.csv. However, I need to have it output to a "COMMA" separated file. I realize I could create another script to convert this file from TAB to COMMA separated, however, I'd rather save the step if I can. So how can I have MySQL dump the file in comma separated format?

编辑:
我没有找到这个解决方案:<一href=\"http://stackoverflow.com/questions/707473/how-do-you-output-mysql-query-results-in-csv-format-to-the-screen-not-to-a-file\">http://stackoverflow.com/questions/707473/how-do-you-output-mysql-query-results-in-csv-format-to-the-screen-not-to-a-file

不过,我似乎无法得到它的工作:

However I can't seem to get it to work:

echo "SELECT CONCAT_WS(',', ${MYSQLTABLE}.created_at, ${MYSQLTABLE}.product_options) FROM ${MYSQLTABLE} WHERE ${MYSQLTABLE}.product_id=1 ORDER BY ${MYSQLTABLE}.created_at" | mysql ${MYSQLOPTS} > ${MYSQLDUMP}

不工作:(

推荐答案

找到一个简单的Perl 1班轮,似乎这样的伎俩:

Found a simple perl 1-liner that seems to do the trick:

perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F '

- 的Perl脚本

因此​​,在我的脚本行看起来是这样的:

So the line in my script looks like this:

    	echo "SELECT ${MYSQLTABLE}.created_at, ${MYSQLTABLE}.product_options FROM ${MYSQLTABLE} WHERE ${MYSQLTABLE}.product_id=1 ORDER BY ${MYSQLTABLE}.created_at" | mysql ${MYSQLOPTS} | perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' > ${MYSQLDUMP}

感谢所有帮助,特别是从比尔Karwin,我想一个VoteUp添加到您的答案,但我没有足够的声誉:(
一旦我得到更多的代表,我会绕回来,并投你的答案了。
再次感谢。

Thanks for all the help, Especially from Bill Karwin, I wanted to add a VoteUp to your answer but I don't have enough reputation :( Once I get more rep, I'll circle back and vote your answer up. Thanks again.

这篇关于BASH MySQL查询,以逗号分隔的文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆