MySQL 查詢樹(shù)結(jié)構(gòu)方式
更新時(shí)間：2021年12月14日 10:29:53 作者：弦上的夢(mèng)
今天小編就為大家分享一篇MySQL 查詢樹(shù)結(jié)構(gòu)方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教
MySQL 查詢樹(shù)結(jié)構(gòu)
1. 關(guān)于樹(shù)結(jié)構(gòu)
此類(lèi)結(jié)構(gòu)的數(shù)據(jù)，通常需要表結(jié)構(gòu)中含有id 、parentId等自關(guān)聯(lián)字段，有時(shí)為了提高查詢效率還可增加更多冗余字段，如index，index的值為所有父級(jí)目錄的id字符串集合。
關(guān)于樹(shù)結(jié)構(gòu)數(shù)據(jù)的組裝，常見(jiàn)的寫(xiě)法是在程序中通過(guò)遞歸的方式去構(gòu)建出一顆完整的樹(shù)，單純通過(guò)sql的方式其實(shí)并不常用，下面分別給出兩種方式的例子。
2. MySQL自定義函數(shù)的方式
什么是MySQL自定義函數(shù)：聚合函數(shù)，日期函數(shù)之類(lèi)的都是MySQL的函數(shù)，此處我們定義的函數(shù)可同他們一樣使用，不過(guò)只能在定義的數(shù)據(jù)庫(kù)中使用，自定義函數(shù)和存儲(chǔ)過(guò)程類(lèi)似，不同的是，函數(shù)只會(huì)返回一個(gè)值，不允許返回一個(gè)結(jié)果集。
2.1 創(chuàng)建測(cè)試數(shù)據(jù)
CREATE TABLE `tree` ( `id` bigint(11) NOT NULL, `pid` bigint(11) NULL DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; INSERT INTO `tree` VALUES (1, 0, '中國(guó)'); INSERT INTO `tree` VALUES (2, 1, '四川省'); INSERT INTO `tree` VALUES (3, 2, '成都市'); INSERT INTO `tree` VALUES (4, 3, '武侯區(qū)'); INSERT INTO `tree` VALUES (5, 4, '紅牌樓'); INSERT INTO `tree` VALUES (6, 1, '廣東省'); INSERT INTO `tree` VALUES (7, 1, '浙江省'); INSERT INTO `tree` VALUES (8, 6, '廣州市');
2.2 獲取 某節(jié)點(diǎn)下所有子節(jié)點(diǎn)
CREATE FUNCTION `GET_CHILD_NODE`(rootId varchar(100))
RETURNS varchar(2000)
BEGIN
DECLARE str varchar(2000);
DECLARE cid varchar(100);
SET str = '$';
SET cid = rootId;
WHILE cid is not null DO
SET str = concat(str, ',', cid);
SELECT group_concat(id) INTO cid FROM tree where FIND_IN_SET(pid, cid);
END WHILE;
RETURN str;
END
調(diào)用自定義函數(shù)
select * from tree where FIND_IN_SET(id, GET_CHILD_NODE(2));
2.3 獲取 某節(jié)點(diǎn)的所有父節(jié)點(diǎn)
CREATE FUNCTION `GET_PARENT_NODE`(rootId varchar(100))
RETURNS varchar(1000)
BEGIN
DECLARE fid varchar(100) default '';
DECLARE str varchar(1000) default rootId;
WHILE rootId is not null do
SET fid =(SELECT pid FROM tree WHERE id = rootId);
IF fid is not null THEN
SET str = concat(str, ',', fid);
SET rootId = fid;
ELSE
SET rootId = fid;
END IF;
END WHILE;
return str;
END
調(diào)用自定義函數(shù)
select * from tree where FIND_IN_SET(id, GET_PARENT_NODE(5));
3. Oracle數(shù)據(jù)庫(kù)的方式
只需要使用start with connect by prior語(yǔ)句即可完成遞歸的樹(shù)查詢，詳情請(qǐng)自己查閱相關(guān)資料。
4. 程序代碼遞歸的方式構(gòu)建樹(shù)
這里我就不給出完整代碼了，遞歸的方式很簡(jiǎn)單，就是先查出所有樹(shù)節(jié)點(diǎn)，然后通過(guò)一個(gè)TreeNode類(lèi)中的add方法遞歸把所有子節(jié)點(diǎn)給加進(jìn)來(lái)。核心代碼如下：
public class TreeNodeDTO {
private String id;
private String parentId;
private String name;
private List<TreeNodeDTO> children = new ArrayList<>();
public void add(TreeNodeDTO node) {
if ("0".equals(node.parentId)) {
this.children.add(node);
} else if (node.parentId.equals(this.id)) {
this.children.add(node);
} else {
//遞歸調(diào)用add()添加子節(jié)點(diǎn)
for (TreeNodeDTO tmp_node : children) {
tmp_node.add(node);
}
}
}
}
5. 通過(guò)hashMap，只需要遍歷一次
就可以完成樹(shù)的生成：五星推薦
List<TreeNodeDTO> list = dbMapper.getNodeList();
ArrayList<TreeNodeDTO> rootNodes = new ArrayList<>();
Map<Integer, TreeNodeDTO> map = new HashMap<>();
for (TreeNodeDTO node :list) {
map.put(node.getId(), node);
Integer parentId = node.getParentId();
// 判斷是否有父節(jié)點(diǎn) (沒(méi)有父節(jié)點(diǎn)本身就是個(gè)父菜單)
if (parentId.equals('0')){
rootNodes.add(node);
// 找出不是父級(jí)菜單的且集合中包括其父菜單ID
} else if (map.containsKey(parentId)){
map.get(parentId).getChildren().add(node);
}
}
MySQL 查詢帶樹(shù)狀結(jié)構(gòu)的信息
在Oracle中有函數(shù)應(yīng)用直接能夠查詢出樹(shù)狀的樹(shù)狀結(jié)構(gòu)信息，例如有下面樹(shù)狀結(jié)構(gòu)的組織成員架構(gòu)，那么如果我們想查其中一個(gè)節(jié)點(diǎn)下的所有節(jié)點(diǎn)信息
在Oracle中可以直接用下面的語(yǔ)法可以進(jìn)行直接查詢
START WITH CONNECT BY PRIOR
但是在Mysql中是沒(méi)有這個(gè)語(yǔ)法的
而如果你也是想要查詢這樣的數(shù)據(jù)結(jié)構(gòu)信息該怎么做呢？我們可以自定義函數(shù)。我們將上面的信息初始化信息進(jìn)數(shù)據(jù)庫(kù)中。首先先創(chuàng)建一張表用于存儲(chǔ)這些信息，ID為存儲(chǔ)自身的ID信息，PARENT_ID存儲(chǔ)父ID信息
CREATE TABLE `company_inf` ( `ID` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `NAME` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `PARENT_ID` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL )
然后將圖中的信息初始化表中
INSERT INTO company_inf VALUES ('1','總經(jīng)理王大麻子','1');
INSERT INTO company_inf VALUES ('2','研發(fā)部經(jīng)理劉大瘸子','1');
INSERT INTO company_inf VALUES ('3','銷(xiāo)售部經(jīng)理馬二愣子','1');
INSERT INTO company_inf VALUES ('4','財(cái)務(wù)部經(jīng)理趙三駝子','1');
INSERT INTO company_inf VALUES ('5','秘書(shū)員工J','1');
INSERT INTO company_inf VALUES ('6','研發(fā)一組組長(zhǎng)吳大棒槌','2');
INSERT INTO company_inf VALUES ('7','研發(fā)二組組長(zhǎng)鄭老六','2');
INSERT INTO company_inf VALUES ('8','銷(xiāo)售人員G','3');
INSERT INTO company_inf VALUES ('9','銷(xiāo)售人員H','3');
INSERT INTO company_inf VALUES ('10','財(cái)務(wù)人員I','4');
INSERT INTO company_inf VALUES ('11','開(kāi)發(fā)人員A','6');
INSERT INTO company_inf VALUES ('12','開(kāi)發(fā)人員B','6');
INSERT INTO company_inf VALUES ('13','開(kāi)發(fā)人員C','6');
INSERT INTO company_inf VALUES ('14','開(kāi)發(fā)人員D','7');
INSERT INTO company_inf VALUES ('15','開(kāi)發(fā)人員E','7');
INSERT INTO company_inf VALUES ('16','開(kāi)發(fā)人員F','7');
例如我們想要查詢研發(fā)部門(mén)經(jīng)理劉大瘸子下的所有員工，在Oracle中我們可以這樣寫(xiě)
SELECT * FROM T_PORTAL_AUTHORITY START WITH ID='1' CONNECT BY PRIOR ID = PARENT_ID
而在Mysql中我們需要下面這樣自定義函數(shù)
CREATE FUNCTION getChild(parentId VARCHAR(1000))
RETURNS VARCHAR(1000)
BEGIN
DECLARE oTemp VARCHAR(1000);
DECLARE oTempChild VARCHAR(1000);
SET oTemp = '';
SET oTempChild =parentId;
WHILE oTempChild is not null DO
IF oTemp != '' THEN
SET oTemp = concat(oTemp,',',oTempChild);
ELSE
SET oTemp = oTempChild;
END IF;
SELECT group_concat(ID) INTO oTempChild FROM company_inf where parentId<>ID and FIND_IN_SET(parent_id,oTempChild)>0;
END WHILE;
RETURN oTemp;
END
然后這樣查詢即可
SELECT * FROM company_inf WHERE FIND_IN_SET(ID,getChild('2'));
此時(shí)查看查詢出來(lái)的信息就是劉大瘸子下所有的員工信息了
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
這篇文章主要給大家介紹了關(guān)于linux/mac安裝mysql忘記密碼的解決辦法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧。2017-10-10
Hibernate4在MySQL5.1以上版本創(chuàng)建表出錯(cuò) type=InnDB
本文主要介紹解決Hibernate4在MySQL5.1自動(dòng)創(chuàng)建表出錯(cuò)的方法，簡(jiǎn)單實(shí)用，需要的朋友可以參考下。2016-05-05
mysql 8.0.12 解壓版安裝教程 個(gè)人親測(cè)!
這篇文章主要為大家詳細(xì)介紹了mysql 8.0.12 解壓版安裝教程，步驟簡(jiǎn)單，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-08-08
有些時(shí)候我們需要?jiǎng)h除表中指定ID數(shù)據(jù)，主要是接下模糊刪除，需要的朋友可以參考下2013-01-01
MySQL性能優(yōu)化之Open_Table配置參數(shù)的合理配置建議
這篇文章主要介紹了MySQL性能優(yōu)化之Open_Table配置參數(shù)的合理配置建議,在MySQL數(shù)據(jù)庫(kù)中，Opened_tables表示打開(kāi)過(guò)的表數(shù)量,需要的朋友可以參考下2014-07-07
mysql關(guān)聯(lián)子查詢的一種優(yōu)化方法分析
這篇文章主要介紹了mysql關(guān)聯(lián)子查詢的一種優(yōu)化方法,結(jié)合實(shí)例形式分析了針對(duì)MySQL的關(guān)聯(lián)子查詢進(jìn)行優(yōu)化的技巧,需要的朋友可以參考下2016-04-04
mysql根據(jù)拼音字母查詢（簡(jiǎn)單易懂的字段拼音查詢）
MySQL在開(kāi)發(fā)中,我們經(jīng)常需要根據(jù)字段拼音查詢數(shù)據(jù)庫(kù)中的數(shù)據(jù),它支持多種查詢方式,包括根據(jù)拼音字母查詢,使用 Collation 可以方便地進(jìn)行簡(jiǎn)單的拼音查詢,而使用拼音索引可以大幅提高查詢性能,根據(jù)具體的需求和情況,我們可以選擇合適的方法來(lái)實(shí)現(xiàn)拼音查詢2023-10-10
這篇文章主要介紹了macOS安裝Solr并索引MySQL的相關(guān)資料,非常不錯(cuò)具有參考借鑒價(jià)值，需要的朋友可以參考下2016-11-11
Tableau連接mysql數(shù)據(jù)庫(kù)的實(shí)現(xiàn)步驟
本文主要介紹了Tableau連接mysql數(shù)據(jù)庫(kù)的實(shí)現(xiàn)步驟，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01
這篇文章主要介紹了MySQL之mysqldump的使用詳解，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-09-09