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

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


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/101226/showart_2023513.html