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

mysql的批量执行更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#!/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/
Spring Cloud中基于Sleuth的参数透传功能探索
Spring Cloud Gateway入门案例