MySQL如何判断UPDATE语句执行成功 存储过程事务全场景回滚方案求解

我们可以把SQL异常捕获和自定义行数校验规则结合，兼顾异常和无匹配行的场景，改造逻辑如下：

1. 首先声明退出处理器，所有SQL异常触发时自动回滚事务并抛出错误
2. 每次执行DML后统一做行数校验，不满足预期行数就主动抛出异常，由统一的异常处理器处理回滚，避免每条语句重复写回滚逻辑
3. 注意入参名不要和表字段重名，原代码中的moeny属于拼写错误（应为money），入参userID和表字段UserID重名容易导致查询条件失效，建议给入参加前缀区分

改造后的完整代码：

CREATE DEFINER=`dev`@`%` PROCEDURE `sp_buyScore`(IN p_userID int, IN p_money int)
BEGIN
    DECLARE original_balance int;   
    -- 统一异常处理：任何SQL异常触发时自动回滚并抛出错误
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL; -- 保留原始错误信息，也可自定义抛出内容
    END;

    START TRANSACTION; 
    -- 锁定用户余额行，避免并发修改导致的脏数据
    SELECT balance.`balance` FROM balance WHERE balance.`UserID` = p_userID INTO original_balance FOR UPDATE;
    -- 处理用户不存在的边界场景
    IF original_balance IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户不存在';
    END IF;
    IF original_balance - p_money < 0 THEN  
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足，无法购买';
    END IF;

    -- 执行余额扣减
    UPDATE balance SET balance = balance - p_money WHERE balance.`UserID` = p_userID;
    -- 校验第一条更新是否生效
    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户余额更新失败';
    END IF;

    -- 执行积分增加
    UPDATE userScore SET Score = Score + p_money * 10 WHERE userScore.`UserID` = p_userID;
    -- 校验第二条更新是否生效
    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户积分更新失败';
    END IF;

    COMMIT;
END

如果后续需要扩展更多DML操作，只需要在每条DML后加行数校验逻辑即可，所有错误场景都会被统一的异常处理器自动回滚，不用重复编写回滚代码。如果觉得逐行加校验太繁琐，也可以把校验逻辑封装成存储函数，调用SELECT check_row_count('操作描述')即可实现复用，适配性更高。

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