MySQL存储过程执行后更新0行数据的排查与优化求助

问题背景 #

• 本地MySQL（root账号）创建的存储过程，预期更新约13000条数据，但执行后显示更新0行
• 修改UPDATE语句的WHERE子句后，触发Error Code: 1048. Column 'price' cannot be null错误
• 测试产品ID：512020060，对应156条属性记录、6种颜色，在14个店铺售卖（id_shop 13、15暂不启用）
• 核心逻辑：从sockelsat_product取标准价格stdPrice（值为229），循环遍历店铺（id_shop_counter 2-16），通过游标读取sockelsat_product_attribute的price_impact（范围-0.3000至15.0000），结合店铺factor（1至15）计算新价格，更新sockelsat_product_attribute_shop表
• 限制条件：sockelsat_product_attribute仅存储店铺1的价格系数，需复用至店铺2-16；每个商品需独立计算价格，不能用批量SET语句；id_product_attribute在各店铺唯一，reference表的id_product-measurement在各店铺通用

核心排查点 #

1. 游标未正确读取数据

   • 游标定义是否筛选了目标产品ID？是否包含id_product_attribute和price_impact必要字段？
   • 有没有添加NOT FOUND处理器？如果没有，游标耗尽后会进入死循环，无法执行后续更新
   • 循环前是否打开游标？循环结束后是否关闭？

2. UPDATE语句WHERE子句不匹配

   • 原WHERE子句可能未关联到sockelsat_product_attribute_shop的有效记录，导致0行更新
   • 修改WHERE后出现非空错误，说明价格计算逻辑中存在NULL值：要么stdPrice未取到，要么price_impact为NULL，要么店铺factor为NULL

3. 多店铺数据关联缺失

   • sockelsat_product_attribute_shop中是否存在对应店铺（2-16）的id_product_attribute记录？如果没有，UPDATE自然无法匹配到数据，需要先插入再更新
   • reference表的id_product-measurement关联是否正确，有没有遗漏条件导致数据匹配失败

分步修复方案 #

1. 修正游标逻辑 #

添加必要的处理器和调试输出，确保游标能正确读取数据：

-- 声明变量
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE v_attr_id INT;
DECLARE v_price_impact DECIMAL(10,4);
DECLARE v_shop_counter INT;
DECLARE v_factor INT;
DECLARE v_new_price DECIMAL(10,2);
DECLARE stdPrice DECIMAL(10,2);

-- 获取测试产品的标准价格
SELECT stdPrice INTO stdPrice FROM sockelsat_product WHERE id_product = 512020060;

-- 定义游标（先限定测试ID，排查完成后再放开全量）
DECLARE attr_cursor CURSOR FOR
SELECT id_product_attribute, price_impact
FROM sockelsat_product_attribute
WHERE id_product = 512020060;

-- 添加NOT FOUND处理器，避免死循环
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- 游标循环逻辑
SET done = FALSE;
OPEN attr_cursor;
attr_loop: LOOP
    FETCH attr_cursor INTO v_attr_id, v_price_impact;
    IF done THEN
        LEAVE attr_loop;
    END IF;

    -- 调试用：输出当前读取的属性ID和价格系数（可注释）
    -- SELECT v_attr_id, v_price_impact;

    -- 循环处理店铺（2-16，排除13、15）
    SET v_shop_counter = 2;
    shop_loop: LOOP
        -- 跳过暂不启用的店铺
        IF v_shop_counter IN (13,15) THEN
            SET v_shop_counter = v_shop_counter + 1;
            IF v_shop_counter > 16 THEN LEAVE shop_loop; END IF;
            ITERATE shop_loop;
        END IF;

        -- 获取当前店铺的factor（替换为实际店铺表名）
        SELECT factor INTO v_factor FROM shop_table WHERE id_shop = v_shop_counter;

        -- 计算新价格，用IFNULL处理NULL值，避免非空错误
        SET v_new_price = IFNULL(stdPrice, 0) + IFNULL(v_price_impact, 0) * IFNULL(v_factor, 1);

        -- 先检查目标表是否存在记录，不存在则插入，再更新
        IF NOT EXISTS (
            SELECT 1 FROM sockelsat_product_attribute_shop 
            WHERE id_product_attribute = v_attr_id AND id_shop = v_shop_counter
        ) THEN
            INSERT INTO sockelsat_product_attribute_shop (id_product_attribute, id_shop, price)
            VALUES (v_attr_id, v_shop_counter, v_new_price);
        ELSE
            UPDATE sockelsat_product_attribute_shop
            SET price = v_new_price
            WHERE id_product_attribute = v_attr_id AND id_shop = v_shop_counter;
        END IF;

        SET v_shop_counter = v_shop_counter + 1;
        IF v_shop_counter > 16 THEN LEAVE shop_loop; END IF;
    END LOOP shop_loop;
END LOOP attr_loop;
CLOSE attr_cursor;

2. 排查非空错误根源 #

手动执行以下SQL，确认NULL值来源：

• 检查标准价格是否为NULL：

SELECT stdPrice FROM sockelsat_product WHERE id_product = 512020060;

• 检查属性表的price_impact是否有NULL：

SELECT id_product_attribute, price_impact FROM sockelsat_product_attribute WHERE id_product = 512020060 AND price_impact IS NULL;

• 检查店铺factor是否有NULL：

SELECT id_shop, factor FROM shop_table WHERE id_shop BETWEEN 2 AND 16 AND id_shop NOT IN (13,15) AND factor IS NULL;

3. 验证数据关联正确性 #

检查目标表是否存在对应记录：

SELECT COUNT(*) FROM sockelsat_product_attribute_shop 
WHERE id_product_attribute IN (SELECT id_product_attribute FROM sockelsat_product_attribute WHERE id_product = 512020060) 
AND id_shop BETWEEN 2 AND 16 
AND id_shop NOT IN (13,15);

如果计数为0，说明需要先插入这些店铺的属性记录，否则UPDATE无法匹配数据。

验证建议 #

• 先针对测试产品ID（512020060）单独测试存储过程，不要直接跑全量数据
• 在循环中保留调试用的SELECT语句，输出中间变量，确认每一步的计算和关联是否正确
• 手动执行单条UPDATE语句测试：取一个已知的id_product_attribute和id_shop，手动计算价格后执行UPDATE，验证WHERE子句和价格计算的正确性

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