导入 500 000 个类别，其中包含左右嵌套集模型

不要让任何人拉扯你的链子。对于相对稳定的数据，嵌套集远远优于邻接列表。这是我用来将 800k+ 行 AL 转换为 NS 的脚本。它通常在 6-8 分钟内运行。它最初是用 T-SQL 编写的，我进行了一些更改，使其(或多或少(适用于您展示的架构。它需要工作才能在MySql中运行。在任何情况下，都不需要函数或存储过程。您需要的一切都可以直接从输入表中提取。

我对您的设计进行了两项更改。首先，您的示例数据看起来具有非常好的标识值，因此我使用它而不是 Identity 字段。其次，我添加了 SetID 列来维护每个集。因此，完成的数据将如下所示：

id      SetID  Name        lft rgt
381773  Arts   Arts        0   11
423945  Arts   Animation   1   6
425085  Arts   Anime       2   5
425147  Arts   Character   3   4
132456  Arts   2D          7   10
123457  Arts   Painting    8   9
381773  Crafts Crafts      0   7
423945  Crafts Clay        1   6
425085  Crafts Structures  2   3
425147  Crafts Pottery     4   5

整个表不应该定义一棵树，而应该定义一片树林(我称它们为"集合"，以符合"嵌套集"命名法(，边界编号在每个集合中是唯一的。每个集的每个子集都有用于分组的集标识符。边界编号在每个集的开头重新开始，因此每个集根的左边界值将为零。

代码如下。请记住，这更像是一个模板，而不是最终代码。提问。

CREATE TABLE IF NOT EXISTS nested_category(
    ID      INT not null PRIMARY KEY,
    SetID   varchar( 20 ) not null,
    Name    VARCHAR( 20 ) NOT NULL,
    lft     INT NOT NULL,
    rgt     INT NOT NULL
);
-- This was originally written in T-SQL so some syntax modification will
-- be in order. Sorry.
declare
    @CatIDNew       int,
    @CatIDPrev      int = 0,
    @rgt            int,
    @SetID          varchar( 20 ),
    @NameNew        varchar( 20 ),
    @LevelNew       int,
    @LevelPrev      int = 0;
declare
    @Defs table(
        Lev     int,
        ID      int,
        Name    varchar( 20 ),
        lft     int
    );
declare SS cursor fast_forward for
    select  Category_ID, Category_Name, Length( Path ) - Length( Replace( Path, '/', '' )) as Category_Level
    from    tbl_categories
    --where   Category_Path like 'Top/Arts%' )  -- Uncomment to test a smaller sample
    order by Category_Path;
open ss;
fetch next from ss into @CatIDNew, @NameNew, @LevelNew;
-- Go ahead and store the first record of the table
insert into @Defs values( @LevelNew, @CatIDNew, @NameNew, 0 );
select  @LevelPrev  = @LevelNew,
        @CatIDPrev  = @CatIDNew,
        @SetID      = @NameNew,
        @rgt        = 1;
-- Get the second row
fetch next from ss into @CatIDNew, @NameNew, @LevelNew;
-- Now we enter the row with the first entry in the table variable. We're using this table as a stack.
-- As we read entries from the cursor, we push them "onto" the stack until we read an entry the same
-- or smaller level than the previous entry.
while @@FETCH_STATUS = 0 begin
        if @LevelNew = 1 begin
            -- Handle the special case when transitioning between sets
            insert into nested_category( ID, SetID, Name, lft, rgt )
            select  ID, @SetID, Name, lft, @rgt +( @LevelPrev - Lev )
            from    @Defs
            where   Lev <= @LevelPrev
            order by Lev desc;
            -- Clear the stack
            delete @Defs;
            insert @Defs values( @LevelNew, @CatIDNew, @NameNew, 0 );
            select  @LevelPrev  = @LevelNew,
                    @CatIDPrev  = @CatIDNew,
                    @SetID      = @NameNew,
                    @rgt        = 1;
        end
        else if @LevelNew > @LevelPrev begin
            -- This new standard is the next level down from the last one. Save the last one.
            Merge into @Defs d
                using (select @LevelNew) as Search( Lev )
                    on d.Lev = Search.Lev
                when matched then
                    update  set ID      = @CatIDNew,
                                Name    = @NameNew,
                                lft     = @rgt
                when not matched then
                    insert values( @LevelNew, @CatIDNew, @NameNew, @rgt );
            select  @LevelPrev  = @LevelNew,
                    @rgt     = @rgt + 1;
        end
        else begin
            -- This is on the same or higher level as the last one. We can write out the last one to the new level.
            insert into nested_category( ID, SetID, Name, lft, rgt )
            select  ID, @SetID, Name, lft, @rgt +( @LevelPrev - Lev )
            from    @Defs
            where   Lev between @LevelNew and @LevelPrev;
            -- Then insert/replace the new one (in case the next one is lower)
            select  @LevelPrev  = @LevelNew,
                    @CatIDPrev  = @CatIDNew,
                    @rgt = @rgt + @@ROWCOUNT;
            Merge into @Defs d
                using (select @LevelNew) as Search( Lev )
                    on d.Lev = Search.Lev
                when matched then
                    update  set ID      = @NameNew,
                                Name    = @NameNew,
                                lft     = @rgt
                when not matched then
                    insert values( @LevelNew, @CatIDNew, @NameNew, @rgt );
            set @rgt = @rgt + 1;
        end;
    -- Read in the next row
    fetch next from ss into @CatIDNew, @NameNew, @LevelNew;
end;
-- There are no more entries. Purge any remaining in the stack.
insert into nested_category( ID, SetID, Name, lft, rgt )
select  ID, @SetID, Name, lft, @rgt +( @LevelPrev - Lev )
from    @Defs
where   Lev <= @LevelPrev;
close ss;
deallocate ss;
-- Saving these definitions until after the table is loaded saves a lot of time.
alter table nested_category add
    constraint  PK_nested_category primary key clustered( ID ),
    constraint  IX_nested_category_lft unique( SetID, lft ),
    constraint  CK_nested_categoryBounds check( lft < rgt );
alter table nested_category add
    constraint  FK_nested_categoryOrgID foreign key( SetID )references StandardOrgs( SetID ),
    constraint  FK_nested_categoryGUID foreign key( ID )references StandardGuidXref ( EDID ),
    constraint  FK_nested_categorySubject foreign key( SubjectID )references StandardSubjects ( ID ),
    constraint  FK_nested_categoryLevel foreign key( SetID, LevelID )references StandardLevels ( SetID, LevelID );
create index    IX_nested_categoryBounds on nested_category ( SetID, lft, rgt ); -- oft-used covering index