在mysql中获取包含所有父项的树中任意节点的父项/子项关系的完整树

CREATE TABLE tree (
  id INT PRIMARY KEY,
  parent_id INT
);

INSERT INTO tree (id, parent_id) VALUES
(1, NULL),
(2, 1),
(3, 1),
(4, 2),
(5, 2),
(6, 3),
(7, 3),
(8, 4);

DELIMITER //

CREATE PROCEDURE get_full_tree(IN node_id INT)
BEGIN
  CREATE TEMPORARY TABLE IF NOT EXISTS temp_tree (
    id INT PRIMARY KEY,
    parent_id INT
  );
  
  INSERT INTO temp_tree
  SELECT t.id, t.parent_id
  FROM tree t
  WHERE t.id = node_id
  
  UNION ALL
  
  SELECT t.id, t.parent_id
  FROM tree t
  INNER JOIN temp_tree tt ON t.parent_id = tt.id
  WHERE t.id <> node_id;
  
  SELECT *
  FROM temp_tree;
  
  DROP TEMPORARY TABLE IF EXISTS temp_tree;
END//

DELIMITER ;

CALL get_full_tree(4);