BASH MySQL查询到逗号分隔文件 [英] BASH MySQL Query to Comma Separated File
问题描述
我有以下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)"
将MySQL查询转换成 TAB 分隔文件 report.csv 。但是,我需要将其输出到 COMMA 分隔的文件。我意识到我可以创建另一个脚本来将这个文件从TAB转换为COMMA,但是,如果可以,我宁愿保存步骤。所以我怎么能有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?
编辑:
我发现这个解决方案:如何输出MySQL查询结果以csv格式(到屏幕,而不是文件)?
但我似乎无法使其工作:
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-liner似乎做的诀窍:
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脚本
因此,我的脚本中的行如下所示: / p>
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}
$ b $感谢所有的帮助,尤其是从比尔·卡文,我想添加一个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屋!