编辑
@leftclickben提到的解决方案也是有效的。我们也可以使用相同的存储过程。CREATE PROCEDURE get_tree(IN id int)
BEGIN
DECLARE child_id int;
DECLARE prev_id int;
SET prev_id = id;
SET child_id=0;
SELECT col3 into child_id
FROM table1 WHERE col1=id ;
create TEMPORARY table IF NOT EXISTS temp_table as (select * from table1 where 1=0);
truncate table temp_table;
WHILE child_id <> 0 DO insert into temp_table select * from table1 WHERE col1=prev_id;
SET prev_id = child_id;
SET child_id=0;
SELECT col3 into child_id FROM TABLE1 WHERE col1=prev_id;
END WHILE;
select * from temp_table;
END //
我们使用临时表来存储输出的结果,而由于临时表是基于会话的,所以输出数据不正确将不会出现任何问题。