基于MySQL触发器实现Gantt任务树层级完成度自动计算

前提假设 #

假设你的任务表名为gantt_tasks，核心字段包括：

• id：任务唯一ID
• parent_id：父任务ID（顶层任务为NULL）
• project_id：项目ID（确保只更新同项目任务）
• duration：任务时长
• progress：任务进度（0-1之间的数值）

触发器实现代码 #

DELIMITER //

CREATE TRIGGER update_parent_progress AFTER UPDATE ON gantt_tasks
FOR EACH ROW
BEGIN
    -- 仅当progress字段被修改时执行逻辑
    IF OLD.progress != NEW.progress THEN
        DECLARE current_parent INT;
        DECLARE current_project INT;
        DECLARE total_weighted_progress DECIMAL(10,4);
        DECLARE parent_duration DECIMAL(10,4);
        
        -- 初始化当前父任务ID和所属项目ID
        SET current_parent = NEW.parent_id;
        SET current_project = NEW.project_id;
        
        -- 循环向上遍历父任务，直到无父任务
        WHILE current_parent IS NOT NULL DO
            -- 计算当前父任务所有直接子任务的duration*progress总和
            SELECT SUM(t.duration * t.progress) INTO total_weighted_progress
            FROM gantt_tasks t
            WHERE t.parent_id = current_parent AND t.project_id = current_project;
            
            -- 获取当前父任务的duration
            SELECT duration INTO parent_duration
            FROM gantt_tasks
            WHERE id = current_parent AND project_id = current_project;
            
            -- 更新父任务的progress，处理duration为0的情况避免除以0
            IF parent_duration > 0 THEN
                UPDATE gantt_tasks
                SET progress = total_weighted_progress / parent_duration
                WHERE id = current_parent AND project_id = current_project;
            ELSE
                -- 父任务时长为0时，可根据业务需求设置默认值，比如0
                UPDATE gantt_tasks
                SET progress = 0
                WHERE id = current_parent AND project_id = current_project;
            END IF;
            
            -- 获取上一级父任务ID，继续循环
            SELECT parent_id INTO current_parent
            FROM gantt_tasks
            WHERE id = current_parent AND project_id = current_project;
        END WHILE;
    END IF;
END //

DELIMITER ;

逻辑说明 #

1. 触发条件：仅当任务的progress字段被修改时，才执行后续递归更新逻辑，避免不必要的计算。
2. 递归遍历：通过WHILE循环向上遍历父任务，每次更新当前父任务后，获取其上级父任务ID，直到顶层任务（parent_id为NULL）。
3. 进度计算规则：父任务的进度 = 所有直接子任务的duration*progress总和 / 父任务自身的duration，确保进度计算符合加权平均的逻辑。
4. 边界处理：增加了父任务duration为0的判断，避免出现除以0的错误，可根据实际业务调整默认值。
5. 项目隔离：所有查询和更新都加上project_id条件，确保只更新同一项目内的任务。

测试验证 #

当你执行UPDATE gantt_tasks SET progress = 0.6 WHERE id = 18;时，触发器会自动：

1. 找到ID18的父任务ID15
2. 计算ID15下所有子任务的duration*progress总和，除以ID15的duration得到新的进度并更新
3. 继续向上查找ID15的父任务，重复计算更新逻辑，直到顶层任务

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