如何为MySQL更新语句创建含异常处理的存储过程及最佳实践

兄弟，你这个更新500万条记录的存储过程可不能这么简陋啊！作为踩过无数坑的MySQL老玩家，我得给你好好捋捋正规的最佳实践——尤其是不加错误处理的话，万一中途出问题，你连哪错了、更到哪一步都不知道，排查起来能头大到爆炸！

1. 错误处理是底线，必须加！ #

MySQL存储过程里用DECLARE HANDLER就能轻松捕获各种SQL错误（比如锁超时、字段不存在、数据格式错误），出错时还能回滚事务、记录错误信息，绝对不能省。

给你个带基础错误处理的框架：

DELIMITER //
CREATE PROCEDURE test_item_update()
BEGIN
    -- 先声明变量存错误信息和代码
    DECLARE err_code INT;
    DECLARE err_msg VARCHAR(255);
    
    -- 声明错误处理器：只要出SQL异常，就执行下面的逻辑
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 出错就回滚，别留半截事务
        ROLLBACK;
        -- 把错误码和信息捞出来
        GET DIAGNOSTICS CONDITION 1
            err_code = MYSQL_ERRNO, err_msg = MESSAGE_TEXT;
        -- 建议把错误写到专门的日志表，方便后续查问题
        INSERT INTO op_error_log (log_time, err_code, err_msg, op_type)
        VALUES (NOW(), err_code, err_msg, 'BATCH_UPDATE');
        -- 给个明确的失败提示
        SELECT CONCAT('更新炸了！错误码：', err_code, '，原因：', err_msg) AS result;
    END;
    
    -- 开启事务，要么全成要么全败
    START TRANSACTION;
    
    -- 这里放你的更新逻辑（后面会说为啥不能一次性更500万）
    -- UPDATE your_table SET ... WHERE ...;
    
    -- 没出错就提交
    COMMIT;
    SELECT '500万条记录更新完成！' AS result;
END //
DELIMITER ;

2. 500万条绝对不能一次性更，必须分批！ #

一次性更这么多数据会搞出大问题：锁表时间太长，其他业务根本用不了这个表；事务日志直接暴涨，磁盘IO可能扛不住；甚至可能因为执行时间太长被MySQL的超时机制干掉，白忙活一场。

正确的做法是按主键/唯一索引分批，比如每次更1万条，循环执行：

-- 把上面存储过程里的更新逻辑换成这个循环
DECLARE done INT DEFAULT 0;
DECLARE max_record_id BIGINT;
DECLARE current_start_id BIGINT DEFAULT 0;

-- 先拿到要更新的最大ID（假设你的表有自增主键id）
SELECT MAX(id) INTO max_record_id FROM your_table WHERE 你的过滤条件;

-- 循环分批更
WHILE current_start_id < max_record_id DO
    UPDATE your_table
    SET target_column = new_value
    WHERE id > current_start_id 
      AND id <= current_start_id + 10000 
      AND 你的过滤条件;
    
    -- 更新当前的起始ID
    SET current_start_id = current_start_id + 10000;
    
    -- 可选：每次更完歇0.1秒，给数据库喘口气，避免压力太大
    DO SLEEP(0.1);
END WHILE;

要是你的表没有自增主键，用创建时间、唯一编号这种有序字段分批也行，核心是别让MySQL每次都扫全表，效率太低。

3. 几个能帮你少踩坑的细节 #

• 先检查索引：UPDATE语句的WHERE条件里的字段一定要有索引！不然全表扫描一次500万条，慢到你怀疑人生。
• 提前备份：执行这么大的更新前，一定要先备份表！比如CREATE TABLE your_table_backup LIKE your_table; INSERT INTO your_table_backup SELECT * FROM your_table;，万一更错了还能救回来。
• 记录更新进度：可以加个进度日志表，每次分批更新后插一条记录，写清楚更到哪个ID、更了多少行，就算中途断了，也能从断点继续，不用从头再来。
• 别搞超大事务：如果你的数据库配置一般，也可以把每一批更新放在独立事务里（每更完一批就提交），避免事务日志撑爆磁盘。

最后总结下 #

你的原始存储过程只适合小批量数据操作，500万条这种量级，错误处理、分批执行、性能优化这三点一个都不能少，这样才能保证更新安全、不影响业务。

内容的提问来源于stack exchange，提问作者badri