MySQL存储过程动态设置循环变量失效问题求助

问题背景 #

需要编写存储过程实现以下逻辑：

• 匹配价格表与商品表的idProduct，将生效日期早于当前日期的最新价格更新到商品表的currentPrice列
• 支持向价格表插入未来日期的记录，提前安排价格变动

原存储过程代码（硬编码id范围可正常运行，动态获取id范围无报错但无法完成更新）：

DELIMITER $$
 create procedure updatePrice()
 begin
 declare loopLeng int default 1000;
 declare loopMax int default 1099;
 declare newPrice decimal(10,2);
 --select min(idProduct) into loopLeng from product;
--select count(idProduct) into loopMax from product;
set loopLeng  = 1000;
set loopMax = 1099;
 updateLoop : LOOP
    if loopLeng > loopMax  then 
    leave updateLoop;
    end if;
    select price into newPrice from price where idProduct = loopLeng and dateApplicableFrom = (select max(dateApplicableFrom) from price where idProduct = loopLeng and dateApplicableFrom <= current_timestamp());
    update product set currentPrice = newPrice where idProduct = loopLeng;
    set loopLeng = loopLeng + 1; 
    end loop;
    end
    $$ DELIMITER ;

问题原因 #

1. loopMax赋值逻辑错误：注释中的select count(idProduct) into loopMax from product完全错误，count(idProduct)返回的是商品总数，而非最大的idProduct值。即便改成max(idProduct)，仍存在后续问题。
2. id不连续导致的中断：如果idProduct存在跳号（比如商品被删除、id非连续生成），循环中会遇到不存在的idProduct，此时select price into newPrice会因无返回值抛出异常，直接中断存储过程，无法完成所有商品的更新。
3. 循环效率低下：逐行循环更新的方式在商品数量较多时性能极差，远不如批量更新高效。

解决方案 #

方案1：使用游标遍历存在的商品id（规避id不连续问题） #

通过游标获取所有真实存在的idProduct，逐个处理：

DELIMITER $$
CREATE PROCEDURE updatePrice()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE currentId INT;
    DECLARE newPrice DECIMAL(10,2);
    -- 声明游标，获取所有商品id
    DECLARE productCursor CURSOR FOR SELECT idProduct FROM product;
    -- 处理游标结束的情况
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN productCursor;
    readLoop: LOOP
        FETCH productCursor INTO currentId;
        IF done THEN
            LEAVE readLoop;
        END IF;
        -- 获取当前商品的最新有效价格
        SELECT price INTO newPrice 
        FROM price 
        WHERE idProduct = currentId 
          AND dateApplicableFrom = (
              SELECT MAX(dateApplicableFrom) 
              FROM price 
              WHERE idProduct = currentId 
                AND dateApplicableFrom <= CURRENT_TIMESTAMP()
          );
        -- 更新商品表，无有效价格时保留原价格（可按需改为NULL或默认值）
        UPDATE product 
        SET currentPrice = COALESCE(newPrice, currentPrice)
        WHERE idProduct = currentId;
    END LOOP;
    CLOSE productCursor;
END $$
DELIMITER ;

方案2：单条UPDATE语句批量更新（最优性能） #

无需循环，直接通过关联子查询完成批量更新，性能远优于游标：

DELIMITER $$
CREATE PROCEDURE updatePrice()
BEGIN
    -- 更新有有效价格的商品
    UPDATE product p
    JOIN (
        SELECT 
            idProduct,
            price AS latestValidPrice
        FROM price
        WHERE (idProduct, dateApplicableFrom) IN (
            SELECT 
                idProduct,
                MAX(dateApplicableFrom)
            FROM price
            WHERE dateApplicableFrom <= CURRENT_TIMESTAMP()
            GROUP BY idProduct
        )
    ) pp ON p.idProduct = pp.idProduct
    SET p.currentPrice = pp.latestValidPrice;
    
    -- 可选：处理无有效价格的商品，将currentPrice设为NULL或指定默认值
    UPDATE product p
    LEFT JOIN (
        SELECT DISTINCT idProduct 
        FROM price 
        WHERE dateApplicableFrom <= CURRENT_TIMESTAMP()
    ) pp ON p.idProduct = pp.idProduct
    SET p.currentPrice = NULL
    WHERE pp.idProduct IS NULL;
END $$
DELIMITER ;

补充说明 #

• 方案2的批量更新方式在商品数量较多时性能优势明显，优先推荐。
• 若无需处理无有效价格的商品，可去掉第二个UPDATE语句。
• 建议在price表的idProduct和dateApplicableFrom字段上建立联合索引，大幅提升查询效率。

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