您好,登錄后才能下訂單哦!
這篇文章主要介紹“mysql的樹形結(jié)構(gòu)存儲及查詢實例分析”,在日常操作中,相信很多人在mysql的樹形結(jié)構(gòu)存儲及查詢實例分析問題上存在疑惑,小編查閱了各式資料,整理出簡單好用的操作方法,希望對大家解答”mysql的樹形結(jié)構(gòu)存儲及查詢實例分析”的疑惑有所幫助!接下來,請跟著小編一起來學習吧!
這種方式就是每個節(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)存由應用自己構(gòu)造
這種方式在存儲parent的基礎(chǔ)上,額外存儲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都得跟著修改
不存儲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 | +----+---------+-----+-----+ -- 樹形結(jié)構(gòu)展示 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都要修改
到此,關(guān)于“mysql的樹形結(jié)構(gòu)存儲及查詢實例分析”的學習就結(jié)束了,希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習,快去試試吧!若想繼續(xù)學習更多相關(guān)知識,請繼續(xù)關(guān)注億速云網(wǎng)站,小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。