How to select recursively all parents in MySQL table?

Question

How to select recursively all parents in MySQL table ?

Structure is like this:

id   name      parent_id
-----------------------
1    namelvl1  null
2    namelvl2  1
3    namelvl3  2

and so on....

How to get list of all parents for id=3 ?

Answer 1

1. Inefficient Solution

The following solution works, but it's terrible in terms of efficiency. It always reads all the rows of the table (even if you just need three rows) and then excludes the ones you don't need. It's adapted from this article:

SELECT  @r AS _id,
         (
         SELECT  @r := parent_id
         FROM    my_table
         WHERE   id = _id
         ) AS parent,
         @l := @l + 1 AS lvl
 FROM    (
         SELECT  @r := 3, -- starting node = 3
                 @l := 0,
                 @cl := 0
         ) vars,
         my_table h
WHERE    @r <> 0

What can I say... it works. The thing is it just does a Full Table Scan on the table. If the table is huge this can be prohibitively expensive.

2. Standard Hierarchical Queries

Starting on version 8.0 MySQL (not released as stable yet) formally supports hierarchical queries according to the SQL standard.

If you can upgrade to version 8.0 you can run normal hierarchical queries.

3. Use MariaDB

Now, if you can use MariaDB instead (originally a clone of MySQL), hierarchical queries are already available using standard SQL starting on version 10.2.2.