mysql如何实现递归等级查询

在oracle中
select max(level) into max_level
from zj_template_option t
where t.enable = '1'
and t.template_id = '5'
--and level >='3'
and t.ispfx = '1'
start with t.pid = '0'
connect by prior t.id = t.pid
order by template_id;
把它改成mysql 如何实现,及查询的结果为
id pid level
1 0 1
2 1 2
......

大神能给个这样的函数吗