mysql怎么返回树的深度,MYSQL和关闭表树的深度

How would I go about populating a closure table's depth/length column when inserting a new node to the tree?

The values in ancestor and descendant are IDs from another table that represent pages to be arranged in a tree structure.

Closure Table:

ancestor descendant depth

1 1 0

1 2 1

1 3 1

1 4 1

2 2 0

3 3 0

4 4 0

This will insert the ancestor and descendants properly but I'm not sure how to populate the depth column

Insert Query:

INSERT INTO closure_tree_path (ancestor, descendant)

SELECT ancestor, '{$node_id}' FROM closure_tree_path

WHERE descendant = '{$parent_id}'

UNION ALL SELECT '{$node_id}', '{$node_id}';

What's the best way to go about this? Thanks a bunch!

解决方案

Add depth+1 to the first SELECT.

INSERT INTO closure_tree_path (ancestor, descendant, depth)

SELECT ancestor, '{$node_id}', depth+1 FROM closure_tree_path

WHERE descendant = '{$parent_id}'

UNION ALL SELECT '{$node_id}', '{$node_id}', 0;