Shulou(Shulou.com)06/01 Report--

This article mainly introduces "mysql tree structure storage and query case analysis". In daily operation, I believe that many people have doubts about mysql tree structure storage and query case analysis. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "mysql tree structure storage and query case analysis". Next, please follow the editor to study!

Storage parent

In 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 '0mm, PRIMARY KEY (`id`) ENGINE=InnoDB INSERT INTO `menu` (`id`, `name`, `menu`) 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-2a1a1a, 3), (8,' level3-2b1a1, 4), (9, 'level3-2a1b1), 5), (10) 'level3-2b1b1, 6)

Query

-- query all nodes under the node SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3FROM menu AS t1LEFT JOIN menu AS T2 ON t2.parent_id = t1.idLEFT JOIN menu AS T3 ON t3.parent_id = t2.idWHERE t1.name = 'level1a' +-+ | lev1 | lev2 | lev3 | +-+ | level1a | level2a-1a | level3-2a1a | | level1a | level2b-1a | level3-2b1a | +-- -+-- query the leaf node SELECT t1.name FROMmenu AS T1 LEFT JOIN menu as t2ON t1.id = t2.parent_idWHERE 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 difficult to query the tree in sql. Generally, it is loaded into memory and constructed by the application itself.

Storage path

This method stores the parent in addition to the path, 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,' 1level2b-1b','), (2, 'level2a-1a',1 0,' 2level2a-1a',1'), (3, 'level2a-1a',1,' 1level2b-1b', 3'), (4, 'level2b-1a',1,' 1level2b-1b', 4'), (5, 'level2b-1b', 2,' 2level2b-1a',1 5'), (6, 'level2b-1b', 2,' 2Comp5') (7, 'level3-2a1a1a, 3,' 1xpx7'), (8, 'level3-2b1a1a, 4,' 1xpx8'), (9, 'level3-2a1b1b, 5,' 2px9'), (10, 'level3-2b1bl, 6,' 2x6, 10')

Query

-- query all the child nodes of a node, select * from menu_path where path like '1GUBG.% query, id, name, parent_id, path | +-+. + | 1 | level1a | 0 | 1 / | | 3 | level2a-1a | 1 | 1 level2a-1a | 4 | level2b-1a | 1 | 1 2a1a | | 7 | level3-2b1a | 3 | 1-3-7 | 8 | level3-2b1a | 4 | 1-4-8 | +-+

To find the comparison between a node and its child nodes, it is difficult to modify, especially when the node moves, the path of all the child nodes has to be modified accordingly.

MPTT (Modified Preorder Tree Traversal)

Instead of storing parent_id, store lft,rgt instead, and their values are determined by the first-order 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 '0mm, `rgt` int (11) NOT NULL DEFAULT' 0mm, PRIMARY KEY (`id`) ENGINE=InnoDB 1 (level1a) 142 (level2a) 7 8 (level2b) 4 5 (level3b-2a) 6 9 (level3c-2b) 10 11 (level3d-2b) 12INSERT 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 | +-+

Query

-- query a node and its child nodes For example, level2bselect * from menu_preorder where lft between 8 and 13 members id | name | lft | rgt | +-- + | 3 | level2b | 8 | 13 | 6 | level3c-2b | 9 | 10 | 7 | level3d-2b | 11 | 12 | + +-- query all leaf nodes SELECT nameFROM menu_preorderWHERE rgt = lft + 1 +-+ | name | +-+ | level3a-2a | | level3b-2a | | level3c-2b | | level3d-2b | +-+-query a node and its parent node SELECT parent.*FROM menu_preorder AS node,menu_preorder AS parentWHERE node.lft BETWEEN parent.lft AND parent.rgtAND node.name = 'level2b'ORDER BY parent.lft +-+ | id | name | lft | rgt | +-+ | 1 | level1a | 1 | 14 | 3 | level2b | 8 | 13 | +-tree structure display SELECT CONCAT (REPEAT ('') COUNT (parent.name)-1), node.name) AS nameFROM menu_preorder AS node,menu_preorder AS parentWHERE node.lft BETWEEN parent.lft AND parent.rgtGROUP BY node.nameORDER BY node.lft +-+ | name | +-+ | level1a | | level2a | | level3a-2a | | level3b-2a | | level2b | | level3c-2b | | level3d-2b | +-+

The advantage is that the scope can be found through lft (the node's lft,rgt is used as the scope). The disadvantage is that adding and deleting nodes causes many nodes' lft and rgt to be modified.

At this point, the study on "mysql tree structure storage and query instance analysis" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.