How to select all parents of a node in a hierarchical mysql table?

I have a MySQL table that represents data for a tree GUI component, here's the structure of my table:

treeTable ( 
  id INT NOT NULL PRIMARY KEY, 
  parentId INT, 
  name VARCHAR(255) 
);

parentId is a self-referencing foreign key.

Now I want to write a stored procedure which gets a node id and returns a result set that contains that node and all of its parents.

For example, suppose that my table has filled with this data:

1, null, 'root'
2, 1   , 'level_1'
3, 2   , 'level_2'

N开发者_运维知识库ow I want to get all parent nodes of node 3 (nodes 1 and 2) and return a result set that contains all tree records. Can anybody help me please?

---

Good question. In Oracle you would use something like CONNECT BY.

Since you are using MySQL, I would suggest you change your data structure to efficiently answer that query. Here are some ideas.

---

There was a similar discussion to this that might be helpful in solving this problem.

I think I might attack this problem by recursively retrieving the data until I'd reached the root node (parent was null). I might have been inclined to do this outside of the stored procedure initially (repeatedly calling the thing until the retrieved row had the null parent), but the "closure table" solution on the page I referenced here looks like a great solution.

---

Look here under "Retrieving a Single Path". But better use a nested set approach, it will be much easier to work with a tree. Also I recommend reading "Trees In The Database - Advanced data structures" presentation.

---

There's also materialized paths to think about. Pretty simple concept that's really database agnostic. Much easier to manage inserts etc as in contrast to nested sets, you don't have to know you're left/right nodes etc before you insert.

---

MySQL does not support table valued functions 18.2.1. Stored Routine Syntax (which is what you need to be able to return an arbitrary result set).

Without them you have three choices:

1. unroll the tree query to a fixed maximum depth and limit the permitted nesting in your hierarchy,
2. use a loop to write the data into a temporary table and introduce some convention to return the results to the caller. You will need to consider re-entrancy, or,
3. pre-calculate the results by including all ancestors of each component into a support table (as shown) and maintain it with triggers on the treeTable. This way, the stored procedure returns the rows in parentTable filtered appropriately. You will need to create a compound primary key and possibly indices for efficient access.

The third option has very small rows, will give good performance and avoids artificial limitations.

parentTable (
    id INT NOT NULL,
    parentId INT NOT NULL
); 

The suggestion to use a nested set approach may be appropriate in this application where the data is largely static. A rapidly changing dataset would start to impact on I/O performance as on average half of the rows in the table are updated for each insert or delete.