Can MySQL Query a Tree Structure Table to Any Depth in One Query?

Can a Tree Structure Table in MySQL Be Queried to Any Depth in a Single Attempt?

The question arises as to whether it's feasible to traverse a tree-structured table in MySQL and retrieve all descendants or ancestors, regardless of depth, in a single query.

Answer

Yes, it's possible. A Modified Preorder Tree Traversal technique, as outlined in Joe Celko's "Trees and Hierarchies in SQL for Smarties," allows for this. Here's a snippet from the reference material:

SELECT *
FROM tree
ORDER BY FIELD(`left`, @field_list_all, @field_list_current, @field_list_children)
LIMIT 1

This query utilizes a "field list" stored as a variable (@field_list_all) to track the traversal and identify nodes to be processed efficiently. Refer to the specified documentation for a comprehensive example.

The above is the detailed content of Can MySQL Query a Tree Structure Table to Any Depth in One Query?. For more information, please follow other related articles on the PHP Chinese website!