CREATE DEFINER = 'root'@'localhost'PROCEDURE test.GetHierarchyUsers(IN StartKey INT)BEGIN -- prepare a hierarchy level variable SET @hierlevel := 00000; -- prepare a variable for total rows so we know when no more rows found SET @lastRowCount := 0; -- pre-drop temp table DROP TABLE IF EXISTS MyHierarchy; -- now, create it as the first level you want... -- ie: a specific top level of all "no parent" entries -- or parameterize the function and ask for a specific "ID". -- add extra column as flag for next set of ID's to load into this. CREATE TABLE MyHierarchy AS SELECT U.ID , U.Parent , U.`name` , 00 AS IDHierLevel , 00 AS AlreadyProcessed FROM Users U WHERe U.ID = StartKey; -- how many rows are we starting with at this tier level -- START the cycle, only IF we found rows... SET @lastRowCount := FOUND_ROWS(); -- we need to have a "key" for updates to be applied against, -- otherwise our UPDATE statement will nag about an unsafe update command CREATE INDEX MyHier_Idx1 ON MyHierarchy (IDHierLevel); -- NOW, keep cycling through until we get no more records WHILE @lastRowCount > 0 DO UPDATE MyHierarchy SET AlreadyProcessed = 1 WHERe IDHierLevel = @hierLevel; -- NOW, load in all entries found from full-set NOT already processed INSERT INTO MyHierarchy SELECt DISTINCT U.ID , U.Parent , U.`name` , @hierLevel + 1 AS IDHierLevel , 0 AS AlreadyProcessed FROM MyHierarchy mh JOIN Users U ON mh.Parent = U.ID WHERe mh.IDHierLevel = @hierLevel; -- preserve latest count of records accounted for from above query -- now, how many acrual rows DID we insert from the select query SET @lastRowCount := ROW_COUNT(); -- only mark the LOWER level we just joined against as processed, -- and NOT the new records we just inserted UPDATE MyHierarchy SET AlreadyProcessed = 1 WHERe IDHierLevel = @hierLevel; -- now, update the hierarchy level SET @hierLevel := @hierLevel + 1; END WHILE; -- return the final set now SELECT * FROM MyHierarchy;-- and we can clean-up after the query of data has been selected / returned.-- drop table if exists MyHierarchy;END
看起来很麻烦，但是要使用它，
call GetHierarchyUsers( 5 );
（或您要在层次树中查找的任何密钥ID）。
前提是从您正在使用的一个钥匙开始。然后，将其用作加入用户表AGAIN的基础，但要基于第一个条目的PARENT
ID。找到后，更新临时表，以免在下一个周期再次尝试加入该键。然后继续操作，直到找不到更多的“父” ID密钥为止。
无论嵌套的深度如何，这都会将整个记录层次结构返回到父级。但是，如果只需要FINAL父级，则可以使用@hierlevel变量仅返回所添加文件中的最后一个，或者使用ORDER
BY和LIMIT 1。
