查询 id = 6 的所有父级

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 := 6, @l := 0
		) b
	WHERE @id > 0
) ID, table1 DATA
WHERE ID._id = DATA.id
ORDER BY level;

根据这个父级查询方法，很容易可以写出查所有子级的，下面的查询 id=3 的所有子级

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 := 3, @l := 0
		) b
	WHERE @ids IS NOT NULL
) id, table1 DATA
WHERE FIND_IN_SET(DATA.id, ID._ids)
ORDER BY level, id