作者：pany
時(shí)間：2019-3-12 21:36
“收獲之前你一定得先付出介时，這是鐵律”
一报咳、背景
在項(xiàng)目中，需要查詢父節(jié)點(diǎn)下所有的子節(jié)點(diǎn)玖详，我的同事是利用遞歸查詢的把介，我發(fā)現(xiàn)他是參考網(wǎng)上的例子寫的。
地址：https://www.cnblogs.com/rainydayfmb/p/8028868.html（其實(shí)這個(gè)作者寫的還算比較仔細(xì)）
我在重構(gòu)他的模塊時(shí)蟋座，發(fā)現(xiàn)這個(gè)遞歸查詢并不能將所有的字節(jié)點(diǎn)都查詢出來(lái)拗踢，起初我是懷疑是sql的問(wèn)題，于是按照上面的案例寫了一遍向臀，發(fā)現(xiàn)sql能夠查詢出所有子節(jié)點(diǎn)巢墅，也就證明sql沒(méi)問(wèn)題。于是我懷疑數(shù)據(jù)有問(wèn)題，于是將數(shù)據(jù)仔細(xì)梳理好幾遍砂缩，發(fā)現(xiàn)數(shù)據(jù)也沒(méi)有問(wèn)題作谚。這下我凌亂了，sql 和數(shù)據(jù)都沒(méi)問(wèn)題庵芭，到底是什么造成了這么詭異的事情呢妹懒？
之所以走了這么多彎路，還是自己想圖省事双吆，懶得去分析sql眨唬，走了這么多彎路之后，我不能忍受了好乐，于是我決定好好分析下案例中的sql匾竿。
二、基于第一組數(shù)據(jù)分析（類似于案例中的數(shù)據(jù)）
表結(jié)構(gòu)：（由于演示蔚万，表結(jié)構(gòu)創(chuàng)建的比較隨意傲胙）
DROP TABLE IF EXISTS `re_menu`;
CREATE TABLE `re_menu`? (
? `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜單id',
? `parent_id` int(11) NULL DEFAULT NULL COMMENT '父節(jié)點(diǎn)id',
? `menu_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜單名稱',
? `menu_url` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '菜單路徑',
? `status` tinyint(3) NULL DEFAULT 1 COMMENT '菜單狀態(tài) 1-有效；0-無(wú)效',
? PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12223 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
數(shù)據(jù)：
INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (1, 0, '菜單1', '', 1);
INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (11, 1, '菜單11', '', 1);
INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (111, 11, '菜單111', '', 1);
INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (122, 11, '菜單122', '', 1);
INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (1111, 111, '菜單12211', '', 1);
INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (1221, 122, '菜單1221', '', 1);
INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (12221, 1221, NULL, '', 1);
INSERT INTO `re_menu`(`id`, `parent_id`, `menu_name`, `menu_url`, `status`) VALUES (12222, 1221, NULL, '', 1);
##? sql 分析：
select
????? t3. id
from (
??????????????select
??????????????????????? t1.id,
???????????? ? ? ? ? ? if ( find_in_set(t1.parent_id, @pids) > 0, @pids:= concat(? @pids, ? ','? , ? t1.id), 0) as ischild
??????????????from
????????????? (
?????????????????????????? select t.id,? t.parent_id from re_menu t where t.status = 1? order by t.parent_id, t.id
????????????? ) t1,
???????????? (select @pids:= '目標(biāo)節(jié)點(diǎn)id' id)? t2
) t3 where t3.ischild != '0'
先關(guān)注 t1 ,它是元數(shù)據(jù)反璃，查詢的是菜單表狀態(tài)是'1'的所有數(shù)據(jù)昵慌，如下圖；
然后再看t2,它是給變量@pids 賦值 '目標(biāo)節(jié)點(diǎn)id'淮蜈；我們?cè)倏?/p>
if ( find_in_set(t1.parent_id, @pids) > 0, @pids:= concat(? @pids, ? ','? , ? t1.id), 0) as ischild
第一條記錄斋攀，判斷 @pids（也就是目標(biāo)節(jié)點(diǎn)的id，我們假如它是1）是否包含第一條記錄的 parent_id（也就是0）梧田，顯然不包括淳蔼，IF(expr1,expr2,expr3)判斷如果expr1 條件不滿足，執(zhí)行expr3裁眯，也就是0鹉梨；
再看第二條記錄，此時(shí)的@pids 仍然是1穿稳，判斷@pids 是否包含第二條記錄的parent_id（也就是1）俯画，顯然包含，那么執(zhí)行expr2司草， @pids:= concat(? @pids, ? ','? , ? t1.id)艰垂，@pids 拼上' ,'再拼上第二條記錄的id，也就是11埋虹，結(jié)果是"1,11 "猜憎，賦值給@pids，后面的依次這樣分析搔课，確實(shí)能把所有的子節(jié)點(diǎn)都查詢出來(lái)胰柑，如下圖：
（注意： = 和 := 的區(qū)別， = 只是在set和update的時(shí)候才是賦值，而 := 除了在set和update時(shí)是賦值柬讨，select 的時(shí)候也是）
三崩瓤、基于第二組數(shù)據(jù)分析
表結(jié)構(gòu)：
DROP TABLE IF EXISTS `re_menu_new`;
CREATE TABLE `re_menu_new`? (
? `id` varchar(111) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '菜單id',
? `parent_id` varchar(111) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '父節(jié)點(diǎn)id',
? `menu_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜單名稱',
? `menu_url` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '菜單路徑',
? `status` tinyint(3) NULL DEFAULT 1 COMMENT '菜單狀態(tài) 1-有效；0-無(wú)效',
? PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
數(shù)據(jù)：
INSERT INTO `re_menu_new` VALUES ('2001655aa8054fa4841aa192baf45c6b', 'ROOT_FUNCTION_1', '子功能', '', 1);
INSERT INTO `re_menu_new` VALUES ('4cfe206fe97711e88e78fa163e66a663', 'ROOT_FUNCTION_0', '子功能', '', 1);
INSERT INTO `re_menu_new` VALUES ('7a0d9c0d6b164d128dc4e9fa60261169', '4cfe206fe97711e88e78fa163e66a663', '子功能', '', 1);
INSERT INTO `re_menu_new` VALUES ('8cc6b43beae311e88e78fa163e66a663', 'ROOT_FUNCTION_0', '子功能', '', 1);
INSERT INTO `re_menu_new` VALUES ('ROOT', '', '根節(jié)點(diǎn)', '', 1);
INSERT INTO `re_menu_new` VALUES ('ROOT_AREA_MENU', 'ROOT', '菜單節(jié)點(diǎn)', '', 1);
INSERT INTO `re_menu_new` VALUES ('ROOT_FUNCTION_0', 'ROOT_AREA_MENU', '功能1', '', 1);
INSERT INTO `re_menu_new` VALUES ('ROOT_FUNCTION_1', 'ROOT_AREA_MENU', '功能2', '', 1);
##? sql 分析：
select
????? t3. id
from (
??????????????select
??????????????????????? t1.id,
???????????? ? ? ? ? ? if ( find_in_set(t1.parent_id, @pids) > 0, @pids:= concat(? @pids, ? ','? , ?? t1.id), 0) as ischild
?????????????? from
????????????? (
?????????????????????????? select t.id,? t.parent_id from re_menu_new? t where t.status = 1? order by t.parent_id, t.id
????????????? ) t1,
???????????? (select @pids:= 'ROOT' id)? t2
) t3 where t3.ischild != '0'
跟上面一樣踩官，我們先看t1却桶，如下圖；
然后再看t2,它是給變量@pids 賦值 'ROOT'蔗牡；我們?cè)倏?/p>
if ( find_in_set(t1.parent_id, @pids) > 0, @pids:= concat(? @pids, ? ','? , ? t1.id), 0) as ischild
第一條記錄颖系，判斷 @pids（ROOT）是否包含第一條記錄的 parent_id（無(wú)），顯然不包括辩越，IF(expr1,expr2,expr3)判斷如果expr1 條件不滿足嘁扼，執(zhí)行expr3，也就是0黔攒；
再看第二條記錄趁啸，此時(shí)的@pids 仍然是ROOT，判斷@pids 是否包含第二條記錄的parent_id（也就是4cfe206fe97711e88e78fa163e66a663）督惰，顯然不包含不傅，那么執(zhí)行expr3， 返回0姑丑，而 where t3.ischild != '0'? 這里看到 0是被排除的，被認(rèn)為不是子節(jié)點(diǎn)辞友，但是從關(guān)系可以看出id是7a0d9c0d6b164d128dc4e9fa60261169 的節(jié)點(diǎn)很明顯是ROOT下的葉子節(jié)點(diǎn)栅哀。
問(wèn)題在哪呢？
由于 執(zhí)行 select t.id,? t.parent_id from re_menu_new? t where t.status = 1? order by t.parent_id, t.id 的排序?qū)е铝藛?wèn)題称龙，因?yàn)榕判蛑罅羰埃覀僷arent_id的順序不在是從ROOT依次向下排列了，而是中間多了一個(gè)4cfe206fe97711e88e78fa163e66a663鲫尊。所以這個(gè)sql的成敗關(guān)鍵在于元數(shù)據(jù)的排序痴柔。如果我們將第一組數(shù)據(jù)的元素順序改成倒序，如下：
select t.id, t.parent_id from re_menu t where t.status = 1 order by t.parent_id DESC
我們?cè)賵?zhí)行查詢子節(jié)點(diǎn)的sql疫向，也會(huì)出錯(cuò)咳蔚，如下：
我總結(jié)這么多，其實(shí)就像說(shuō)明這個(gè)sql的成敗關(guān)鍵搔驼，如果你的id和pid的關(guān)系是序列這種谈火，使用上面的sql完全沒(méi)問(wèn)題，但是如果類似第二種數(shù)據(jù)舌涨，可能就不適合了糯耍。我說(shuō)的可能有點(diǎn)啰嗦，非常抱歉，因?yàn)槲乙郧爱?dāng)過(guò)老師温技，喜歡講東西多舉例子革为。
那遇到這類問(wèn)題是不是沒(méi)有解決方案了呢？并不是舵鳞，還有其他的寫法震檩，網(wǎng)上也有很多例子。
四系任、解決方案
其實(shí)還有其他的方案恳蹲，主要我們根據(jù)具體環(huán)境去用，有些環(huán)境下俩滥，我們是不能隨便添加存儲(chǔ)過(guò)程的嘉蕾。
下面的解決方案是利用函數(shù)的：(注意函數(shù)的創(chuàng)建用戶要根據(jù)具體的改下，DEFINER=`root`@`%???? 這個(gè)root是指定的用戶霜旧，小白要注意)
DELIMITER //
CREATE DEFINER=`root`@`%` FUNCTION `getChildLst`(rootId varchar(50)) RETURNS varchar(1000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =rootId;
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM re_menu_new where parent_id<>id and FIND_IN_SET(parent_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END//
創(chuàng)建完成之后错忱，使用下面的查詢：
select
? ? r. id,r.parent_id
from re_menu_new r
where FIND_IN_SET(r.id,(select `getChildLst`('ROOT') as id))
所有的結(jié)果都查詢出來(lái)了。
