How to implement MySql multi-level menu query

Background

We often encounter this need at work (especially in traditional projects), which is tree-structured query (multi-level query). Common scenarios include: organizational structure (user department) query And multi-level menu query

For example, the menu is divided into three levels, first-level menu, second-level menu, and third-level menu. The user is required to query the menus at all levels according to the tree structure. As shown in the figure below

For fixed levels and a small number of levels, usually 3 levels, the requirement is very simple to implement. First query the smallest child level, and then query the upper level in sequence. , and finally assemble it and return it to the front end.

Then the question is, if the number of levels is very large, 10 levels, or the levels are simply uncertain, some are 3 levels, some are 5 levels, some are 8 levels, the levels are fixed and the number of levels is the same as before. Obviously the problem is more complicated than Let’s call it a tree table:

CREATE TABLE tree (
	id int not null auto_increment,
	name varchar(50) not null comment '名称',
	parent_id int not null default 0 comment '父级id',
	level int not null default 1 comment '层级，从1开始',
    created datetime,
    modified datetime
);

Three-level query process: query the third-level tree, query the second-level tree according to the parent_id of the third-level tree, and query the first-level tree in the same way tree, the backend assembles tree data and returns it to the frontend.

Multi-level query (the level is not fixed/the level is very deep)

In this case, the first thing we think of is subquery or joint table query, but it cannot be used in actual development. Everyone knows the reason:

SQL statements are complex and prone to errors

• Performance problems may be caused by leaders

• So the best way is to add a table tree_depth to maintain the hierarchical depth relationship.

  CREATE TABLE tree_depth (
  	id int not null auto_increment,
  	root_id int not null default 0 comment '根节点（祖先节点）id',
      tree_id int not null default 0 comment '当前节点id',
  	depth int not null default 0 comment '深度（当前节点 tree_id 到 根节点 root_id 的深度）',
      created datetime
  );

  The depth field in the table represents: the depth from the current node tree_id to the root node root_id, not the depth of the entire branch where the current node is located. The depth of all nodes relative to itself is 0

With the tree_depth table, it is convenient to query the organizational data of an N-level node:

Traverse the entire tree:

Directly check all level = 1 nodes in the tree, and then remove the nodes of these nodes id searches subordinate nodes based on parent_id. After querying all nodes, it can be assembled into a complete tree diagram and returned to the front end

Node search (find the entire branch where this node is located)

Query the node treeN from the tree table

select * from tree where id = N

• 
  According to the id value of treeN, to The tree_depth table queries its root node id:

  select root_id from tree_depth where tree_id = #{treeId}

• 
  Query all current nodes of tree_depth based on root_id Branch data

  select * from tree_depth where root_id = #{rootId}

• 
  Get all current node tree_id# from the query tree_depth table data ##select * from tree where id in (?,?,?)

• 
  The branch tree structure where the assembly is located

The above is the detailed content of How to implement MySql multi-level menu query. For more information, please follow other related articles on the PHP Chinese website!