MySQL의 계층 데이터에서 깊이 기반 트리 생성(CTE 없음)

MySQL의 계층 데이터에서 깊이 기반 트리 생성(CTE 없음)

안녕하세요. 며칠동안 MySQL에서 이 문제에 대해 연구해 왔지만 알 수가 없습니다.제안해 주실 분 계신가요?

기본적으로 다음과 같은 도메인이 있는 카테고리 테이블이 있습니다.id,name(범주명) 및parent(범주의 부모 ID).

예제 데이터:

1  Fruit        0
2  Apple        1
3  pear         1
4  FujiApple    2
5  AusApple     2
6  SydneyAPPLE  5
....

여러 가지 레벨이 있습니다. 아마도 3개 이상의 레벨이 있을 것입니다.계층에 따라 데이터를 그룹화하는 sql 쿼리를 만들고 싶습니다: parent > child > grandchild > 등.

다음과 같이 트리 구조를 출력해야 합니다.

1 Fruit 0
 ^ 2 Apple 1
   ^ 4 FujiApple 2
   - 5 AusApple 2
     ^ 6 SydneyApple 5
 - 3 pear 1

단일 SQL 쿼리를 사용하여 이 작업을 수행할 수 있습니까?제가 시도해보고 노력한 대안은 다음과 같습니다.

SELECT * FROM category WHERE parent=0

이 후 데이터를 다시 루프하고 parent=id가 있는 행을 선택합니다.이것은 좋지 않은 해결책인 것 같습니다.mySQL이기 때문에 CTE는 사용할 수 없습니다.

저장 프로시저를 사용하면 php에서 mysql로 한번의 호출로 할 수 있습니다.

호출 예제

mysql> call category_hier(1);

+--------+---------------+---------------+----------------------+-------+
| cat_id | category_name | parent_cat_id | parent_category_name | depth |
+--------+---------------+---------------+----------------------+-------+
|      1 | Location      |          NULL | NULL                 |     0 |
|      3 | USA           |             1 | Location             |     1 |
|      4 | Illinois      |             3 | USA                  |     2 |
|      5 | Chicago       |             3 | USA                  |     2 |
+--------+---------------+---------------+----------------------+-------+
4 rows in set (0.00 sec)


$sql = sprintf("call category_hier(%d)", $id);

도움이 되길 바라요 :)

전체대본

테스트 테이블 구조:

drop table if exists categories;
create table categories
(
cat_id smallint unsigned not null auto_increment primary key,
name varchar(255) not null,
parent_cat_id smallint unsigned null,
key (parent_cat_id)
)
engine = innodb;

테스트 데이터:

insert into categories (name, parent_cat_id) values
('Location',null),
   ('USA',1), 
      ('Illinois',2), 
      ('Chicago',2),  
('Color',null), 
   ('Black',3), 
   ('Red',3);

절차:

drop procedure if exists category_hier;

delimiter #

create procedure category_hier
(
in p_cat_id smallint unsigned
)
begin

declare v_done tinyint unsigned default 0;
declare v_depth smallint unsigned default 0;

create temporary table hier(
 parent_cat_id smallint unsigned, 
 cat_id smallint unsigned, 
 depth smallint unsigned default 0
)engine = memory;

insert into hier select parent_cat_id, cat_id, v_depth from categories where cat_id = p_cat_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table tmp engine=memory select * from hier;

while not v_done do

    if exists( select 1 from categories p inner join hier on p.parent_cat_id = hier.cat_id and hier.depth = v_depth) then

        insert into hier 
            select p.parent_cat_id, p.cat_id, v_depth + 1 from categories p 
            inner join tmp on p.parent_cat_id = tmp.cat_id and tmp.depth = v_depth;

        set v_depth = v_depth + 1;          

        truncate table tmp;
        insert into tmp select * from hier where depth = v_depth;

    else
        set v_done = 1;
    end if;

end while;

select 
 p.cat_id,
 p.name as category_name,
 b.cat_id as parent_cat_id,
 b.name as parent_category_name,
 hier.depth
from 
 hier
inner join categories p on hier.cat_id = p.cat_id
left outer join categories b on hier.parent_cat_id = b.cat_id
order by
 hier.depth, hier.cat_id;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end #

테스트 실행:

delimiter ;

call category_hier(1);

call category_hier(2);

야후 지오플래닛을 이용한 일부 성능 테스트는 데이터를 배치합니다.

drop table if exists geoplanet_places;
create table geoplanet_places
(
woe_id int unsigned not null,
iso_code  varchar(3) not null,
name varchar(255) not null,
lang varchar(8) not null,
place_type varchar(32) not null,
parent_woe_id int unsigned not null,
primary key (woe_id),
key (parent_woe_id)
)
engine=innodb;

mysql> select count(*) from geoplanet_places;
+----------+
| count(*) |
+----------+
|  5653967 |
+----------+

표의 560만 행(장소)입니다. php에서 호출된 인접 목록 구현/실행 절차가 어떻게 처리되는지 살펴보겠습니다.

     1 records fetched with max depth 0 in 0.001921 secs
   250 records fetched with max depth 1 in 0.004883 secs
   515 records fetched with max depth 1 in 0.006552 secs
   822 records fetched with max depth 1 in 0.009568 secs
   918 records fetched with max depth 1 in 0.009689 secs
  1346 records fetched with max depth 1 in 0.040453 secs
  5901 records fetched with max depth 2 in 0.219246 secs
  6817 records fetched with max depth 1 in 0.152841 secs
  8621 records fetched with max depth 3 in 0.096665 secs
 18098 records fetched with max depth 3 in 0.580223 secs
238007 records fetched with max depth 4 in 2.003213 secs

전반적으로 저는 이러한 콜드 런 타임에 매우 만족합니다. 수만 행의 데이터를 프론트 엔드에 반환할 생각도 하지 않고, 한 통화당 몇 단계씩만 가져와 트리를 동적으로 구축하는 것이 좋습니다.아 그리고 혹시 nodb가 내 isam보다 느리다고 생각하는 경우를 대비해서 내가 테스트한 myisam 구현은 모든 카운트에서 두 배나 느렸습니다.

여기 더 많은 것들 : http://pastie.org/1672733

도움이 되길 바라요 :)

RDBMS에 계층 데이터를 저장하는 일반적인 방법에는 인접 목록(사용 중인)과 중첩 집합(nested set)의 두 가지가 있습니다.MySQL의 계층 데이터 관리에는 이러한 대안에 대한 내용이 매우 잘 기록되어 있습니다.중첩 집합 모델을 사용하여 단일 쿼리에서만 원하는 작업을 수행할 수 있습니다.그러나 중첩 집합 모형을 사용하면 계층 구조를 업데이트하는 작업이 많아지므로 운영 요구 사항에 따라 절충을 고려해야 합니다.

단일 쿼리로는 이 작업을 수행할 수 없습니다.이 경우 계층적 데이터 모형은 효과가 없습니다.데이터베이스에 계층 데이터를 저장하는 다른 두 가지 방법, 즉 MPTT 모델 또는 "Lineage" 모델을 사용해 보는 것이 좋습니다.이 두 가지 모델 중 하나를 사용하면 원하는 선택을 한 번에 할 수 있습니다.

자세한 내용은 다음과 같습니다. http://articles.sitepoint.com/article/hierarchical-data-database

선형 방식:

나는 간단한 문자열 필드에 트리를 만들기 위해 못생긴 기능을 사용하고 있습니다.

/              topic title
/001           message 1
/002           message 2
/002/001       reply to message 2
/002/001/001/  reply to reply
/003           message 3
etc...

테이블은 간단한 SQL 쿼리로 트리 순서의 모든 행을 선택하는 데 사용할 수 있습니다.

select * from morum_messages where m_topic=1234 order by m_linear asc

INSERT는 단지 부모 선형(및 자식)을 선택하고 필요에 따라 문자열을 계산합니다.

select M_LINEAR FROM forum_messages WHERE m_topic = 1234 and M_LINEAR LIKE '{0}/___' ORDER BY M_LINEAR DESC limit 0,1  
/* {0} - m_linear of the parent message*/

DELETE메시지를 삭제하거나 부모 응답의 모든 응답을 선형으로 삭제하는 것은 간단합니다.

언급URL : https://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes