詳細聊一聊mysql的樹形結構存儲以及查詢

更新時間：2022年04月05日 11:13:43 作者：go4it

由于mysql是關系型數(shù)據(jù)庫,因此對于類似組織架構,子任務等相關的樹形結構的處理不是很友好,下面這篇文章主要給大家介紹了關于mysql樹形結構存儲以及查詢的相關資料,需要的朋友可以參考下

序

本文主要研究一下mysql的樹形結構存儲及查詢

存儲parent

這種方式就是每個節(jié)點存儲自己的parent_id信息

建表及數(shù)據(jù)準備

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);

查詢

-- 查詢跟節(jié)點下的所有節(jié)點
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 |
+---------+------------+-------------+

-- 查詢?nèi)~子節(jié)點
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 |
+-------------+

存儲及修改上比較方便，就是要在sql里頭查詢樹比較費勁，一般是加載到內(nèi)存由應用自己構造

存儲path

這種方式在存儲parent的基礎上，額外存儲path，即從根節(jié)點到該節(jié)點的路徑

建表及數(shù)據(jù)準備

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');

查詢

-- 查詢某個節(jié)點的所有子節(jié)點
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 |
+----+-------------+-----------+-------+

查找某個節(jié)點及其子節(jié)點比較方面，就是修改比較費勁，特別是節(jié)點移動，所有子節(jié)點的path都得跟著修改

MPTT(Modified Preorder Tree Traversal)

不存儲parent_id，改為存儲lft,rgt，它們的值由樹的先序遍歷順序決定

建表及數(shù)據(jù)準備

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  |
+----+------------+-----+-----+

查詢

-- 查詢某個節(jié)點及其子節(jié)點，比如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  |
+----+------------+-----+-----+

-- 查詢所有葉子節(jié)點
SELECT name
FROM menu_preorder
WHERE rgt = lft + 1;

+------------+
| name       |
+------------+
| level3a-2a |
| level3b-2a |
| level3c-2b |
| level3d-2b |
+------------+

-- 查詢某個節(jié)點及其父節(jié)點
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 |
+--------------+

好處是通過lft進行范圍(該節(jié)點的lft,rgt作為范圍)查找就可以，缺點就是增刪節(jié)點導致很多節(jié)點的lft及rgt都要修改

小結

存儲parent的方式最為場景，一般樹形結構數(shù)據(jù)量不大的話，直接在應用層內(nèi)存構造樹形結構和搜索
存儲path的好處是可以借助path來查找節(jié)點及其子節(jié)點，缺點就是移動node需要級聯(lián)所有子節(jié)點的path，比較費勁
MPTT的方式好處是通過lft進行范圍(該節(jié)點的lft,rgt作為范圍)查找就可以，缺點就是增刪節(jié)點導致很多節(jié)點的lft及rgt都要修改