mysql层次结构自连接，检索所有子类别 [英] mysql hierarchy self-join, retrieve all subcategories

问题描述

我的表categories包含以下列:

category_id
category_name
parent_id

我需要获取给定主要类别在所有级别的所有子类别的列表，因此，例如，如果我给出某些lvl 3类别的ID，我将获得所有lvl 4、5、6 ...类别的列表是那个lvl 3类别的孩子.

I need to get list of all subcategories on all levels for a given main category, so if for example I give id of some lvl 3 category I would get back list of all lvl 4, 5, 6... categories that are children of that one lvl 3 category.

无需保留层次结构，只需保留一个简单列表即可.

No hierarchy needs to be preserved, just a simple list.

我首先想到只通过几个连接和子查询来完成此操作，但是我认为之后类别会更深入，所以这不是可行的方法.

I first thought about just doing it with several joins and subqueries but than I figured categories will bee much deeper afterwards so that's not a way to go.

自从我刚开始使用SQL以来，我仍然不知道如何编写递归查询，因此这将是一个很好的帮助和学习材料.

Since I've just started SQL I still don't know how to write recursive queries so this would be a great help and learning material.

推荐答案

首先阅读底部的Please Note.好的，您回来了.

Read Please Note at bottom first. Ok good, you are back.

为类似递归的层次结构检索创建存储过程.

Creation of a Stored Procedure for recursive-like hierarchy retrieval.

请注意，您不希望按级别进行操作，但这很容易做到.

Note, you didn't want it by levels but that can easily be done.

create table category
(   category_id int not null auto_increment primary key,
    category_name varchar(40) not null,
    parent_id int null,  -- index on this column not a shabby idea
    unique key (category_name)
);

insert category(category_name,parent_id) values ('car',null),('food',null); -- 1,2
insert category(category_name,parent_id) values ('ford',1),('chevy',1),('fruit',2); -- 3,4,5
insert category(category_name,parent_id) values ('economy',3),('escort',6),('exhaust',7); -- 6,7,8
insert category(category_name,parent_id) values ('chassis',7),('loud',8),('banana',5); -- 9,10,11
-- ok granted I could have explicity inserted category_id to make it more obvious

创建存储过程:

-- drop procedure showHierarchyBelow;
delimiter $$
create procedure showHierarchyBelow
(
catname varchar(40)
)
BEGIN
    -- deleteMe parameter means i am anywhere in hierarchy of role
    -- and i want me and all my offspring deleted (no orphaning of children or theirs)
    declare bDoneYet boolean default false;
    declare working_on int;
    declare theCount int;
    declare findFirst int;

    select ifnull(category_id,0) into findFirst from category where category_name=catname;

    CREATE TABLE xx_RecursishHelper_xx
    (   -- it's recurshish, not recursive
        category_id int not null,
        processed int not null
    );
    if isnull(findFirst) then
        set findFirst=0;
    end if;
    insert into xx_RecursishHelper_xx (category_id,processed) select findFirst,0;
    if (findFirst=0) then
        set bDoneYet=true;
    else
        set bDoneYet=false;
    end if;

    while (!bDoneYet) do
        -- I am not proud of this next line, but oh well
        select count(*) into theCount from xx_RecursishHelper_xx where processed=0;

        if (theCount=0) then 
            -- found em all
            set bDoneYet=true;
        else
            -- one not processed yet, insert its children for processing
            SELECT category_id INTO working_on FROM xx_RecursishHelper_xx where processed=0 limit 1;
            insert into xx_RecursishHelper_xx (category_id,processed)
            select category_id,0 from category
            where parent_id=working_on;

            -- mark the one we "processed for children" as processed
            update xx_RecursishHelper_xx set processed=1 where category_id=working_on;
        end if;
    end while;

    delete from xx_RecursishHelper_xx where category_id=findFirst;

    select x.category_id,c.category_name
    from xx_RecursishHelper_xx x
    join category c
    on c.category_id=x.category_id;

    drop table xx_RecursishHelper_xx;
END
$$

测试存储过程:

call showHierarchyBelow('food');
+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
|           5 | fruit         |
|          11 | banana        |
+-------------+---------------+

call showHierarchyBelow('car');
+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
|           3 | ford          |
|           4 | chevy         |
|           6 | economy       |
|           7 | escort        |
|           8 | exhaust       |
|           9 | chassis       |
|          10 | loud          |
+-------------+---------------+

call showHierarchyBelow('ford');
+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
|           6 | economy       |
|           7 | escort        |
|           8 | exhaust       |
|           9 | chassis       |
|          10 | loud          |
+-------------+---------------+

call showHierarchyBelow('xxx');
-- no rows

请注意，我只是几个月前修改了我的答案，以满足您的需求.

Note I merely modified this Answer of mine from a few months ago for your needs.

以上内容仅用于说明目的.在现实世界中，我永远不会在存储的proc中创建表. DDL开销很大.相反，我将使用具有会话概念的预先存在的非临时表.并在完成会话的行中将其清除.因此，不要将上述内容当作一个稻草人，等着您使它表现得更好.询问是否令人困惑.

The above is for illustrative purposes only. In a real world situation, I would never do create tables in a stored proc. The DDL overhead is significant. Instead, I would use pre-existing non temp tables with a session concept. And clean it out of rows for the session done. So do not take the above as any more than a straw man, waiting for you to make it more performant as such. Ask if that is confusing.

这篇关于mysql层次结构自连接，检索所有子类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！