MySQL如何创建从另一表字段获取数据的派生属性

你可以根据业务场景选择以下两种方案实现需求： #

方案1：持久化存储overall_rating字段（读多写少场景推荐） #

该方案会把平均分真实存在Item表中，查询性能更高，需要通过触发器保证数据一致性。

步骤1：新增字段 #

先给Item表添加overall_rating字段，精度可以根据需求调整，以下示例保留2位小数：

ALTER TABLE Item ADD COLUMN overall_rating DECIMAL(3,2);

步骤2：初始化历史数据 #

给所有已存在的商品计算历史评论的平均分：

UPDATE Item i
SET overall_rating = COALESCE((
    SELECT AVG(rating) 
    FROM Review r 
    WHERE r.item_id = i.ID
), 0);

这里用COALESCE是为了把没有评论的商品的平均分默认设为0，不需要的话可以去掉。

步骤3：创建触发器自动更新 #

需要分别创建新增、修改、删除Review时的触发器，自动同步对应商品的平均分：

• 新增Review后触发更新：

DELIMITER //
CREATE TRIGGER update_rating_after_insert
AFTER INSERT ON Review
FOR EACH ROW
BEGIN
    UPDATE Item
    SET overall_rating = COALESCE((SELECT AVG(rating) FROM Review WHERE item_id = NEW.item_id), 0)
    WHERE ID = NEW.item_id;
END //
DELIMITER ;

• 修改Review后触发更新：

DELIMITER //
CREATE TRIGGER update_rating_after_update
AFTER UPDATE ON Review
FOR EACH ROW
BEGIN
    -- 处理rating修改或者关联商品修改的场景
    IF OLD.rating <> NEW.rating OR OLD.item_id <> NEW.item_id THEN
        -- 更新原关联商品的平均分
        UPDATE Item
        SET overall_rating = COALESCE((SELECT AVG(rating) FROM Review WHERE item_id = OLD.item_id), 0)
        WHERE ID = OLD.item_id;
        -- 关联商品变更时额外更新新商品的平均分
        IF OLD.item_id <> NEW.item_id THEN
            UPDATE Item
            SET overall_rating = COALESCE((SELECT AVG(rating) FROM Review WHERE item_id = NEW.item_id), 0)
            WHERE ID = NEW.item_id;
        END IF;
    END IF;
END //
DELIMITER ;

• 删除Review后触发更新：

DELIMITER //
CREATE TRIGGER update_rating_after_delete
AFTER DELETE ON Review
FOR EACH ROW
BEGIN
    UPDATE Item
    SET overall_rating = COALESCE((SELECT AVG(rating) FROM Review WHERE item_id = OLD.item_id), 0)
    WHERE ID = OLD.item_id;
END //
DELIMITER ;

方案2：动态计算平均分（写多读少场景推荐） #

不需要维护额外字段和触发器，查询时自动计算，用视图实现即可：

CREATE VIEW ItemWithOverallRating AS
SELECT 
    i.*,
    COALESCE(AVG(r.rating), 0) AS overall_rating
FROM Item i
LEFT JOIN Review r ON i.ID = r.item_id
GROUP BY i.ID;

后续直接查询SELECT * FROM ItemWithOverallRating就能拿到带平均分的商品数据，所有评论变更会自动同步到视图结果中。 #

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