Mysql批量执行更新shell脚本

在生产上执行更新sql，当更新的数据超过上100万的时候，执行更新操作会造成卡表或者锁表。本文主要记录一个了批量更新Mysql某张表的数据的脚本代码片。

mysql的批量执行更新

#!/bin/bash
. ~/.bash_profile
log=/home/apps/update/update_log_1_$(date +%F).log
vstart=1
step=10000
vstop=$((${vstart}+${step}))
max=14800000
echo "stop value is $vstop"
while [ ${vstart} -lt $((${max}+1)) ] 
do
        echo "`date +%F-%T`; mysql -uusername -ppwd --default-character-set=utf8 -S /tmp/mysql3306.sock vip_dbname -e\"UPDATE tbname set limit_days=3, update_time=now() where pid between ${vstart}  and ${vstop}\"" >> ${log}
        /apps/svr/mysql5/bin/mysql -uusername -ppwd --default-character-set=utf8 -S /tmp/mysql3306.sock vip_dbname -e"UPDATE  tbname set limit_days=3, update_time=now() where pid between ${vstart}  and ${vstop} "
        vstart=$((${vstop}+1))
        vstop=$((${vstop}+${step}))
        if [ ${vstop} -gt ${max} ]; then
                vstop=${max}
        fi
        sleep 1
        #echo "start at ${vstart}, stop at ${vstop}"
done

[原文地址] http://xujin.org/ex/mysql-batch-update/