MySQL recursive query, infinite level of the subordinate menu

mysql recursive query,MySQL from the subclass ID query all the parent class (do unlimited classification often used)

Because MySQL does not support recursive query syntax similar to Oracle with ... connect
It was thought that a similar query would either use a stored procedure or only write a recursive query with a program.

Now I find out that a SQL statement can be done.

Let's look at the structure of the data table as follows:

ID Name parent_id

---------------------------
1 Home 0
2 about 1
3 Contact 1
4 Legal 2
5 Privacy 4
6 Products 1
7 Support 1
I want the request is based on a classification ID (this classification ID may be a subcategory), get all the parent category, the following is the corresponding SQL:

SELECT T2.id, T2.name
From (
SELECT
@r as _id,
(SELECT @r: = parent_id from table1 WHERE id = _id) ASPARENT_ID,
@l: = @l + 1 as lvl
From
(SELECT @r: = 5, @l: = 0) VARs,
Table1 h
WHERE @r<> 0) T1
JOIN table1 T2
On t1._id = T2.id
ORDER by T1.lvl DESC;

MySQL recursive query, infinite level of the subordinate menu