MySQL通过自定义函数实现递归查询父级ID或者子级ID

在MySQL中，递归查询通常用于处理层次结构的数据，如组织结构、菜单系统或类别树等。当数据的层级关系无法预知或者可能无限深时，传统的JOIN操作可能无法满足需求，此时就需要自定义函数来实现递归查询。本文将详细介绍如何通过MySQL的自定义函数来查询父级ID和子级ID。 我们需要创建一个示例表格`table_name`，该表包含三个字段：`id`（主键）、`status`（状态）和`pid`（父级ID）。表的结构如下： ```sql CREATE TABLE `table_name` ( `id` int(11) NOT NULL AUTO_INCREMENT, `status` int(255) NULL DEFAULT NULL, `pid` int(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; ``` 接着，我们需要插入一些测试数据，构建一个简单的层级关系，例如： ```sql INSERT INTO `table_name` VALUES (1, 12, 0); INSERT INTO `table_name` VALUES (2, 4, 1); INSERT INTO `table_name` VALUES (3, 8, 2); -- ... 更多插入语句 ... ``` 现在，我们将编写两个自定义函数，分别用于查询当前节点的所有父级节点和子级节点。 1. 查询父级节点的自定义函数`getParentList`： ```sql DELIMITER // CREATE FUNCTION `getParentList`(root_id BIGINT) RETURNS VARCHAR(1000) BEGIN DECLARE k INT DEFAULT 0; DECLARE fid INT DEFAULT 1; DECLARE str VARCHAR(1000) DEFAULT '$'; WHILE root_id > 0 DO SET fid=(SELECT pid FROM table_name WHERE id=root_id); IF fid > 0 THEN SET str = concat(str,',',fid); SET root_id = fid; ELSE SET root_id = fid; END IF; END WHILE; RETURN str; END // DELIMITER ; ``` 这个函数使用了一个WHILE循环，每次迭代都会查找当前节点的父级，并将父级ID添加到结果字符串中。当找不到父级ID（即父级ID为0）时，循环结束。 2. 查询子级节点的自定义函数`getChildList`： ```sql DELIMITER // CREATE FUNCTION `getChildList`(root_id BIGINT) RETURNS VARCHAR(1000) BEGIN DECLARE str VARCHAR(1000) ; DECLARE cid VARCHAR(1000) ; DECLARE k INT DEFAULT 0; SET str = '$'; SET cid = CAST(root_id AS CHAR); WHILE cid IS NOT NULL DO IF k > 0 THEN SET str = CONCAT(str,',',cid); END IF; SELECT GROUP_CONCAT(id) INTO cid FROM table_name WHERE FIND_IN_SET(pid, cid) > 0; SET k = k + 1; END WHILE; RETURN str; END // DELIMITER ; ``` 这个函数利用了`GROUP_CONCAT`函数和`FIND_IN_SET`函数，将当前节点的所有子级ID合并成一个字符串。同样，当没有更多的子节点时，循环终止。 使用这两个函数，我们可以轻松地获取任何节点的父级列表和子级列表。例如，要查询ID为3的节点的所有父级，可以执行： ```sql SELECT getParentList(3); ``` 而要查询ID为3的节点的所有子级，可以执行： ```sql SELECT getChildList(3); ``` 以上就是在MySQL中通过自定义函数实现递归查询父级ID和子级ID的方法。这种方法虽然效率可能不如其他数据库系统中的递归查询语法，但对于有限的层级深度和不支持递归查询的MySQL版本，这是一种有效的解决方案。需要注意的是，如果层级关系非常复杂，大量的递归可能会导致性能问题，因此在实际应用中，应结合具体场景考虑优化策略。