MySQL如何按car_id计算平均评分并生成车型评分统计表

原有代码错误梳理 #

• 自定义函数逻辑错误：一是AVG()计算的字段填成了car_id，应该填评论表中存储用户评分的字段（比如stars）；二是没有将查询结果赋值给返回变量，也缺少RETURN返回语句，导致返回类型不匹配。
• 建表语法完全不合法：MySQL普通表的字段定义不支持直接调用自定义函数关联其他表的数据，你写的getAverage(car_id) AS average_stars行不符合语法规范；另外外键关联的actor_id字段在你当前表中根本不存在，属于冗余错误内容，直接删除即可。

方案1：修复原有逻辑实现持久化评分表 #

第一步：修复自定义函数 #

DELIMITER $$
CREATE FUNCTION getAverage(car_id_input INT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE avg_score INT;
    -- 下方`stars`替换为你评论表中实际存储评分的字段名
    SELECT IFNULL(AVG(`stars`), 0) INTO avg_score
    FROM `car_user_reviews`
    WHERE car_id = car_id_input;
    RETURN avg_score;
END$$
DELIMITER ;

第二步：创建评分表并初始化数据 #

CREATE TABLE `car_overall_stars` (
    `car_model_id` INT NOT NULL, 
    `avg_stars` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`car_model_id`),
    -- 外键关联你的车型主表，替换`car_models`和`id`为你实际的表名和主键字段名
    FOREIGN KEY (`car_model_id`) REFERENCES `car_models` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- 全量初始化所有车型的平均分
INSERT INTO car_overall_stars (car_model_id, avg_stars)
SELECT car_id, getAverage(car_id)
FROM car_user_reviews
GROUP BY car_id;

第三步：配置触发器自动更新评分（可选） #

如果需要评论表数据变动后自动同步平均分，可以给car_user_reviews配置增删改触发器，示例新增触发器如下：

DELIMITER $$
CREATE TRIGGER after_review_insert
AFTER INSERT ON car_user_reviews
FOR EACH ROW
BEGIN
    UPDATE car_overall_stars 
    SET avg_stars = getAverage(NEW.car_id)
    WHERE car_model_id = NEW.car_id;
    -- 新增车型无评分记录时自动插入
    IF ROW_COUNT() = 0 THEN
        INSERT INTO car_overall_stars (car_model_id, avg_stars)
        VALUES (NEW.car_id, getAverage(NEW.car_id));
    END IF;
END$$
DELIMITER ;

更新、删除场景的触发器逻辑和上述示例一致，对应修改触发时机即可。

方案2：更轻量的实现（推荐） #

如果你不需要持久化存储平均分，只是需要查询时获取统计结果，直接创建视图即可，无需维护冗余数据和触发器，性能可以满足绝大多数场景：

CREATE VIEW car_overall_stars AS
SELECT 
    car_id AS car_model_id,
    IFNULL(AVG(stars), 0) AS avg_stars
FROM car_user_reviews
GROUP BY car_id;

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