MySQL存储过程无法更新/插入表数据问题排查请求

核心错误分析 #

• 游标未配置结束处理程序：原代码声明了done变量但未绑定游标结束事件，当游标遍历完所有数据后，FETCH会触发NOT FOUND错误，直接抛出"No data - zero rows fetched..."提示，且循环无法正常退出。
• 计数器未初始化：counter变量默认值为NULL，执行counter = counter +1后结果仍为NULL，无法统计实际处理的行数。
• 存在性检查逻辑异常：通过SELECT product_id INTO var_isset_product_id检查记录是否存在时，若目标表无对应记录，该语句会抛出异常，直接中断当前循环的后续逻辑。
• INSERT语句缺失关键字段：插入product_summary时未指定product_id，导致新记录无法关联对应产品，且若product_id是主键/唯一键，会触发约束错误。
• 语法错误：存储过程末尾的ND //应为END //，属于拼写错误。

修复后的存储过程代码 #

DELIMITER //
CREATE PROCEDURE UpdateProduct()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE counter INT DEFAULT 0; -- 初始化计数器
    DECLARE var_product_id INT;
    DECLARE var_last_day INT;

    DECLARE ps_rating FLOAT;
    DECLARE ps_comment INT;
    DECLARE ps_price_discount INT;
    DECLARE ps_price_average INT;
    DECLARE ps_quantity INT;
    DECLARE ps_lost_sale INT;
    DECLARE ps_order30 INT;
    DECLARE ps_revenue30 INT;
    DECLARE ps_order30pre INT;
    DECLARE ps_revenue30pre INT;

    -- 声明游标结束处理程序，遍历完数据后自动设置done为TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    DECLARE var_cur CURSOR FOR 
        SELECT DISTINCT id FROM product p ORDER BY id LIMIT 100000;

    OPEN var_cur;

    read_loop: LOOP
        FETCH var_cur INTO var_product_id;
        IF done THEN 
            LEAVE read_loop; 
        END IF;

        -- 用COUNT安全检查product_summary中是否存在记录，避免查询无结果时抛出异常
        DECLARE var_isset INT DEFAULT 0;
        SELECT COUNT(*) INTO var_isset 
        FROM product_summary 
        WHERE product_id = var_product_id;

        -- 获取最后一条有效日志的时间戳
        SELECT MAX(create_at)
        INTO var_last_day
        FROM product_log
        WHERE product_id = var_product_id
          AND create_at <= UNIX_TIMESTAMP(CURDATE() - INTERVAL 1 DAY);

        IF var_last_day IS NOT NULL THEN
            -- 合并单字段查询为多字段查询，减少数据库IO次数
            SELECT 
                COALESCE(rating, 0),
                COALESCE(comment, 0),
                COALESCE(price_discount, 0),
                COALESCE(quantity, 0)
            INTO ps_rating, ps_comment, ps_price_discount, ps_quantity
            FROM product_log
            WHERE product_id = var_product_id
              AND create_at = var_last_day;

            -- 计算30天平均折扣价
            SELECT COALESCE(ROUND(AVG(price_discount)), 0)
            INTO ps_price_average
            FROM product_log
            WHERE product_id = var_product_id
              AND create_at BETWEEN UNIX_TIMESTAMP(CURDATE() - INTERVAL 31 DAY) 
                                AND UNIX_TIMESTAMP(CURDATE() - INTERVAL 1 DAY);

            -- 修正lost_sale查询：若product表无create_at字段，需删除下方注释的条件
            SELECT COALESCE(lost_sale, 0)
            INTO ps_lost_sale
            FROM product
            WHERE id = var_product_id
              -- AND create_at = var_last_day;

            -- 计算30天订单数与营收
            SELECT 
                COALESCE(ROUND(SUM(order_count)), 0),
                COALESCE(ROUND(SUM(order_count * price_discount)), 0)
            INTO ps_order30, ps_revenue30
            FROM product_log
            WHERE product_id = var_product_id
              AND create_at BETWEEN UNIX_TIMESTAMP(CURDATE() - INTERVAL 31 DAY) 
                                AND UNIX_TIMESTAMP(CURDATE() - INTERVAL 1 DAY);

            -- 计算前30天订单数与营收
            SELECT 
                COALESCE(ROUND(SUM(order_count)), 0),
                COALESCE(ROUND(SUM(order_count * price_discount)), 0)
            INTO ps_order30pre, ps_revenue30pre
            FROM product_log
            WHERE product_id = var_product_id
              AND create_at BETWEEN UNIX_TIMESTAMP(CURDATE() - INTERVAL 62 DAY) 
                                AND UNIX_TIMESTAMP(CURDATE() - INTERVAL 32 DAY);

            -- 使用INSERT ... ON DUPLICATE KEY UPDATE替代IF-ELSE，保证原子性且更高效
            -- 需确保product_summary表的product_id字段为主键或唯一键
            INSERT INTO product_summary (
                product_id, rating, comment, price_discount,
                price_average, quantity, lost_sale,
                order30, revenue30, order30pre, revenue30pre
            ) VALUES (
                var_product_id, ps_rating, ps_comment, ps_price_discount,
                ps_price_average, ps_quantity, ps_lost_sale,
                ps_order30, ps_revenue30, ps_order30pre, ps_revenue30pre
            ) ON DUPLICATE KEY UPDATE
                rating = ps_rating,
                comment = ps_comment,
                price_discount = ps_price_discount,
                price_average = ps_price_average,
                quantity = ps_quantity,
                lost_sale = ps_lost_sale,
                order30 = ps_order30,
                revenue30 = ps_revenue30,
                order30pre = ps_order30pre,
                revenue30pre = ps_revenue30pre;

            SET counter = counter + 1;
        END IF;
    END LOOP read_loop;

    CLOSE var_cur;
    SELECT counter AS processed_rows; -- 明确输出处理的产品行数

END // -- 修正拼写错误
DELIMITER ;

额外优化说明 #

• 用INSERT ... ON DUPLICATE KEY UPDATE替代原分支判断，既简化代码，又保证更新/插入操作的原子性。
• 合并多个单字段查询为多字段查询，减少数据库交互次数，提升存储过程执行效率。
• 对lost_sale查询增加注释提示：若product表没有create_at字段，需删除对应的匹配条件，否则会导致无结果时ps_lost_sale为NULL。

内容的提问来源于stack exchange，提问作者Александр Коваленко