BASH MySQL 查询以逗号分隔的文件

Question

我有以下 bash 脚本:

#!/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 转换为逗号分隔，但是，如果可以的话，我宁愿保存该步骤。那么如何让 MySQL 以逗号分隔格式转储文件？

编辑:我确实找到了这个解决方案:How do you output MySQL query results in csv format (to the screen, not to a file)?

但是我似乎无法让它工作:

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}

不起作用:(

Answer 1

运行以下命令:

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

tr命令将一个字符替换为另一个字符。