MySQL递归查找所有父母

本文介绍了MySQL递归查找所有父母的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下表格:

Category id INT name VARCHAR Category_parent category_id INT parent_id INT

在 Category_parent 中，我存储类别关系，两列都从类别表中获取 id.所以我可以确定什么是什么的父级.

In Category_parent I store category relationship, and both columns get id from category table. So I can identify what is parent of what.

可以有任意数量的世代，因此找到特定类别继承自的所有类别有点困难.

There can be any number of generations, so it is a bit difficult to find all categories from which a particular category inherited.

比如CAT10的父级是CAT5，CAT5的CAT3，CAT3的CAT1等等.此外，一个类别可能有任意数量的父母.我只给出了类别的名称，我的查询应该返回该类别的所有祖先.

For example, CAT10's parent is CAT5, CAT5's CAT3, CAT3's CAT1 and so on. Besides, one category may have any number of parents. I give just name of category and my query should return all ancestors of that category.

MySQL 中是否有解决此类问题的方法?我如何为此使用存储过程?

Is there a solution to such problems in MySQL? How can I use stored procedure for this?

推荐答案

让我给你一个想法.创建一个从给定 ID 多次获取 parent_id 的函数，我们称之为 generation.Cat10 第 1 代将是 CAT5 第 2 代的父母，依此类推.

Let me give you an idea. Create a function that gets the parent_id from a given ID a number of times, let's call it generation. Cat10 generation 1 would be parent who is CAT5 generation 2 would be CAT3 and so on.

DROP FUNCTION IF EXISTS getNameIdGeneration; DELIMITER $$ CREATE FUNCTION getNameIdGeneration(idPar int, generationPar int) RETURNS VARCHAR(1000) READS SQL DATA BEGIN DECLARE auxLoopVar INT default 0; DECLARE parentIdVar INT default idPar; DECLARE nameVar VARCHAR(1000) default NULL; count_loop: LOOP SELECT parent_id INTO parentIdVar FROM Category_parent WHERE Category_id = parentIdVar; SET auxLoopVar = auxLoopVar + 1; IF auxLoopVar >= generationPar THEN LEAVE count_loop; END IF; END LOOP; SELECT name INTO nameVar FROM Category WHERE id = parentIdVar; RETURN nameVar; END; $$ DELIMITER ;

如果您测试给定的函数，则假设 CAT10 的 Category_Id = 10

assuming that Category_Id for CAT10 = 10 if you test the function given

select getNameIdGeneration(10, 2);

CAT3

现在您只需要一个表格，其中包含您想知道其血统的 CAT 的 id

Now all you need is a table which contains the id of the CAT you want to know its lineage

MariaDB [mydatabase]> select * from test; +-------------+------------+ | category_id | generation | +-------------+------------+ | 10 | 1 | | 10 | 2 | | 10 | 3 | +-------------+------------+ MariaDB [mydatabase]> select generation, getNameIdGeneration(category_id, generation) as Name from test; +------------+------+ | generation | Name | +------------+------+ | 1 | CAT5 | | 2 | CAT3 | | 3 | CAT1 | +------------+------+ 3 rows in set (0.00 sec)