新建测试数据表

create table menu(id int auto_increment,name VARCHAR(255),parent_id int,PRIMARY KEY(id)
) ENGINE=INNODB auto_increment=1 default charset = "utf8";

插入测试数据

insert into menu(id, name, parent_id) values(1, 'Home', 0);
insert into menu(id, name, parent_id) values(2, 'About', 1);
insert into menu(id, name, parent_id) values(3, 'Contact', 1);
insert into menu(id, name, parent_id) values(4, 'Legal', 2);
insert into menu(id, name, parent_id) values(5, 'Privacy', 4);
insert into menu(id, name, parent_id) values(6, 'Products', 1);
insert into menu(id, name, parent_id) values(7, 'Support', 1);

查询id为5的所有上级

SELECT T2.id, T2.name 
FROM ( SELECT @r AS _id, (SELECT @r := parent_id FROM menu WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := 5, @l := 0) vars,  #查询id为5的所有上级menu h WHERE @r <> 0) T1 
JOIN menu T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC;

查询所有父到子之间级数和父到子路径

SELECT id AS ID,parent_id AS 父ID ,levels AS 父到子之间级数, paths AS 父到子路径 FROM (SELECT id,parent_id,@le:= IF (parent_id = 0 ,0,  IF( LOCATE( CONCAT('|',parent_id,':'),@pathlevel)   > 0  ,      SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',parent_id,':'),-1),'|',1) +1,@le+1) ) levels, @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel, @pathnodes:= IF( parent_id =0,',0', CONCAT_WS(',',IF( LOCATE( CONCAT('|',parent_id,':'),@pathall) > 0  , SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',parent_id,':'),-1),'|',1),@pathnodes ) ,parent_id  ) )paths,@pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall FROM  menu, (SELECT @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vvORDER BY  parent_id,id) src
ORDER BY id