Talk about the tree structure storage and query of mysql

sequence

This article mainly studies the tree structure storage and query of mysql

store parent

This way each node stores its own parent_id information

• Table building and data preparation

   CREATE TABLE `menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `parent_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;
  
  INSERT INTO `menu` (`id`, `name`, `parent_id`) VALUES
  (1, 'level1a',  0),
  (2, 'level1b', 0),
  (3, 'level2a-1a',1),
  (4, 'level2b-1a',1),
  (5, 'level2a-1b', 2),
  (6, 'level2b-1b', 2),
  (7, 'level3-2a1a', 3),
  (8, 'level3-2b1a', 4),
  (9, 'level3-2a1b', 5),
  (10, 'level3-2b1b', 6);

• Inquire

   -- 查询跟节点下的所有节点
  SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3
  FROM menu AS t1
  LEFT JOIN menu AS t2 ON t2.parent_id = t1.id
  LEFT JOIN menu AS t3 ON t3.parent_id = t2.id
  WHERE t1.name = 'level1a';
  
  +---------+------------+-------------+
  | lev1    | lev2       | lev3        |
  +---------+------------+-------------+
  | level1a | level2a-1a | level3-2a1a |
  | level1a | level2b-1a | level3-2b1a |
  +---------+------------+-------------+
  
  -- 查询叶子节点
  SELECT t1.name FROM
  menu AS t1 LEFT JOIN menu as t2
  ON t1.id = t2.parent_id
  WHERE t2.id IS NULL;
  
  +-------------+
  | name        |
  +-------------+
  | level3-2a1a |
  | level3-2b1a |
  | level3-2a1b |
  | level3-2b1b |
  +-------------+

  It is more convenient to store and modify, that is, it is more laborious to query the tree in SQL. Generally, it is loaded into memory and constructed by the application itself.

storage path

In this way, on the basis of storing the parent, the path is additionally stored, that is, the path from the root node to the node

• Table building and data preparation

   CREATE TABLE `menu_path` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `path` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;
  
  INSERT INTO `menu_path` (`id`, `name`, `parent_id`, `path`) VALUES
  (1, 'level1a', 0, '1/'),
  (2, 'level1b', 0, '2/'),
  (3, 'level2a-1a',1, '1/3'),
  (4, 'level2b-1a',1, '1/4'),
  (5, 'level2a-1b', 2, '2/5'),
  (6, 'level2b-1b', 2, '2/6'),
  (7, 'level3-2a1a', 3, '1/3/7'),
  (8, 'level3-2b1a', 4, '1/4/8'),
  (9, 'level3-2a1b', 5, '2/5/9'),
  (10, 'level3-2b1b', 6, '2/6/10');

• Inquire

   -- 查询某个节点的所有子节点
  select * from menu_path where path like '1/%'
  +----+-------------+-----------+-------+
  | id | name        | parent_id | path  |
  +----+-------------+-----------+-------+
  | 1  | level1a     | 0         | 1/    |
  | 3  | level2a-1a  | 1         | 1/3   |
  | 4  | level2b-1a  | 1         | 1/4   |
  | 7  | level3-2a1a | 3         | 1/3/7 |
  | 8  | level3-2b1a | 4         | 1/4/8 |
  +----+-------------+-----------+-------+

  In terms of finding a node and its child nodes, it is more difficult to modify, especially when the node moves, the paths of all child nodes must be modified accordingly.

MPTT (Modified Preorder Tree Traversal)

Instead of storing parent_id, store lft and rgt, whose values are determined by the preorder traversal order of the tree

• Table building and data preparation

   CREATE TABLE `menu_preorder` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `lft` int(11) NOT NULL DEFAULT '0',
  `rgt` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;
  
                   1(level1a)14
         2(level2a)7                8(level2b)13
  3(level3a-2a)4 5(level3b-2a)6 9(level3c-2b)10 11(level3d-2b)12
  
  INSERT INTO `menu_preorder` (`id`, `name`, `lft`, `rgt`) VALUES
  (1, 'level1a', 1, 14),
  (2, 'level2a',2, 7),
  (3, 'level2b',8, 13),
  (4, 'level3a-2a', 3, 4),
  (5, 'level3b-2a', 5, 6),
  (6, 'level3c-2b', 9, 10),
  (7, 'level3d-2b', 11, 12);
  
  select * from menu_preorder
  +----+------------+-----+-----+
  | id | name       | lft | rgt |
  +----+------------+-----+-----+
  | 1  | level1a    | 1   | 14  |
  | 2  | level2a    | 2   | 7   |
  | 3  | level2b    | 8   | 13  |
  | 4  | level3a-2a | 3   | 4   |
  | 5  | level3b-2a | 5   | 6   |
  | 6  | level3c-2b | 9   | 10  |
  | 7  | level3d-2b | 11  | 12  |
  +----+------------+-----+-----+

• Inquire

   -- 查询某个节点及其子节点，比如level2b
  select * from menu_preorder where lft between 8 and 13
  +----+------------+-----+-----+
  | id | name       | lft | rgt |
  +----+------------+-----+-----+
  | 3  | level2b    | 8   | 13  |
  | 6  | level3c-2b | 9   | 10  |
  | 7  | level3d-2b | 11  | 12  |
  +----+------------+-----+-----+
  
  -- 查询所有叶子节点
  SELECT name
  FROM menu_preorder
  WHERE rgt = lft + 1;
  
  +------------+
  | name       |
  +------------+
  | level3a-2a |
  | level3b-2a |
  | level3c-2b |
  | level3d-2b |
  +------------+
  
  -- 查询某个节点及其父节点
  SELECT parent.*
  FROM menu_preorder AS node,
  menu_preorder AS parent
  WHERE node.lft BETWEEN parent.lft AND parent.rgt
  AND node.name = 'level2b'
  ORDER BY parent.lft;
  
  +----+---------+-----+-----+
  | id | name    | lft | rgt |
  +----+---------+-----+-----+
  | 1  | level1a | 1   | 14  |
  | 3  | level2b | 8   | 13  |
  +----+---------+-----+-----+
  
  -- 树形结构展示
  SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
  FROM menu_preorder AS node,
  menu_preorder AS parent
  WHERE node.lft BETWEEN parent.lft AND parent.rgt
  GROUP BY node.name
  ORDER BY node.lft;
  
  +--------------+
  | name         |
  +--------------+
  | level1a      |
  |  level2a     |
  |   level3a-2a |
  |   level3b-2a |
  |  level2b     |
  |   level3c-2b |
  |   level3d-2b |
  +--------------+

The advantage is that the range can be searched through lft (the lft and rgt of the node are used as the range), but the disadvantage is that adding or deleting nodes will cause the lft and rgt of many nodes to be modified

summary

• The way to store the parent is the most scenario. Generally, if the amount of data in the tree structure is not large, the tree structure and search are directly constructed in the memory of the application layer.
• The advantage of storing the path is that you can use the path to find the node and its child nodes. The disadvantage is that moving the node requires cascading the paths of all child nodes, which is more laborious.
• The advantage of the MPTT method is that the range can be searched through lft (the lft and rgt of the node are used as the range). The disadvantage is that adding or deleting nodes leads to the modification of the lft and rgt of many nodes.

doc

• Managing Hierarchical Data in MySQL
• hierarchical-data-database
• hierarchical-data-database-2
• hierarchical-data-database-3