尽管您无法使用单个查询进行操作，但是可以使用存储过程进行操作…唯一的先决条件是，您需要在现有示例表中再添加2条记录，以表示“ C1”和“ C2”
ARE顶层…添加一条记录，其中“父级”字段为空白，子级为“ C1”，另一级为“
C2”。这将“准备”最上层的父级。进行后续层次结构关联，否则您将没有顶层层次结构的起始“基础”。它还需要一个“主键”列（我在此脚本中将其创建为“
IDMyTable”，它只是1-x顺序的，但是假设您在表上有一个自动增量列可以代替使用）。

我已经包括了所有输出列以显示其构建方式，但是此例程的前提是根据预期的列输出创建表，但还要额外保留一些层次表示形式，以供构建时使用。为了确保它们在层变得更深时保持正确的方向，我在“
ID”列中加上了“ ID”列-您将看到它在最终结果集中的工作方式。

然后，在最终结果集中，我将根据层次结构数据的深度填充空白。

循环将基于在上一个结果集中找到的父记录添加任何记录，但前提是尚未添加ID（防止重复记录）…

要查看循环顺序是如何不断添加的，您可以运行不带该顺序的最后一个查询，并查看每个迭代如何限定条件并添加上一个层次结构级别…

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetHierarchy2`()
BEGIN
    -- prepare a hierarchy level variable 
    set @hierlvl := 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 
            t1.IDMyTable,
            t1.Child AS Parent,
            @hierlvl as IDHierLevel,
            cast( t1.IDMyTable as char(100)) FullHierarchy
        from
            MyTable t1
        where
                t1.Parent is null
            OR t1.Parent = '';


    -- how many rows are we starting with at this tier level
    set @lastRowCount := ROW_COUNT();

    -- we need to have a "primary key", otherwise our UPDATE
    -- statement will nag about an unsafe update command
    alter table MyHierarchy add primary key (IDMyTable);


    -- NOW, keep cycling through until we get no more records
    while @lastRowCount > 0 do

        -- NOW, load in all entries found from full-set NOT already processed
        insert into MyHierarchy
            select 
                    t1.IDMyTable,
                    t1.Child as Parent,
                    h1.IDHierLevel +1 as IDHierLevel,
                    concat_ws( ',', h1.FullHierarchy, t1.IDMyTable ) as FullHierarchy
                from
                    MyTable t1
                        join MyHierarchy h1
                            on t1.Parent = h1.Parent
                    left join
                        MyHierarchy h2
                            on t1.IDMyTable = h2.IDMyTable
                where
                    h2.IDMyTable is null;


        set @lastRowCount := row_count();

        -- now, update the hierarchy level
        set @hierLevel := @hierLevel +1;

    end while;


    -- return the final set now
    select 
            *, concat( lpad( ' ', 1 + (IDHierLevel * 3 ), ' ' ), Parent ) as ShowHierarchy
        from MyHierarchy
        order by FullHierarchy;

END