基于另一表数据计算平均值的计算列实现方案咨询

看起来你想要给影片信息表加上平均评分的计算列，不过得先说明：MySQL的**生成列（Computed Columns）**只能依赖当前表的字段，没办法直接引用其他表的数据，所以我们得换几种思路来实现，下面是适合你场景的几个实用方案：

方案1：查询时实时计算（最简单，无需修改表结构） #

如果只是需要在查询影片信息的时候展示平均评分，不需要把评分持久化存在表里面，直接用JOIN和聚合函数就能搞定。假设你的影片信息表叫movies，我补个常见的结构示例（你可以根据自己的实际表结构调整）：

CREATE TABLE `movies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  -- 其他你需要的字段比如导演、上映年份等
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

那查询所有影片及其平均评分的SQL可以这么写：

SELECT 
  m.id,
  m.name,
  -- 用COALESCE处理没有评分的影片，返回0而不是NULL，显示更友好
  COALESCE(AVG(r.rating), 0) AS average_rating
FROM movies m
LEFT JOIN movie_ratings r ON m.id = r.movie_id
GROUP BY m.id, m.name;

• 用LEFT JOIN能保证即使影片还没有任何用户评分，也会出现在结果里
• COALESCE函数专门用来把NULL（无评分时AVG的返回值）转换成0，避免界面显示空值

方案2：创建视图（复用查询逻辑，方便调用） #

如果你需要经常查看带平均评分的影片信息，不想每次都写上面的长SQL，可以创建一个视图，把这个查询逻辑封装起来：

CREATE VIEW movies_with_ratings AS
SELECT 
  m.id,
  m.name,
  COALESCE(AVG(r.rating), 0) AS average_rating
FROM movies m
LEFT JOIN movie_ratings r ON m.id = r.movie_id
GROUP BY m.id, m.name;

之后你只需要查询这个视图就行，和查普通表完全一样：

SELECT * FROM movies_with_ratings;

视图的好处是实时更新，每次查询都会用最新的评分数据计算平均，不需要你手动维护数据一致性。

方案3：持久化存储平均评分（性能优先，用触发器维护） #

如果你的网站访问量比较大，实时计算平均评分可能有性能压力，那可以在影片表中新增一个存储平均评分的字段，然后用触发器自动维护这个字段的值。

步骤1：修改影片表，新增平均评分字段 #

ALTER TABLE movies ADD COLUMN average_rating DECIMAL(3,1) DEFAULT 0;

这里用DECIMAL(3,1)是因为5分制的平均评分最多是5.0，这个类型足够存储，还能保留一位小数，显示更精准。

步骤2：创建触发器，自动维护平均评分 #

需要创建三个触发器：当movie_ratings表新增、更新、删除评分时，自动更新对应影片的平均评分。

新增评分时的触发器

DELIMITER //
CREATE TRIGGER update_rating_after_insert
AFTER INSERT ON movie_ratings
FOR EACH ROW
BEGIN
  UPDATE movies m
  SET m.average_rating = (
    SELECT COALESCE(AVG(r.rating), 0)
    FROM movie_ratings r
    WHERE r.movie_id = NEW.movie_id
  )
  WHERE m.id = NEW.movie_id;
END //
DELIMITER ;

更新评分时的触发器

DELIMITER //
CREATE TRIGGER update_rating_after_update
AFTER UPDATE ON movie_ratings
FOR EACH ROW
BEGIN
  UPDATE movies m
  SET m.average_rating = (
    SELECT COALESCE(AVG(r.rating), 0)
    FROM movie_ratings r
    WHERE r.movie_id = NEW.movie_id
  )
  WHERE m.id = NEW.movie_id;
END //
DELIMITER ;

删除评分时的触发器

DELIMITER //
CREATE TRIGGER update_rating_after_delete
AFTER DELETE ON movie_ratings
FOR EACH ROW
BEGIN
  UPDATE movies m
  SET m.average_rating = (
    SELECT COALESCE(AVG(r.rating), 0)
    FROM movie_ratings r
    WHERE r.movie_id = OLD.movie_id
  )
  WHERE m.id = OLD.movie_id;
END //
DELIMITER ;

这样以后每次用户新增、修改或删除评分，影片表的average_rating字段都会自动更新，查询的时候直接取这个字段就行，性能会比实时计算好很多。

选择建议 #

• 如果只是偶尔查看评分，或者网站流量小，用方案1最省事
• 如果需要频繁查询带评分的影片信息，用方案2的视图，逻辑清晰还不用维护数据
• 如果对查询性能要求高，或者需要把平均评分用于排序、筛选等业务逻辑，用方案3的触发器持久化存储

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