MySQL删除评论触发器：多列更新的准确性与性能咨询

触发器实现方案一 #

直接在UPDATE语句中通过CASE逻辑更新产品表的评论数和评分：

DELIMITER $$
CREATE TRIGGER after_review_delete
AFTER DELETE ON product_reviews
FOR EACH ROW
BEGIN 
    UPDATE product
    SET
             reviews_count = CASE WHEN OLD.status = 1 
                  THEN reviews_count-1 
                  ELSE reviews_count 
                  END,
      rating = CASE WHEN OLD.status = 1
            THEN CASE
                    WHEN reviews_count = 0 THEN 0 
                    ELSE (rating*(reviews_count+1)+OLD.rating)/reviews_count
               END
            ELSE rating
    WHERE id = OLD.pid;
END$$
DELIMITER ;

逻辑说明：判断被删除评论的status是否为1（活跃评论，待审核评论不影响评分和评论数），再更新product表的reviews_count和rating字段。

触发器实现方案二 #

先查询当前产品的评论数和评分存入变量，再基于变量更新字段：

DELIMITER $$

CREATE TRIGGER after_review_delete
AFTER DELETE ON product_reviews
FOR EACH ROW
BEGIN
    -- 声明本地变量存储当前值
    DECLARE current_reviews_count INT;
    DECLARE current_rating DECIMAL(10, 2);

    -- 获取当前评论数和评分
    SELECT reviews_count, rating INTO current_reviews_count, current_rating
    FROM product
    WHERE id = OLD.pid;

    -- 带条件逻辑更新产品表
    UPDATE product
    SET
        reviews_count = CASE
                           WHEN OLD.status = 1 THEN current_reviews_count - 1
                           ELSE current_reviews_count
                        END,
        rating = CASE
                    WHEN OLD.status = 1 THEN
                        CASE
                            WHEN current_reviews_count - 1 = 0 THEN 0 -- 无剩余评论
                            ELSE (current_rating * current_reviews_count - OLD.rating) / (current_reviews_count - 1)
                        END
                    ELSE current_rating
                 END
    WHERE id = OLD.pid;
END$$

DELIMITER ;

逻辑说明：先查询product表的当前reviews_count和rating存入变量，再基于变量更新字段，但会额外执行一次查询，可能影响性能。

核心疑问 #

1. 第一个触发器的UPDATE语句中，计算rating时使用的reviews_count是更新前的初始值还是更新后的值？
2. 该计算是否准确？
3. 第一个方案是否既准确又高效，能否安全替代第二个方案？

使用环境：MySQL 8.0.39-0 ubuntu0.24.04.1（Linux x86_64）

解答 #

1. reviews_count的取值 #

在MySQL的UPDATE语句中，所有字段的计算都基于更新前的初始值。也就是说，第一个触发器里计算rating时用到的reviews_count是更新前的原始值，不会受到同一UPDATE语句中reviews_count = reviews_count -1这条赋值的影响。

2. 计算准确性验证 #

第一个触发器的rating计算逻辑是错误的：
原计算式：(rating*(reviews_count+1)+OLD.rating)/reviews_count
而正确的评分更新逻辑应该是：删除前总评分（rating * reviews_count）减去被删除评论的评分，再除以删除后的评论数（reviews_count -1），即(rating*reviews_count - OLD.rating)/(reviews_count -1)。

举个实际例子验证：

• 删除前：reviews_count=3，rating=4（总评分12），被删除评论的rating=5
• 正确结果：(12-5)/(3-1)=3.5
• 第一个触发器计算结果：(4*(3+1)+5)/3=7，偏差极大

3. 能否替代第二个方案 #

第一个方案不能安全替代第二个方案，因为其评分计算逻辑完全错误，会导致产品评分严重失真。

如果想兼顾准确性和效率，可以修正第一个方案的计算逻辑，利用UPDATE语句基于初始值计算的特性，不需要额外查询：

DELIMITER $$
CREATE TRIGGER after_review_delete
AFTER DELETE ON product_reviews
FOR EACH ROW
BEGIN 
    UPDATE product
    SET
        reviews_count = CASE WHEN OLD.status = 1 
                             THEN reviews_count - 1 
                             ELSE reviews_count 
                        END,
        rating = CASE WHEN OLD.status = 1
                      THEN CASE
                              WHEN reviews_count - 1 = 0 THEN 0 
                              ELSE (rating * reviews_count - OLD.rating) / (reviews_count - 1)
                           END
                      ELSE rating
                 END
    WHERE id = OLD.pid;
END$$
DELIMITER ;

这个修正后的版本既保留了单UPDATE语句的高效性，又保证了计算逻辑的准确性，可以安全替代第二个方案。

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