数据库中数据导出到csv文件中

需求：导出数据库中数据，生成csv文件（定时任务去做）

入口：扫表操作

扫表的sql：（polljob.sql）

selectoid,jobId,batchCode,COALESCE(batchStartTime,'2000-01-01'),COALESCE(batchEndTime,'2030-01-01'),jobStatus,COALESCE(offsetOid,'0')

fromT_MONEY_JOBWHEREworkerPidisnullandjobStatusin('toRun');

根据扫描出的内容，做具体的操作（jobpoller.sh）：

#!/bin/bash

basedir=`dirname$`

cd$basedir

whileIFS=$'\t'readoid jobId batchCode batchStartTime batchEndTime jobStatus offsetOid productOid

doargs=("$jobId""$oid""$batchCode""$batchStartTime""$batchEndTime""$jobStatus""$offsetOid""$productOid")script=csvgen.shsh$script"$"

done

进入到csvgen.sh脚本：

#!/bin/sh

key=$1

condition=$2

filetype=$key

basedir=`dirname$`

confirmDate=$(date +"%Y-%m-%d"-d'-1 day')

startTime="$00:00:00"

endTime="$23:59:59"

[$basedir=='.'] && basedir=$PWD

datadir=$basedir/$key/data

logsdir=$basedir/$key/logs

# remove old files within one same minute

oldDate=$(date-d"last month""+%Y%m%d")

if[-d$datadir/$oldDate];then

rm -rf$datadir/$oldDate

echo"完成删除动作"

if[-d$datadir/$oldDate];then

echo"删除失败"

else

echo"删除成功"fi

else

echo"目录不存在"

fi

[-d$datadir] || mkdir -p$datadir

[-d$logsdir] || mkdir -p$logsdir

today=`date +%Y%m%d`

fileDate=`date +%Y-%m-%d`

batch="`date +%Y%m%d%H%M00`"

datadir=$datadir/$today/$batch

[-d$datadir] || mkdir -p$datadir

#filepre=$datadir/customer_$key_

shift

#args: jobId,batchCode,batchStart,batchEnd,jobStatus,offsetOid,productOid

jobId=$1

batchCode=$2

batchStart=$3

batchEnd=$4

jobStatus=$5

offsetOid=$6

productOid=$7

["x$offsetOid"="x"] && offsetOid=$(date +%s)

myfiles=("customer_order_")

["$key"="income"] && myfiles=("customer_income_")

["$key"="order"] && myfiles=("customer_order_")

["$key"="hold"] && myfiles=("customer_hold_")

forxin$

do

#hold

if["$key"="hold"];then

#csv

holdSQLfile="hold_$confirmDate.sql"

holdCSVfile="$$_0.csv"

notifyContent={'"'fileType'"':'"'hold'"','"'fileName'"':'"'$holdCSVfile'"','"'fileDate'"':'"'$fileDate'"','"'filePath'"':'"'$datadir'/"'}

sed-e"s/#startTime/$startTime/g"-e"s/#endTime/$endTime/g"$basedir/hold.temp.sql >$datadir/$holdSQLfile

$mysqlcli-N -r $datadir/$holdCSVfile

cat$datadir/$holdSQLfile

echo"======================================="

#log

holdlogname="$$_0.log"

holdlogfile=$logsdir/$holdlogname

echo$(date) start >>$holdlogfile

echo"confirmDate=$, startTime=$, endTime=$">>$holdlogfile

echo$(date) end >>$holdlogfile

#income

elif["$key"="income"];then

#csv

incomeSQLfile="income_$confirmDate.sql"incomeCSVfile="$$_0.csv"

notifyContent={'"'fileType'"':'"'income'"','"'fileName'"':'"'$incomeCSVfile'"','"'fileDate'"':'"'$fileDate'"','"'filePath'"':'"'$datadir'/"'}

sed-e"s/#startTime/$startTime/g"-e"s/#endTime/$endTime/g"$basedir/income.temp.sql >$datadir/$incomeSQLfile

$mysqlcli-N -r $datadir/$incomeCSVfile

cat$datadir/$incomeSQLfile

echo$fileDate+"fileDate"

echo"======================================="

#log

incomelogname="$$_0.log"

incomelogfile=$logsdir/$incomelogname

echo$(date) start >>$incomelogfile

echo"confirmDate=$, startTime=$, endTime=$">>$incomelogfile

echo$(date) end >>$incomelogfile

elif["$key"="order"];then

#csv

orderSQLfile="order_$confirmDate.sql"

orderCSVfile="$$_0.csv"

notifyContent={'"'fileType'"':'"'order'"','"'fileName'"':'"'$orderCSVfile'"','"'fileDate'"':'"'$fileDate'"','"'filePath'"':'"'$datadir'/"'}

sed-e"s/#offsetOid/$offsetOid/g"$basedir/order.temp.sql >$datadir/$orderSQLfile

$mysqlcli-N -r $datadir/$orderCSVfile

cat$datadir/$orderSQLfile

echo"======================================="

#log

orderlogname="$$_0.log"

orderlogfile=$logsdir/$orderlogname

echo$(date) start >>$orderlogfile

echo"confirmDate=$, startTime=$, endTime=$">>$orderlogfile

echo$(date) end >>$orderlogfile

else

echo"no command execute"

fi

#lock table

sql="update t_money_job set jobStatus='finished' where oid='$condition' "$mysqlcli-e"$sql"

#save notify

notifyOiduuid=`cat /proc/sys/kernel/random/uuid`

notifyoid=`echo$notifyOiduuid| sed's/-//g'`

notifyIduuid=`cat /proc/sys/kernel/random/uuid`

notifyId=`echo$notifyIduuid| sed's/-//g'`

notifyDate=`date"+%Y-%m-%d %H:%M:%S"`

sqlnotify="insert into t_money_platform_notify(oid,notifyId,notifyType,notifyContent,errorCode,notifyStatus,notifyTimes,seqId,updateTime,createTime) values('$notifyoid','$notifyId','documentExport','$notifyContent','0','toConfirm','0','0','$notifyDate','$notifyDate')"

$mysqlcli-e"$sqlnotify"

done

echo"finished!!"

根据不同的条件去执行不同的sql：

SELECT'productOid, investorOid,totalVolume,holdVolume,toConfirmInvestVolume,toConfirmRedeemVolume,redeemableHoldVolume,lockRedeemHoldVolume,expGoldVolume,totalInvestVolume,accruableHoldVolume,value,holdTotalIncome, totalBaseIncome,totalRewardIncome,holdYesterdayIncome,yesterdayBaseIncome,yesterdayRewardIncome,incomeAmount,redeemableIncome,lockIncome,confirmDate,expectIncome,expectIncomeExt,accountType,maxHoldVolume,dayRedeemVolume,dayInvestVolume,dayRedeemCount, productAlias, holdStatus, productType'

UNIONALL

SELECTCONCAT( t1.productOid,', ',

t1.investorOid,', ',

TRUNCATE(t1.totalVolume *100,),', ',

TRUNCATE(t1.holdVolume *100,),', ',

TRUNCATE(t1.toConfirmInvestVolume *100,),', ',

TRUNCATE(t1.toConfirmRedeemVolume *100,),', ',

TRUNCATE(t1.redeemableHoldVolume *100,),', ',

TRUNCATE(t1.lockRedeemHoldVolume *100,),', ',

TRUNCATE(t1.expGoldVolume *100,),', ',

TRUNCATE(t1.totalInvestVolume *100,),', ',

TRUNCATE(t1.accruableHoldVolume *100,),', ',

TRUNCATE(t1.value *100,),', ',

TRUNCATE(t1.holdTotalIncome *100,),', ',

TRUNCATE(t1.totalBaseIncome *100,),', ',

TRUNCATE(t1.totalRewardIncome *100,),', ',

TRUNCATE(t1.holdYesterdayIncome *100,),', ',

TRUNCATE(t1.yesterdayBaseIncome *100,),', ',

TRUNCATE(t1.yesterdayRewardIncome *100,),', ',

TRUNCATE(t1.incomeAmount *100,),', ',

TRUNCATE(t1.redeemableIncome *100,),', ',

TRUNCATE(t1.lockIncome *100,),', ',

IFNULL(t1.confirmDate,'(null)'),', ',

TRUNCATE(t1.expectIncome *100,),', ',

TRUNCATE(t1.expectIncomeExt *100,),', ',

t1.accountType,', ',

TRUNCATE(t1.maxHoldVolume *100,),', ',

TRUNCATE(t1.dayRedeemVolume *100,),', ',

TRUNCATE(t1.dayInvestVolume *100,),', ',

t1.dayRedeemCount,', ',

t1.productAlias,', ',

t1.holdStatus,', ',

t2.type)

FROM`t_money_publisher_hold`t1,`t_gam_product`t2WHEREt1.productOid=t2.oid

SELECT'productOid, investorOid, incomeAmount, confirmDate, beforeVolume, afterVolume,productType '

UNIONALL

SELECTCONCAT( t1.productOid,', ',

t1.investorOid,', ',

TRUNCATE(t1.incomeAmount *100,),', ',

t1.confirmDate,', ',

TRUNCATE(t1.accureVolume *100,),', ',

TRUNCATE((t1.accureVolume + t1.incomeAmount) *100,),', ', t2.type)

FROM`t_money_publisher_investor_holdincome`t1,`t_gam_product`t2WHEREt1.confirmDate >='#startTime'ANDt1.confirmDate

SELECT'investorOid,orderCode, productOid,orderType,orderTime,orderStatus,orderAmount,productType'

UNIONALL

SELECTCONCAT( t1.investorOid,', ',

t1.`orderCode`,', ',

t1.`productOid`,', ',

t1.`orderType`,', ',

t1.`orderTime`,', ',

t1.`orderStatus`,', ',

TRUNCATE(t1.`orderAmount`*100,),', ',

t2.type)

FROM`t_money_investor_tradeorder`t1,`t_gam_product`t2WHEREt1.publisherOffsetOid ='#offsetOid'ANDt1.productOid=t2.oid

mydb=ddyin

basedir=`dirname$`

[$basedir=='.'] && basedir=$PWD

mysqlcli="mysql --defaults-extra-file=$basedir/mysqlclient.conf -N -r$mydb"

cid=2

token=5EA77F0752E2EF36973EB64D8730CC6

(mysql)配置：

#!/bin/sh

db=ddyin

[$1] && db=$1

mysql --defaults-extra-file=mysqlclient.conf$db

(mysqlclient.conf)配置：

[client]host=1.1.1.1

user=1

password=1

default-character-set=utf8

全局配置文件：（crontab）-----定义定时任务

SHELL=/bin/bashPATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/local/redis/binMAILTO=root

HOME=/

*/5 * * * * root /bin/bash /customer/gencmd/jobpoller.sh

每五秒执行一次。

数据库配置已mock（非真实）