MySQL中如何为courses表新增自动统计选课人数的计算列？

首先明确：无法通过默认值或普通表达式列实现需求。因为默认值只能基于当前行的字段或常量赋值，没法跨表完成聚合统计；而普通的表达式生成列（比如MySQL的虚拟/存储列）也不允许引用其他表的数据，所以这两种方式都不可行。

下面是三种可行的实现方式，你可以根据业务场景选择：

1. 用触发器同步统计值 #

在current_enrollments表上创建INSERT、DELETE、UPDATE触发器，每当选课数据新增、删除或课程ID变更时，自动更新courses表中对应课程的统计数。

操作步骤（以MySQL为例）： #

首先给courses表新增统计列：

ALTER TABLE courses ADD COLUMN total_current_enrollments INT DEFAULT 0;

然后创建三个触发器覆盖所有数据变更场景：

新增选课时更新统计

DELIMITER //
CREATE TRIGGER update_enroll_count_after_insert
AFTER INSERT ON current_enrollments
FOR EACH ROW
BEGIN
    UPDATE courses
    SET total_current_enrollments = total_current_enrollments + 1
    WHERE id = NEW.course_id;
END //
DELIMITER ;

删除选课时更新统计

DELIMITER //
CREATE TRIGGER update_enroll_count_after_delete
AFTER DELETE ON current_enrollments
FOR EACH ROW
BEGIN
    UPDATE courses
    SET total_current_enrollments = total_current_enrollments - 1
    WHERE id = OLD.course_id;
END //
DELIMITER ;

变更选课的课程ID时同步统计

DELIMITER //
CREATE TRIGGER update_enroll_count_after_update
AFTER UPDATE ON current_enrollments
FOR EACH ROW
BEGIN
    IF OLD.course_id != NEW.course_id THEN
        -- 给原课程减少统计数
        UPDATE courses
        SET total_current_enrollments = total_current_enrollments - 1
        WHERE id = OLD.course_id;
        -- 给新课程增加统计数
        UPDATE courses
        SET total_current_enrollments = total_current_enrollments + 1
        WHERE id = NEW.course_id;
    END IF;
END //
DELIMITER ;

适用场景：需要频繁查询统计值，且选课数据变更不频繁的场景。优点是查询速度快，缺点是需要维护触发器，数据变更时会有额外性能开销。

2. 创建包含实时统计的视图 #

如果不需要把统计值存储在courses表中，仅查询时需要实时选课人数，可以直接创建视图，关联courses表和选课统计结果。

示例代码： #

CREATE VIEW courses_with_enrollments AS
SELECT 
    c.id,
    c.title,
    c.number_of_credits,
    c.course_code,
    c.lecturer_id,
    -- 用COALESCE确保无选课的课程显示0而非NULL
    COALESCE(ce.enroll_count, 0) AS total_current_enrollments
FROM courses c
LEFT JOIN (
    SELECT course_id, COUNT(student_id) AS enroll_count
    FROM current_enrollments
    GROUP BY course_id
) ce ON c.id = ce.course_id;

之后查询时直接使用该视图即可获取实时数据：

SELECT * FROM courses_with_enrollments;

适用场景：需要实时准确的统计数据，且查询频率不高的场景。优点是无需维护额外数据，数据永远最新；缺点是每次查询都要执行聚合计算，速度较慢。

3. 使用物化视图（部分数据库支持） #

如果既要快速查询，又能保证数据相对新鲜，可以用物化视图（如PostgreSQL、Oracle支持），它会存储统计结果，你可以定期刷新数据。

PostgreSQL 示例： #

先创建物化视图存储统计结果：

CREATE MATERIALIZED VIEW course_enroll_counts AS
SELECT course_id, COUNT(student_id) AS total_current_enrollments
FROM current_enrollments
GROUP BY course_id;

需要更新数据时手动刷新：

REFRESH MATERIALIZED VIEW course_enroll_counts;

若需自动刷新，可配合触发器或定时任务实现。查询时可关联物化视图与courses表，或直接查询物化视图。

适用场景：选课数据变更不频繁，需要快速查询统计值的场景。优点是查询速度快；缺点是数据非实时，需定期刷新。

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