总体来说:我接到这个需求当时有点不解,为什么我们要删除150天前的数据,因为数据都是很宝贵的;在国外工作,总会接到很多奇怪的需求,但是没有办法,既然有需求就积极的满足,同时提出了自己的意见。
至于为什么删除150天数据,因为涉及到公司的业务就不做解释了。

#查询150天前数据,取出主键ID,存于文本
mysql -u root -ppassword  -h IP -P 3306 -e "select 字段ID,字段ID,字段ID  from 数据库名.表名 where DATE_SUB(CURDATE(), INTERVAL 150 DAY) >= date(changeTime);" |awk -F '|' '{print $1}'|awk '{print $4}' >/home/mysql.data
#统计单表中要删除的数据记录的条目数,用于LIMIT 值	
countdata=`mysql -u root -ppassword  -h IP -P 3306 -e "select memberId,changeTime,changeId,count(*) from 数据库名.表名 where DATE_SUB(CURDATE(), INTERVAL 150 DAY) >= date(changeTime);"|grep '[0-9]'|awk '{print $5}'`


#!/bin/bash
/usr/bin/which bc
if [ $? -eq 0  ];then
        echo true
   else
        yum -y install bc
        echo "install bz"
 fi

for i in  $(cat /home/mysql.data)
        do

        if [ "$countdata" -gt 10000000 ];then
           numlimit=`expr "$countdata"/500|bc`
           echo "$numlimit"
                   mysql -u root -ppassword  -h IP -P 3306 -e "delete from amoeba101.a_account_change where changeId=$i   LIMIT $numlimit;"
        elif [ "$countdata" -gt 1000000 ]  && [ "$countdata" -lt 5000000 ];then
                numlimit=`expr "$countdata"/200|bc`
                        echo "$numlimit"
                        mysql -u root -ppassword  -h IP -P 3306 -e "delete from amoeba101.a_account_change where changeId=$i   LIMIT $numlimit;"
                elif [ "$countdata" -gt 500000 ] &&  [ "$countdata" -lt 999999 ];then
                        numlimit=`expr "$countdata"/100|bc`
                                echo "$numlimit"
                                mysql -u root -ppassword  -h IP -P 3306 -e "delete from amoeba101.a_account_change where changeId=$i   LIMIT $numlimit;"
                        elif [ "$countdata" -gt 100000 && "$countdata" -lt 499999 ] ;then
                        numlimit=`expr "$countdata"/50|bc`
                                echo "$numlimit"
                                      mysql -u root -ppassword  -h IP -P 3306 -e "delete from amoeba101.a_account_change where changeId=$i   LIMIT $numlimit;"
                            elif [ "$countdata" -gt 1 && "$countdata" -lt 99999 ] ;then
                                numlimit=`expr "$countdata"/20|bc`
                                        echo "$numlimit"
                                            mysql -u root -ppassword  -h IP -P 3306 -e "delete from amoeba101.a_account_change where changeId=$i   LIMIT $numlimit;"
							else
                                 exit 0

	fi
done

rm -f /home/mysql.data
打赏作者

Leave a Reply

Your email address will not be published.