之前一直用的是 Oracle, 对于树形查询可以使用 start with ... connect by
select * from menu start with id='130000' connect by id = prior parent_id;
没错, 这是 Oracle 所支持的
现在公司用的是 mysql, 对于这种查询方式只能通过 sql 语句实现了
语言都是相通的, 何况 sql 呢
mysql 随没有自带的语法支持, 不过可以通过创建函数来实现递归查询.
如下图所示...
直接上 sql 语句
- create table `nodelist` (
- `id` int (11),
- `nodecontent` varchar (300),
- `pid` int (11)
- );
- insert into `nodelist` (`id`, `nodecontent`, `pid`) values('1','a',NULL);
- insert into `nodelist` (`id`, `nodecontent`, `pid`) values('2','b','1');
- insert into `nodelist` (`id`, `nodecontent`, `pid`) values('3','c','1');
- insert into `nodelist` (`id`, `nodecontent`, `pid`) values('4','d','2');
- insert into `nodelist` (`id`, `nodecontent`, `pid`) values('5','e','3');
- insert into `nodelist` (`id`, `nodecontent`, `pid`) values('6','f','3');
- insert into `nodelist` (`id`, `nodecontent`, `pid`) values('7','g','5');
- insert into `nodelist` (`id`, `nodecontent`, `pid`) values('8','h','7');
- insert into `nodelist` (`id`, `nodecontent`, `pid`) values('9','i','8');
- insert into `nodelist` (`id`, `nodecontent`, `pid`) values('10','j','8');
之后创建一个函数
- DROP FUNCTION IF EXISTS `getChild`$$
- CREATE DEFINER=`root`@`localhost` FUNCTION `getChild`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8
- BEGIN
- DECLARE ptemp VARCHAR(1000);
- DECLARE ctemp VARCHAR(1000);
- SET ptemp = '#';
- SET ctemp =CAST(rootId AS CHAR);
- WHILE ctemp IS NOT NULL DO
- SET ptemp = CONCAT(ptemp,',',ctemp);
- SELECT GROUP_CONCAT(id) INTO ctemp FROM nodelist
- WHERE FIND_IN_SET(pid,ctemp)>0;
- END WHILE;
- RETURN ptemp;
- END$$
- DELIMITER ;
OK, 查询可以通过将函数当做一个查询条件.
SELECT * FROM nodelist WHERE FIND_IN_SET(id, getChild(3))
借鉴 https://www.jianshu.com/p/f99665266bb1
里面用到的内置函数 https://baijiahao.baidu.com/s?id=1595349117525189591&wfr=spider&for=pc
你只要能想到的, 都有对应的解决方式, 幸运的是你该踩得一些坑别人实现给你填好了.
来源: http://www.bubuko.com/infodetail-2714863.html