循环读多行数据插入到mysql中的一列(脚本)

从数据库中读出多行数据，用shell循环读出每一行，插入到数据库中。插成一条记录。

 

#!/bin/sh
TxIp=192.168.0.103
bindir=/usr/local/mysql/bin/mysql
datadir=/data/txdata/test
yestoday=`date -I --date='-1 days'`
#echo $Date >> load.txt
#[ -f load.txt ] && rm -f load.txt
for n in $(seq 10 132)
do
 IP=192.168.0.$n
$bindir -uwaptx -p"(YDtx405)" -h $TxIp -P12341 --default_character_set=GBK txbackend -e "select replace(a.description,'@value@',b.value) as value,b.createTime from alert_config as a,alert_mobile_history_log as b where a.id=b.alertId and a.description like 'load%' and a.ip='$IP' and date_format(b.createTime,'%Y-%m-%d')='$yestoday'"|sed 1d >load_tmp.txt
 exist=`cat load_tmp.txt | wc -l`
 if [ $exist -eq 0 ]
 then
  rm -f load_tmp.txt
 else
loadIp=`$bindir -uwaptx -p"(YDtx405)" -h $TxIp -P12341 --default_character_set=GBK txbackend -e "select distinct a.ip from alert_config as a,alert_mobile_history_log as b where a.id=b.alertId and a.description like 'load%' and a.ip='$IP' and date_format(b.createTime,'%Y-%m-%d')='$yestoday'" | sed 1d`

alertId=`$bindir -uwaptx -p"(YDtx405)" -h $TxIp -P12341 --default_character_set=GBK txbackend -e "select distinct b.alertId from alert_config as a,alert_mobile_history_log as b where a.id=b.alertId and a.description like 'load%' and a.ip='$IP' and date_format(b.createTime,'%Y-%m-%d')='$yestoday'" | sed 1d`

loadCount=`$bindir -uwaptx -p"(YDtx405)" -h $TxIp -P12341 --default_character_set=GBK txbackend -e "select count(*) from alert_config as a, alert_mobile_history_log as b where a.id=b.alertId and a.description like 'load%' and a.ip='$IP' and date_format(b.createTime,'%Y-%m-%d')='$yestoday'" | sed 1d`

#n=`cat load_tmp.txt | wc -l`
#int load1[]={}
#aint time2[]={}
count=`cat load_tmp.txt|wc -l`
num=1
while [ "$num" -le "$count" ]
do
 while read line
 do
  load=`echo "$line"|awk '{print $2}'`
  time=`echo "$line"|awk '{print $4}'`
 if [ "$num" -eq "1" ]
 then
 $bindir --defaults-file=$datadir/my.cnf txtest -e "insert into chen_load (Date,Ip,Id,count,load$num,time$num) value ('$yestoday','$loadIp','$alertId','$loadCount','$load','$time');"
 else
 $bindir --defaults-file=$datadir/my.cnf txtest -e "update chen_load set load$num='$load',time$num='$time' where Ip='$loadIp' and Date='$yestoday';"
 fi
 ((num++))
 donedone
#/usr/local/mysql/bin/mysql --defaults-file=/data/txdata/test/my.cnf txtest -e "insert into chen_load (Date,Ip,Id,count,load1,time1,load2,time2,load3,time3,load4,time4,load5,time5) value ('$yestoday','$loadIp','$alertId','$loadCount','$load1','$time1','$load2','$time2','$load3','$time3','$load4','$time4','$load5','$time5')"
fi
done