mysql 父子节点,MySQL：获取父子结构的根节点

I have a table similar to this:

=================

| Id | ParentId |

=================

| 1 | 0 |

-----+-----------

| 2 | 1 |

-----+-----------

| 3 | 0 |

-----+-----------

| 4 | 3 |

-----+-----------

| 5 | 3 |

-----+-----------

| 6 | 0 |

-----+-----------

| 7 | 6 |

-----+-----------

| 8 | 7 |

-----------------

Given an Id, I need to know its root "node" Id. So,

Given 1, return 1

Given 2, return 1

Given 3, return 3

Given 4, return 3

Given 5, return 3

Given 6, return 6

Given 7, return 6

Given 8, return 7

There is no limit to the levels of the hierarchy. Is there a SQL that can do what I need?

解决方案

This is quite difficult to do in MySQL because it doesn't yet support recursive common table expressions.

I'd suggest instead using a nested sets model, or else storing the root node in the row and updating it as the structure changes.