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

1. 问题背景

由于爬虫同事粗心所致，导致数据库表core_comparison_platform_goods_item（总数据量382w）出现约14.2w条photo_url的http前缀重复，导致APP商品数据图片空白。


出现问题数据量：14.2w，单次count查询耗费约60s
![W$AYL22KEIU[JUO890%7.png

2. 预期目标

从core_comparison_platform_goods_item表中382w数据中，update 14.2w问题数据，即：将问题数据photoUrl字段http:http:替换为http:。
由于该表是业务非常活跃的表，如果仅仅通过一个update语句，进行一次性全量更新，将导致锁表问题（至少锁表一分多钟，影响APP业务）。故而，最终敲定方案为：分批次更新，每更新批次数据量为5条，每一个批次一个事务。

3. 相关实现

3.1 建立日志表

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
);

3.2 数据处理存储过程

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 ('skl_photoUrl_update', 'big_table_update', v_error_code, v_error_message);
            COMMIT;
        END;
    WHILE EXISTS(SELECT 1 FROM core_comparison_platform_goods_item skl WHERE skl.photo_url like 'http:http:%' and skl.item_id > v_primary_key_start)
        DO
        BEGIN
            START TRANSACTION;
            # 查询当前批次处理的最大的主键
            SELECT item_id INTO v_primary_key_end FROM (SELECT item_id 'item_id' FROM core_comparison_platform_goods_item 
                        WHERE photo_url like 'http:http:%' and item_id > v_primary_key_start 
                        LIMIT v_update_limit) temp ORDER BY temp.item_id DESC  LIMIT 1;
            ############################### 自定义更新数据语句【开始】###########################################################
            UPDATE core_comparison_platform_goods_item SET photo_url = replace( photo_url, 'http:http:', 'http:' )  
                        WHERE item_id BETWEEN v_primary_key_start AND v_primary_key_end and photo_url like 'http:http:%';
            ############################### 自定义更新数据语句【结束】###########################################################
            # 重新设置主键起始值
            SET v_primary_key_start = v_primary_key_end + 1;
            COMMIT;
            # 暂停10毫秒
            SELECT SLEEP(0.010);
        END;
        END WHILE;
        ROLLBACK;
END;

3.3 执行存储过程

# 执行存储过程
CALL big_table_update();