mysql父子节点查询方法

创建 table1 表结构
CREATE TABLE table1(id int, name varchar(10), parent_id int);

插入模拟数据
INSERT table1 VALUES
(1, 'Home', 0),
(2, 'About', 1),
(3, 'Contact', 1),
(4, 'Legal', 2),
(5, 'Privacy', 4),
(6, 'Products', 1),
(7, 'Support', 2);

查询id为5的所有的父节点
SELECT ID.level, DATA.* FROM
(
    SELECT
        @id as _id, 
        ( SELECT @id := parent_id FROM table1 WHERE id = @id) as _pid,
        @l := @l+1 as level
    FROM table1, (SELECT @id := 5, @l := 0 ) b 
    WHERE @id > 0
) ID, table1 DATA
WHERE ID._id = DATA.id
ORDER BY level;

查询id为2的所有的子节点
SELECT ID.level, DATA.* FROM
(
    SELECT
        @ids as _ids,
        ( SELECT @ids := GROUP_CONCAT(id) FROM table1 WHERE FIND_IN_SET(parent_id, @ids)) as cids,
        @l := @l+1 as level
    FROM table1, (SELECT @ids :='2', @l := 0 ) b
    WHERE @ids IS NOT NULL
) ID, table1 DATA
WHERE FIND_IN_SET(DATA.id, ID._ids)
ORDER BY level, id

语法说明
@id         定义变量id
@id := 1    给变量赋值