MySQL - how to query a tree?

Say I have the following table:

TABLE: category
 - category_id (PK)
 - parent_id (FK)
 - name

Given a value for category_id, how do I re开发者_如何学编程turn the given category_id and all its descendants?

If you want a single level, you'd do a SELECT on the condition category_id = id OR parent_id = id - but with MySQL, you cannot get a complete tree with a single query.

You can write a stored procedure to go through all of the intermediate results and pick up sub-children, but that really isn't very neat.

Instead, you can redesign your table. On the MySQL developer site, there is a nice article about how you can store hierarchical data in a table, and provides a design which is much more flexible than simply using a parent_id.