在MySQL中如何统计和更新树形结构各节点的业务数量？

DELIMITER //

CREATE PROCEDURE update_region_num(IN p_region_id INT)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE current_id INT;
  DECLARE parent_id INT;
  DECLARE current_num INT;

  DECLARE cur CURSOR FOR SELECT id, parentId, num FROM regions WHERE id = p_region_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO current_id, parent_id, current_num;
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- 更新父节点的num值
    UPDATE regions SET num = (SELECT SUM(num) FROM regions WHERE parentId = parent_id) WHERE id = parent_id;

    -- 递归向上更新
    IF parent_id IS NOT NULL THEN
        CALL update_region_num(parent_id);
    END IF;
  END LOOP;
  CLOSE cur;
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER trg_region_num_update
AFTER UPDATE ON regions
FOR EACH ROW
BEGIN
  IF OLD.num != NEW.num THEN
    CALL update_region_num(NEW.id);
  END IF;
END //

DELIMITER ;

UPDATE regions SET num = 1234 WHERE id = 123;  -- 假设id 123是一个县