从MySQL的表中获取所有的祖先/超类型的后代/子类型

Question

我有一个分层数据表，看起来像这样:

tbody> <<tr>金属金属

ObjectType	ObjectSubType
一切	固体
固体	固体
固体	金属
	金属
	铁
	铁

没有使用递归解决这个问题，但设法使用WHILE循环和临时表来获取所有祖先:

DELIMITER $$
CREATE PROCEDURE get_ancestors(IN value VARCHAR(50))
BEGIN
-- create a temporary table to hold the ancestors, once and truncate it
CREATE TABLE IF NOT EXISTS ancestors (
ObjectType VARCHAR(50) NOT NULL
);
TRUNCATE TABLE ancestors;
-- insert the initial value into the ancestors table
INSERT INTO ancestors (ObjectType) VALUES (value);

-- Loop until no more ancestors are found
SET @new_rows = 1;
SET @iterations = 0;
SET @max_iterations = 100;
WHILE (@new_rows > 0) AND (@iterations < @max_iterations) DO
INSERT INTO ancestors (ObjectType)
SELECT DISTINCT ObjectType
FROM ObjectTypeHierarchyTable
WHERE ObjectSubType IN (SELECT ObjectType FROM ancestors) 
AND ObjectType NOT IN (SELECT ObjectType FROM ancestors);

SET @new_rows = ROW_COUNT();
SET @iterations = @iterations + 1;
END WHILE;
-- select the ancestors from the temporary table
SELECT * FROM ancestors;
END$$
DELIMITER ;
CALL get_ancestors('Metal');

过程也被用来包装WHILE语句，因为WHILE语句不能直接使用。

可选的TEMPORARY关键字可以在创建ancestors表时使用，这取决于MySQL的类型和版本(它在PyMySQL中工作，这是我的用例)。