Mysql循环删除或更新数据

66 阅读 0 评论 44 点赞

我是靠谱客的博主还单身飞机，最近开发中收集的这篇文章主要介绍Mysql循环删除或更新数据，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

使用前提：

1.删除或更新数据时，需要使用索引条件，并且控制每次更新或删除的行数。

2.更新后的数据不能再次被更新，可以通过where条件进行控制实现。

python3脚本如下

from pymysql import connect as MysqlConn   #pip3 install pymysql==0.9.3
from datetime import datetime
from time import sleep

def loop_delete_update(host,port,user,pswd,db,sql):
    conn = MysqlConn(host=host, port=port, user=user, passwd=pswd, db=db, charset='utf8')
    total_count = 0
    while True:
        with conn.cursor() as cursor:
            #cursor.execute("SET SQL_LOG_BIN=0")    #是否关闭当前会话的binlog日志，关闭后速度更快，但是操作不会同步到从库
            counts = cursor.execute(sql)
            conn.commit()
        total_count += counts
        print("%st%s"%(datetime.now().strftime("%Y-%m-%dT%H:%M:%S.%f"),total_count))  #执行进度
        if counts == 0:   #上一次更新或删除的行数为0时，退出循环
            break
        sleep(0.01)   #控制频率
    conn.close()


sql = "DELETE FROM test WHERE create_time < '2020-10-01 00:00:00' LIMIT 200 "
loop_delete_update("127.0.0.1",3306,"root","123456","test",sql)

Mysql存储过程

CREATE TABLE IF NOT EXISTS dba_loop_execute_delete_update_log(create_date datetime default CURRENT_TIMESTAMP,effect_rows int ) ENGINE=MEMORY;

DROP PROCEDURE IF EXISTS dba_loop_execute_delete_update;
DELIMITER ;;
CREATE PROCEDURE dba_loop_execute_delete_update()
BEGIN
		DECLARE effect_rows INT DEFAULT 0; #影响的总行数
		DECLARE effect_row INT DEFAULT 0;  #单次影响的行数

		outer_label:WHILE TRUE DO 

				##替换下面的脚本，进行自定义更新或删除
				DELETE FROM check_data_count WHERE id<100000 LIMIT 1000;  ##执行删除操作


				SELECT ROW_COUNT() INTO effect_row;  ##上一次执行影响的行数
				IF effect_row < 1 THEN   ##如果影响的行数小于1，则跳出循环
					LEAVE outer_label;
				ELSE
					 SET effect_rows=effect_rows + effect_row;
				END IF;

				#如果不需要写日志，看进度的话，此部分可以不需要
				IF MOD(effect_rows,10000) =0 THEN  ##每删除10000行，写一次日志
						INSERT INTO dba_loop_execute_delete_update_log(effect_rows) VALUES(effect_rows);
				END IF;

				SELECT SLEEP(0.1);   #控制频率
		END WHILE;

		#如果不需要写日志，看进度的话，此部分可以不需要
		INSERT INTO dba_loop_execute_delete_update_log(effect_rows) VALUES(effect_rows); #记录总的更新或删除的行数

END ;;
delimiter ;

CALL dba_loop_execute_delete_update();   ##执行存储过程
SELECT * FROM dba_loop_execute_delete_update_log; #查看进度，需要另开一个会话


DROP PROCEDURE IF EXISTS dba_loop_execute_delete_update;
DROP TABLE IF EXISTS dba_loop_execute_delete_update_log;