优化SQL查询：将变量值复用至后续查询的技术问询

原代码存在的问题 #

1. 参数拼写错误：定义的参数pram_userID应为param_userID，拼写错误会导致调用存储过程时参数匹配失败。
2. 变量赋值逻辑不规范：在UPDATE语句的SET子句中直接给变量赋值，虽然当前LIMIT 1的场景下可能暂时生效，但这种写法不符合MySQL最佳实践——若后续调整逻辑移除LIMIT，变量会被多次赋值，最终结果不可控。
3. 缺乏事务控制：三个UPDATE操作是强关联的业务逻辑（结束骑行→扣余额→释放SKU），若中间某一步执行失败，会造成数据不一致（比如骑行记录已更新，但用户余额未扣除）。

优化后的代码 #

CREATE DEFINER=`root`@`localhost` PROCEDURE `RIDE_stop`(
    IN `param_skuID` INT, 
    IN `param_cmimi_per_1_sekond` DECIMAL(6,4), 
    IN `param_userID` INT
)
BEGIN
    DECLARE VAR_trip_time INT;
    DECLARE VAR_cmimi INT;
    DECLARE VAR_rideID INT; -- 锁定要操作的骑行记录ID

    -- 开启事务，保证操作原子性
    START TRANSACTION;

    -- 先查询获取目标骑行记录的计算值与ID
    SELECT 
        TIMESTAMPDIFF(SECOND, R.startTime, NOW()) INTO VAR_trip_time,
        ROUND(TIMESTAMPDIFF(SECOND, R.startTime, NOW()) * param_cmimi_per_1_sekond) INTO VAR_cmimi,
        R.rideID INTO VAR_rideID
    FROM sku_rides R
    INNER JOIN sku S ON S.skuID = R.skuID
    WHERE R.skuID = param_skuID 
      AND R.userID = param_userID 
      AND R.endTime IS NULL 
    ORDER BY R.rideID DESC 
    LIMIT 1;

    -- 仅当找到有效骑行记录时执行后续操作
    IF VAR_rideID IS NOT NULL THEN
        -- 更新骑行记录
        UPDATE sku_rides R
        INNER JOIN sku S ON S.skuID = R.skuID
        SET 
            R.endTime = NOW(),
            R.zona = S.zona,
            R.cmimi = VAR_cmimi,
            R.trip_time = VAR_trip_time
        WHERE R.rideID = VAR_rideID;

        -- 扣除用户余额
        UPDATE users 
        SET kredit = kredit - VAR_cmimi 
        WHERE userID = param_userID;

        -- 释放SKU资源
        UPDATE sku 
        SET userID = NULL 
        WHERE skuID = param_skuID;

        -- 提交事务
        COMMIT;
    ELSE
        -- 未找到有效记录，回滚并抛出错误
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '未找到可结束的有效骑行记录';
    END IF;
END

优化说明 #

• 修正参数错误：统一参数命名，避免调用异常。
• 明确变量赋值逻辑：通过SELECT...INTO提前计算并锁定目标记录，确保变量值唯一且对应正确的骑行数据，避免UPDATE中赋值的不确定性。
• 事务原子性保障：将三个关联操作纳入事务，要么全部执行成功，要么全部回滚，彻底避免数据不一致问题。
• 增加异常处理：对未找到骑行记录的场景抛出明确错误，便于调试和业务感知。
• 提升代码可读性：拆分逻辑步骤，让业务流程更清晰，后续维护更方便。

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