如何在PostgreSQL中实现quizes表plays字段自动统计对应quizes_taken表的行数

嘿，这个需求用数据库触发器就能轻松搞定，不同数据库的实现语法有点不一样，我给你整理了MySQL和PostgreSQL这两个主流数据库的方案，你按需选用就行：

我们需要创建两个触发器，分别处理quizes_taken表的插入和更新操作（如果你的业务中不会修改quizes_taken里的quiz_id，那只需要插入触发器就够了）：

插入操作的触发器 #

当新记录插入到quizes_taken后，自动更新对应quiz_id的plays字段：

DELIMITER //
CREATE TRIGGER update_quizes_plays_after_insert
AFTER INSERT ON quizes_taken
FOR EACH ROW
BEGIN
    UPDATE quizes
    SET plays = (SELECT COUNT(*) FROM quizes_taken WHERE quiz_id = NEW.quiz_id)
    WHERE id = NEW.quiz_id;
END //
DELIMITER ;

更新操作的触发器（当quiz_id变更时触发） #

如果quizes_taken里的quiz_id被修改了，我们需要同时更新旧quiz_id和新quiz_id的plays数：

DELIMITER //
CREATE TRIGGER update_quizes_plays_after_update
AFTER UPDATE ON quizes_taken
FOR EACH ROW
BEGIN
    -- 先更新旧quiz_id的播放数
    UPDATE quizes
    SET plays = (SELECT COUNT(*) FROM quizes_taken WHERE quiz_id = OLD.quiz_id)
    WHERE id = OLD.quiz_id;
    
    -- 再更新新quiz_id的播放数
    UPDATE quizes
    SET plays = (SELECT COUNT(*) FROM quizes_taken WHERE quiz_id = NEW.quiz_id)
    WHERE id = NEW.quiz_id;
END //
DELIMITER ;

PostgreSQL的触发器需要先定义处理逻辑的函数，再把函数绑定到触发器上：

第一步：创建更新plays字段的函数 #

这个函数会处理插入和更新两种场景：

CREATE OR REPLACE FUNCTION update_quizes_plays()
RETURNS TRIGGER AS $$
BEGIN
    -- 更新新quiz_id的播放数
    UPDATE quizes
    SET plays = (SELECT COUNT(*) FROM quizes_taken WHERE quiz_id = NEW.quiz_id)
    WHERE id = NEW.quiz_id;
    
    -- 如果是更新操作，且quiz_id发生了变化，再更新旧quiz_id的播放数
    IF TG_OP = 'UPDATE' AND OLD.quiz_id != NEW.quiz_id THEN
        UPDATE quizes
        SET plays = (SELECT COUNT(*) FROM quizes_taken WHERE quiz_id = OLD.quiz_id)
        WHERE id = OLD.quiz_id;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

第二步：绑定触发器到quizes_taken表 #

分别为插入和更新操作创建触发器：

-- 插入时触发的触发器
CREATE TRIGGER trigger_quizes_taken_insert
AFTER INSERT ON quizes_taken
FOR EACH ROW
EXECUTE FUNCTION update_quizes_plays();

-- 更新时触发的触发器（仅当quiz_id变化时执行）
CREATE TRIGGER trigger_quizes_taken_update
AFTER UPDATE ON quizes_taken
FOR EACH ROW
WHEN (OLD.quiz_id IS DISTINCT FROM NEW.quiz_id)
EXECUTE FUNCTION update_quizes_plays();

如果你的业务对plays字段的实时性要求很高，且不想维护冗余数据，可以考虑创建一个视图来实时计算播放数，这样就不需要触发器了：

CREATE VIEW quizes_with_plays AS
SELECT q.id, q.quiz_name, COUNT(qt.session_id) AS plays
FROM quizes q
LEFT JOIN quizes_taken qt ON q.id = qt.quiz_id
GROUP BY q.id, q.quiz_name;

之后你直接查询这个视图就能获取每个quiz的最新播放数，不过如果数据量很大且查询频繁，触发器维护的冗余字段性能会更优，你可以根据自己的业务场景选择。

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