mysql根据子id查询所有的父节点信息 [并可根据父id查询子节点信息]

SQL如下（示例）：

创建表格

CREATE TABLE `treetype` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT NULL,
  `fid` INT DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

数据

INSERT  INTO `treetype`(`id`,`name`,`fid`) 
VALUES (1,'电脑',0),
	   (2,'华硕',1),
       (3,'联想',1),
       (4,'戴尔',1),
       (5,'手机',0),
       (6,'苹果',5),
       (7,'华为',5),
       (8,'小米',5),
       (9,'华硕耳机',2),
       (10,'华硕耳机X系列',9),
       (11,'华为耳机',7),
       (12,'华为蓝牙耳机',11);

根据父id查询所有的子id

SELECT id,`name` FROM (
	SELECT 
		t1.id,t1.name,
		IF(FIND_IN_SET(fid, @pid) > 0,
    	@pid := CONCAT(@pid, ',', id), 0) AS childId
    FROM 
    	(
    		SELECT 
    			id,fid,`name` 
    	 	FROM treetype t 
    	 	ORDER BY fid, id
   	 	) t1,
	    (SELECT @pid := 5 ) t2) t3 
	WHERE childId != 0

根据子id查询所有的父id

SELECT T2.id,T2.name,T2.fid
FROM ( 
        SELECT 
                @r AS _id, 
                (SELECT @r := fid FROM treetype WHERE id = _id) AS parent_id, 
                @l := @l + 1 AS lvl 
        FROM 
                (SELECT @r := 11, @l := 0) vars,
                treetype h 
        WHERE @r <> 0) T1 
JOIN treetype T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC