循环读多行数据插入到mysql中的一列(脚本)
时间：2009-08-10 来源：nychenlei
从数据库中读出多行数据，用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++))
done<load_tmp.txt
done
#/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
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++))
done<load_tmp.txt
done
#/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
相关阅读 更多 +
