MySQL 大表分批次更新存储过程

DROP PROCEDURE IF EXISTS big_table_update;
CREATE PROCEDURE big_table_update()
BEGIN
	# 每批次处理的记录条数，可自行修改
	DECLARE v_update_limit INT(10) DEFAULT 5;
    DECLARE v_primary_key_start, v_primary_key_end BIGINT(20) DEFAULT 0;

    # 异常捕获
    DECLARE v_error_code CHAR(5) DEFAULT '00000';
    DECLARE v_error_message VARCHAR(512) DEFAULT '';
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            GET DIAGNOSTICS CONDITION 1 v_error_code= RETURNED_SQLSTATE,v_error_message= MESSAGE_TEXT;
            INSERT INTO error_log(type, title, code, message) VALUES ('test', 'big_table_update', v_error_code, v_error_message);
            COMMIT;
        END;

    WHILE EXISTS(SELECT 1 FROM big_table WHERE id > v_primary_key_start)
        DO
        BEGIN
            START TRANSACTION;
            # 查询当前批次处理的最大的主键
            SELECT id INTO v_primary_key_end FROM (SELECT id 'id' FROM big_table WHERE id > v_primary_key_start LIMIT v_update_limit) temp ORDER BY temp.id DESC  LIMIT 1;

            ##################### 自定义更新数据语句【开始】#####################
            UPDATE big_table SET mobile = mobile + 1 WHERE id BETWEEN v_primary_key_start AND v_primary_key_end;
            ##################### 自定义更新数据语句【结束】#####################

            # 重新设置主键起始值
            SET v_primary_key_start = v_primary_key_end + 1;
            COMMIT;
            # 暂停5毫秒
            SELECT SLEEP(0.005);
        END;
        END WHILE;
        ROLLBACK;
END;

# 测试执行
CALL big_table_update();

big_table 表结构及数据

CREATE TABLE big_table
(
    id     INT AUTO_INCREMENT
        PRIMARY KEY,
    mobile INT NULL
);
INSERT INTO big_table (id, mobile) VALUES (1, 1);
INSERT INTO big_table (id, mobile) VALUES (2, 2);
INSERT INTO big_table (id, mobile) VALUES (3, 3);
INSERT INTO big_table (id, mobile) VALUES (4, 4);
INSERT INTO big_table (id, mobile) VALUES (5, 5);
INSERT INTO big_table (id, mobile) VALUES (6, 6);
INSERT INTO big_table (id, mobile) VALUES (7, 7);
INSERT INTO big_table (id, mobile) VALUES (8, 8);
INSERT INTO big_table (id, mobile) VALUES (9, 9);
INSERT INTO big_table (id, mobile) VALUES (10, 10);
INSERT INTO big_table (id, mobile) VALUES (11, 11);
INSERT INTO big_table (id, mobile) VALUES (12, 12);
INSERT INTO big_table (id, mobile) VALUES (13, 13);
INSERT INTO big_table (id, mobile) VALUES (14, 14);
INSERT INTO big_table (id, mobile) VALUES (15, 15);
INSERT INTO big_table (id, mobile) VALUES (16, 16);
INSERT INTO big_table (id, mobile) VALUES (17, 17);
INSERT INTO big_table (id, mobile) VALUES (18, 18);
INSERT INTO big_table (id, mobile) VALUES (19, 19);
INSERT INTO big_table (id, mobile) VALUES (20, 20);
INSERT INTO big_table (id, mobile) VALUES (21, 21);
INSERT INTO big_table (id, mobile) VALUES (22, 22);

error_log 表结构

CREATE TABLE error_log
(
    id          INT AUTO_INCREMENT
        PRIMARY KEY,
    type        VARCHAR(128)                        NULL,
    title       VARCHAR(128)                        NULL,
    code        VARCHAR(128)                        NULL,
    message     TEXT                                NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL
);