在 mysql 中实现树查询

在mysql中实现树查询

在mysql中通过给定一个父节点的id，查询所有层的子节点。

1. 案例数据

CREATE TABLE IF NOT EXISTS `nodelist` (
  `id` int(11) DEFAULT NULL,
  `content` varchar(300) DEFAULT NULL,
  `pid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `nodelist` (`id`, `content`, `pid`) VALUES
	(1, 'a', 0),
	(2, 'b', 1),
	(3, 'c', 1),
	(4, 'd', 2),
	(5, 'e', 3),
	(6, 'f', 3),
	(7, 'g', 5),
	(8, 'h', 7),
	(9, 'i', 8),
	(10, 'j', 8);

2. 定义函数

该函数根据参数设置的父节点id返回一个由所有层子节点组成的字符串。

CREATE DEFINER=`root`@`localhost` FUNCTION `getChildLst`(
	`rootId` INT
)
RETURNS varchar(1000) CHARSET utf8mb4
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);

SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);

WHILE sTempChd is not null DO
	SET sTemp = concat(sTemp,',',sTempChd);
	SELECT group_concat(id) INTO sTempChd 
	FROM nodelist # 表名
	WHERE FIND_IN_SET(pid, sTempChd) > 0;
END WHILE;

RETURN sTemp;
END

3. 利用函数查询子节点

利用 FIND_IN_SET 筛选id在getChildLst函数返回值中的记录。

-- 查询父节点id为7的所有子节点的记录。
SELECT * from nodelist
where FIND_IN_SET(id, getChildLst(7));