MySQL如何使用生成表达式实现跨表求和并自动同步总分数据

能否通过生成列实现？ #

MySQL 不支持通过生成列实现该需求
MySQL的生成列（包括VIRTUAL虚拟生成列和STORED存储生成列）的表达式有严格限制：

• 不允许使用子查询
• 不允许引用其他表的字段
• 仅能引用当前表当前行的字段、以及确定性内置函数
  你编写的生成列使用了跨表SELECT子查询，本身就不符合生成列的语法要求，所以执行报错是必然结果。

可行实现方案 #

方案1：使用触发器（推荐，可实现实时自动更新） #

这是最贴合需求的方案，通过在response表上配置增删改三类触发器，自动同步更新total_mark表数据。

步骤1：创建正常的total_mark表

注意添加quiz_id + student_id的唯一键，避免同一个测验同一个学生生成多条记录：

CREATE TABLE total_mark (
    id INT PRIMARY KEY AUTO_INCREMENT,
    quiz_id INT NOT NULL,
    student_id INT NOT NULL,
    total_mark DECIMAL(5,2) DEFAULT 0,
    UNIQUE KEY `uk_quiz_student` (quiz_id, student_id)
);

步骤2：创建INSERT触发器

response表新增记录时自动更新总分：

DELIMITER //
CREATE TRIGGER after_response_insert
AFTER INSERT ON response
FOR EACH ROW
BEGIN
    INSERT INTO total_mark (quiz_id, student_id, total_mark)
    VALUES (NEW.quiz_id, NEW.student_id, NEW.mark)
    ON DUPLICATE KEY UPDATE total_mark = total_mark + NEW.mark;
END //
DELIMITER ;

步骤3：创建UPDATE触发器

response表修改记录时自动更新新旧两个维度的总分：

DELIMITER //
CREATE TRIGGER after_response_update
AFTER UPDATE ON response
FOR EACH ROW
BEGIN
    -- 扣减旧的测验+学生组合的总分
    UPDATE total_mark 
    SET total_mark = total_mark - OLD.mark
    WHERE quiz_id = OLD.quiz_id AND student_id = OLD.student_id;
    
    -- 新增/更新新的测验+学生组合的总分
    INSERT INTO total_mark (quiz_id, student_id, total_mark)
    VALUES (NEW.quiz_id, NEW.student_id, NEW.mark)
    ON DUPLICATE KEY UPDATE total_mark = total_mark + NEW.mark;
END //
DELIMITER ;

步骤4：创建DELETE触发器

response表删除记录时自动扣减对应总分：

DELIMITER //
CREATE TRIGGER after_response_delete
AFTER DELETE ON response
FOR EACH ROW
BEGIN
    UPDATE total_mark 
    SET total_mark = total_mark - OLD.mark
    WHERE quiz_id = OLD.quiz_id AND student_id = OLD.student_id;
    
    -- 可选：如果总分归零后要删除对应记录，可取消注释下方代码
    -- DELETE FROM total_mark 
    -- WHERE quiz_id = OLD.quiz_id AND student_id = OLD.student_id AND total_mark = 0;
END //
DELIMITER ;

方案2：使用视图（无冗余存储，数据自动实时） #

如果不需要物理存储总分数据，只是查询时使用，直接创建聚合视图即可，无需维护触发器，数据永远和response表一致：

CREATE VIEW v_total_mark AS
SELECT 
    quiz_id,
    student_id,
    SUM(mark) AS total_mark
FROM response
GROUP BY quiz_id, student_id;

使用时直接查询视图即可：SELECT * FROM v_total_mark WHERE quiz_id = 1 AND student_id = 2，按给出的示例数据，查询结果的total_mark就是5。

方案3：事件调度器（非实时，按需选择） #

如果对实时性要求不高，也可以开启MySQL事件调度器，定时批量更新total_mark表，适合离线统计场景。

原有代码的其他问题 #

你写的INSERT语句中，字段名用了单引号包裹，这是语法错误：字段名不需要加引号，如需转义请用反引号`，正确写法参考：

INSERT INTO total_mark (`quiz_id`, `student_id`) VALUES (1, 2);

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