递归mysql选择？

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。