mysql递归查询子节点

来源:转载

先创建一张测试表

DROP TABLE IF EXISTS `one`;CREATE TABLE `one` ( `id` int(11) NOT NULL, `pid` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入测试数据

INSERT INTO `one` VALUES ('1', null, '11111');INSERT INTO `one` VALUES ('2', null, '222');INSERT INTO `one` VALUES ('3', '1', '333');INSERT INTO `one` VALUES ('4', '1', '444');INSERT INTO `one` VALUES ('5', '4', '55555');INSERT INTO `one` VALUES ('6', '4', '666');INSERT INTO `one` VALUES ('7', '6', '777');INSERT INTO `one` VALUES ('8', '7', '8888888');INSERT INTO `one` VALUES ('9', '8', '8888888');INSERT INTO `one` VALUES ('10', '9', '8888888');INSERT INTO `one` VALUES ('11', '10', '8888888');INSERT INTO `one` VALUES ('12', '11', '8888888');

创建函数

<pre name="code" class="sql">CREATE DEFINER=`root`@`%` FUNCTION `testOne`(areaId INT) RETURNS varchar(4000) CHARSET utf8BEGINDECLARE sTemp VARCHAR(4000);DECLARE sTempChd VARCHAR(4000);SET sTemp = '$';SET sTempChd = cast(areaId as char);WHILE sTempChd is not NULL DOSET sTemp = CONCAT(sTemp,',',sTempChd);SELECT group_concat(id) INTO sTempChd FROM one where FIND_IN_SET(pid,sTempChd)>0;END WHILE;return sTemp;END


查询时就是用 select * from one where  FIND_IN_SET(id, testOne(1))

可以将id为1的所有下级全部查询出来



分享给朋友:
您可能感兴趣的文章:
随机阅读: