mysql通过循环增加行_循环读多行数据插入到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