원문 : http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

번역 : 구글번역

소개(Intorduction)

한 번에 또는 대부분의 사용자는 SQL 데이터베이스에서 계층 적 데이터를 처리했으며 계층 적 데이터의 관리는 관계형 데이터베이스의 의도와 다르다는 것을 알게되었습니다. 관계형 데이터베이스의 테이블은 XML과 같은 계층 적 구조는 아니지만 단순한 플랫 목록입니다. 계층 적 데이터는 관계형 데이터베이스 테이블에서 자연스럽게 표현되지 않는 부모 - 자식 관계를가집니다.

우리의 목적을 위해 계층 적 데이터는 각 항목이 하나의 부모와 0 개 이상의 자식을 가진 데이터 모음입니다 (상위 항목이없는 루트 항목 제외). 계층 적 데이터는 포럼 및 메일 목록 스레드, 비즈니스 조직도, 콘텐츠 관리 범주 및 제품 범주를 비롯한 다양한 데이터베이스 응용 프로그램에서 찾을 수 있습니다. 우리의 목적을 위해 가상의 전자 제품 상점에서 다음과 같은 제품 카테고리 계층 구조를 사용합니다.

이 범주는 위에 인용 된 다른 예제와 동일한 방식으로 계층 구조를 형성합니다. 이 기사에서는 전통적인 adjacency list 모델부터 시작하여 MySQL의 계층 적 데이터를 다루는 두 가지 모델을 살펴 보겠습니다.

인접 목록 모델(The Adjacency List Model)

일반적으로 위에 표시된 예제 카테고리는 다음과 같은 테이블에 저장됩니다 (전체 CREATE 및 INSERT 문을 포함하므로 따라갈 수 있습니다).

CREATE TABLE category(
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        parent INT DEFAULT NULL
);

INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
        (4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),
        (9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

SELECT * FROM category ORDER BY category_id;
+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)

인접성 목록 모델에서 테이블의 각 항목에는 상위 항목에 대한 포인터가 포함됩니다. 최상위 요소 (이 경우 electronics)는 상위 요소에 대해 NULL 값을가집니다. 인접 목록 모델은 아주 간단하다는 장점이 있으며, FLASH가 mp3 플레이어의 하위 항목 인 전자 제품의 하위 항목 인 휴대용 전자 제품의 하위 항목임을 쉽게 알 수 있습니다. 인접 목록 모델은 클라이언트 측 코드에서 상당히 쉽게 처리 할 수 있지만 모델로 작업하는 것은 순수 SQL에서 더 문제가 될 수 있습니다.

풀 트리 검색하기(RETRIEVING A FULL TREE)

계층 적 데이터를 처리 할 때 첫 번째로 공통적으로 수행해야 할 작업은 일반적으로 들여 쓰기의 형태로 전체 트리를 표시하는 것입니다. 이 작업을 수행하는 가장 일반적인 방법은 순수한 SQL에서 자체 조인을 사용하는 것입니다.

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)

모든 잎사귀 찾기(FINDING ALL THE LEAF NODES)

LEFT JOIN 쿼리를 사용하여 트리에있는 모든 리프 노드 (자식이없는 노드)를 찾을 수 있습니다.

SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

단일 경로 검색(RETRIEVING A SINGLE PATH)

자체 조인은 또한 우리가 계층 구조를 통해 완전한 경로를 볼 수있게 해줍니다 :

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';

+-------------+----------------------+-------------+-------+
| lev1        | lev2                 | lev3        | lev4  |
+-------------+----------------------+-------------+-------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
+-------------+----------------------+-------------+-------+
1 row in set (0.01 sec)

이러한 접근 방식의 주된 한계는 계층 구조의 모든 수준에 대해 하나의 자체 조인이 필요하며 조인이 복잡해지면서 각 수준이 추가 될 때마다 성능이 자연스럽게 저하된다는 것입니다.

부작용 목록 모델의 제한(LIMITATIONS OF THE ADJACENCY LIST MODEL)

순수 SQL에서 인접성 목록 모델로 작업하는 것은 기껏해야 어려울 수 있습니다. 카테고리의 전체 경로를 표시되기 전에, 우리는 존재하는 레벨을 알 수있다. 그 과정에서 전체 서브 트리를 orphaning에 대한 때문에 가능성의 노드를 삭제하는 경우 또한, 특별한주의는 (휴대용 전자 제품 카테고리와 분리되어 자식을 모두 삭제)주의해야합니다. 이러한 제한 중 일부는 클라이언트 측 코드 나 저장 프로 시저를 사용하여 해결할 수 있습니다. 절차 언어로 우리는 나무의 아래에서 시작하여 전체 트리 또는 단일 경로를 반환 위쪽으로 반복 할 수 있습니다. 프로 시저 프로그래밍을 사용하여 하나의 자식 요소를 승격시키고 나머지 자식을 새로운 부모를 가리 키도록 다시 정렬함으로써 전체 하위 트리를 고아없이 노드를 삭제할 수도 있습니다.

중첩 세트 모델(The Nested Set Model)

이 기사에서 중점을두고 자하는 것은 일반적으로 중첩 세트 모델이라고하는 다른 접근 방식입니다. 중첩 세트 모델에서는 노드와 라인이 아닌 중첩 된 컨테이너로 새로운 방식으로 계층 구조를 볼 수 있습니다. 다음과 같이 전자 제품 범주를 묘사 해보십시오.

부모 범주가 자녀를 감싸고 있기 때문에 우리 계층 구조가 어떻게 유지되고 있는지 주목하십시오. 우리는 노드의 중첩을 나타 내기 위해 왼쪽 및 오른쪽 값을 사용하여 테이블에서이 계층 구조를 나타냅니다.

CREATE TABLE nested_category (
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        lft INT NOT NULL,
        rgt INT NOT NULL
);

INSERT INTO nested_category VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
 (4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
 (9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);

SELECT * FROM nested_category ORDER BY category_id;

+-------------+----------------------+-----+-----+
| category_id | name                 | lft | rgt |
+-------------+----------------------+-----+-----+
|           1 | ELECTRONICS          |   1 |  20 |
|           2 | TELEVISIONS          |   2 |   9 |
|           3 | TUBE                 |   3 |   4 |
|           4 | LCD                  |   5 |   6 |
|           5 | PLASMA               |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |
|           7 | MP3 PLAYERS          |  11 |  14 |
|           8 | FLASH                |  12 |  13 |
|           9 | CD PLAYERS           |  15 |  16 |
|          10 | 2 WAY RADIOS         |  17 |  18 |
+-------------+----------------------+-----+-----+

왼쪽과 오른쪽은 MySQL에서 예약어이기 때문에 lft와 rgt를 사용합니다. 예약어 전체 목록은 http://dev.mysql.com/doc/mysql/en/reserved-words.html을 참조하십시오.

그렇다면 우리는 어떻게 좌우 값을 결정합니까? 바깥 쪽 노드의 가장 왼쪽에 번호를 매기고 오른쪽으로 계속합니다.

이 디자인은 일반적인 트리에도 적용 할 수 있습니다.

트리를 사용하여 작업 할 때 우리는 왼쪽에서 오른쪽으로 한 번에 한 레이어 씩 작업하고 각 노드의 하위 노드로 내림차순 번호를 할당하고 오른쪽으로 이동합니다. 이 접근법을 수정 된 선주문 트리 순회 알고리즘이라고합니다.

풀 트리 검색하기(RETRIEVING A FULL TREE)

우리는 노드의 lft 값이 부모의 lft와 rgt 값 사이에 항상 존재한다는 것을 기반으로 부모를 노드와 연결하는 자체 조인을 사용하여 전체 트리를 검색 할 수 있습니다.

SELECT node.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;

+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| TELEVISIONS          |
| TUBE                 |
| LCD                  |
| PLASMA               |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
| CD PLAYERS           |
| 2 WAY RADIOS         |
+----------------------+

인접성 목록 모델을 사용한 이전 예제와 달리이 쿼리는 트리의 깊이에 관계없이 작동합니다. rgt 값은 항상 lft 값과 같은 부모 내에 속하기 때문에 우리는 BETWEEN 절의 노드의 rgt 값에 신경 쓰지 않습니다.

모든 잎사귀 찾기(FINDING ALL THE LEAF NODES)

인접 목록 모델에서 사용 된 LEFT JOIN 방법보다 훨씬 단순한 중첩 된 집합 모델의 모든 리프 노드 찾기. nested_category 테이블을 보면 리프 노드의 lft 및 rgt 값이 연속 된 숫자임을 알 수 있습니다. 리프 노드를 찾기 위해 rgt = lft + 1 인 노드를 찾는다.

SELECT name
FROM nested_category
WHERE rgt = lft + 1;

+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

단일 경로 검색(RETRIEVING A SINGLE PATH)

중첩 된 집합 모델을 사용하면 여러자가 조인을 사용하지 않고도 단일 경로를 검색 할 수 있습니다.

SELECT parent.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'FLASH'
ORDER BY parent.lft;

+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
+----------------------+

NODES의 깊이 찾기(FINDING THE DEPTH OF THE NODES)

우리는 이미 전체 트리를 표시하는 방법을 살펴 보았지만 트리의 각 노드의 깊이를 표시하여 각 노드가 계층 구조에 어떻게 들어 맞는지 더 잘 식별하려는 경우 어떻게해야할까요? 이것은 전체 트리를 표시하기 위해 COUNT 함수와 GROUP BY 절을 기존 쿼리에 추가하여 수행 할 수 있습니다.

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| ELECTRONICS          |     0 |
| TELEVISIONS          |     1 |
| TUBE                 |     2 |
| LCD                  |     2 |
| PLASMA               |     2 |
| PORTABLE ELECTRONICS |     1 |
| MP3 PLAYERS          |     2 |
| FLASH                |     3 |
| CD PLAYERS           |     2 |
| 2 WAY RADIOS         |     2 |
+----------------------+-------+

depth 값을 사용하여 범주 이름을 CONCAT 및 REPEAT 문자열 함수로 들여 쓸 수 있습니다.

SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+

물론 클라이언트 측 응용 프로그램에서는 깊이 값을 직접 사용하여 계층 구조를 표시 할 가능성이 높습니다. 웹 개발자는 깊이 번호가 증가하거나 감소 할 때 <li> </ li> 및 <ul> </ ul> 태그를 추가하여 트리를 반복 할 수 있습니다.

서브 트리의 깊이(DEPTH OF A SUB-TREE)

하위 트리에 대한 깊이 정보가 필요하면 결과를 손상시킬 수 있으므로 자체 조인에서 노드 또는 부모 테이블을 제한 할 수 없습니다. 대신 세 번째 자체 조인을 하위 쿼리와 함께 추가하여 하위 트리의 새로운 시작점이 될 깊이를 결정합니다.

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;

+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| FLASH                |     2 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+

이 기능은 루트 노드를 포함한 모든 노드 이름과 함께 사용할 수 있습니다. 깊이 값은 항상 명명 된 노드를 기준으로합니다.

NODE의 즉각적인 소굴 찾기(FIND THE IMMEDIATE SUBORDINATES OF A NODE)

소매업 자 웹 사이트에서 전자 제품 범주를 보여주고 있다고 가정 해보십시오. 사용자가 카테고리를 클릭하면 해당 카테고리의 제품을 표시하고 그 바로 아래의 하위 카테고리는 나열하지만 하위 카테고리의 전체 트리는 나열하지 않을 수 있습니다. 이를 위해 우리는 노드와 노드의 즉각적인 하위 노드를 보여줄 필요가 있지만 트리를 더 내려 가지 않아야합니다. 예를 들어 휴대용 전자 제품 카테고리를 표시 할 때 MP3 플레이어, CD 플레이어 및 2 가지 라디오를 표시하려고하지만 플래시는 표시하지 않을 것입니다.

이는 이전 쿼리에 HAVING 절을 추가하여 쉽게 수행 할 수 있습니다.

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                        nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                        AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;

+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+

상위 노드를 표시하지 않으려면, HAVING depth <= 1 행을 HAVING depth = 1로 변경하십시오.

중첩 된 기능의 전체 기능(AGGREGATE FUNCTIONS IN A NESTED SET)

집계 함수를 설명하는 데 사용할 수있는 제품 표를 추가해 보겠습니다.

CREATE TABLE product
(
        product_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(40),
        category_id INT NOT NULL
);

INSERT INTO product(name, category_id) VALUES('20" TV',3),('36" TV',3),
('Super-LCD 42"',4),('Ultra-Plasma 62"',5),('Value Plasma 38"',5),
('Power-MP3 5gb',7),('Super-Player 1gb',8),('Porta CD',9),('CD To go!',9),
('Family Talk 360',10);

SELECT * FROM product;

+------------+-------------------+-------------+
| product_id | name              | category_id |
+------------+-------------------+-------------+
|          1 | 20" TV            |           3 |
|          2 | 36" TV            |           3 |
|          3 | Super-LCD 42"     |           4 |
|          4 | Ultra-Plasma 62"  |           5 |
|          5 | Value Plasma 38"  |           5 |
|          6 | Power-MP3 128mb   |           7 |
|          7 | Super-Shuffle 1gb |           8 |
|          8 | Porta CD          |           9 |
|          9 | CD To go!         |           9 |
|         10 | Family Talk 360   |          10 |
+------------+-------------------+-------------+

이제 각 카테고리의 제품 수와 함께 카테고리 트리를 검색 할 수있는 쿼리를 생성 해 보겠습니다.

SELECT parent.name, COUNT(product.name)
FROM nested_category AS node ,
        nested_category AS parent,
        product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.category_id = product.category_id
GROUP BY parent.name
ORDER BY node.lft;

+----------------------+---------------------+
| name                 | COUNT(product.name) |
+----------------------+---------------------+
| ELECTRONICS          |                  10 |
| TELEVISIONS          |                   5 |
| TUBE                 |                   2 |
| LCD                  |                   1 |
| PLASMA               |                   2 |
| PORTABLE ELECTRONICS |                   5 |
| MP3 PLAYERS          |                   2 |
| FLASH                |                   1 |
| CD PLAYERS           |                   2 |
| 2 WAY RADIOS         |                   1 |
+----------------------+---------------------+

이것은 COUNT 및 GROUP BY가 추가 된 일반적인 트리 전체 쿼리와 WHERE 절의 노드와 product 테이블 간의 조인 및 product 테이블에 대한 참조입니다. 보시다시피 각 카테고리에 대한 개수가 있으며 하위 카테고리의 수는 상위 카테고리에 반영됩니다.

새로운 노드 추가하기(ADDING NEW NODES)

이제 트리를 쿼리하는 방법을 배웠으므로 새 노드를 추가하여 트리를 업데이트하는 방법을 살펴보아야합니다. 중첩 된 집합 다이어그램을 다시 살펴 보겠습니다.

TELEVISIONS 노드와 PORTABLE ELECTRONICS 노드 사이에 새로운 노드를 추가하고자한다면, 새로운 노드는 lft와 rgt 값이 10과 11이 될 것이고, 오른쪽 노드는 lft와 rgt 값이 2만큼 증가 할 것입니다. 그런 다음 적절한 lft 및 rgt 값을 가진 새 노드를 추가합니다. 이 작업은 MySQL 5의 저장 프로 시저에서 수행 할 수 있지만 가장 안정적인 버전이므로 대부분의 독자가 4.1을 사용하고 있다고 가정하고 대신 LOCK TABLES 문으로 쿼리를 분리합니다.

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

우리는 들여 쓰기 트리 쿼리를 사용하여 중첩을 확인할 수 있습니다.

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  GAME CONSOLES        |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+

기존의 자식이없는 노드의 자식으로 노드를 추가하려면이 절차를 약간 수정해야합니다. 2 WAY RADIOS 노드 아래에 새 FRS 노드를 추가해 보겠습니다.

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft FROM nested_category

WHERE name = '2 WAY RADIOS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;

INSERT INTO nested_category(name, lft, rgt) VALUES('FRS', @myLeft + 1, @myLeft + 2);

UNLOCK TABLES;

이 예제에서 우리는 자랑스러운 새로운 부모 노드의 왼쪽 숫자의 오른쪽으로 모든 것을 확장 한 다음 노드를 왼쪽 값의 오른쪽에 배치합니다. 보시다시피 새 노드가 제대로 중첩되었습니다.

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  GAME CONSOLES        |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

노드 삭제(DELETING NODES)

중첩 된 세트를 사용하여 작업하는 마지막 기본 작업은 노드를 제거하는 것입니다. 노드를 삭제할 때 수행하는 과정은 계층 구조에서 노드의 위치에 따라 달라집니다. 분리 된 노드를 처리해야하기 때문에 리프 노드를 삭제하는 것이 자식 노드를 삭제하는 것보다 쉽습니다.

잎 노드를 삭제할 때, 새로운 노드를 추가하는 것과 정반대의 과정을 거치면 모든 노드에서 오른쪽으로 노드와 노드의 너비가 삭제됩니다.

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;

그리고 다시 한번 들여 쓰기 트리 쿼리를 실행하여 노드가 계층 구조를 손상시키지 않고 삭제되었음을 확인합니다.

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

이 접근법은 노드와 그 모든 자식 노드를 삭제하는 것과 똑같이 잘 작동합니다.

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'MP3 PLAYERS';

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;

다시 한번 전체 하위 트리를 성공적으로 삭제했는지 쿼리합니다.

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

우리가 처리해야하는 또 다른 시나리오는 부모 노드는 삭제하지만 자식 노드는 삭제하지 않는 것입니다. 어떤 경우에는 감독자가 해고 될 때와 같이 대체가 표시 될 때까지 이름을 자리 표시 자로 바꿀 수도 있습니다. 다른 경우, 자식 노드는 모두 삭제 된 부모 수준까지 이동해야합니다.

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'PORTABLE ELECTRONICS';

DELETE FROM nested_category WHERE lft = @myLeft;

UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight;

UNLOCK TABLES;

이 경우 노드의 오른쪽에있는 모든 요소에서 2를 뺍니다 (자식이 없으므로 너비가 2가되기 때문에). 자식 노드가 하나 인 노드에서 하나를 뺍니다 (부모의 왼쪽 손실로 인해 생성 된 간격을 닫습니다. 값). 다시 한번, 우리는 요소가 홍보되었음을 확인할 수 있습니다.

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+---------------+
| name          |
+---------------+
| ELECTRONICS   |
|  TELEVISIONS  |
|   TUBE        |
|   LCD         |
|   PLASMA      |
|  CD PLAYERS   |
|  2 WAY RADIOS |
|   FRS         |
+---------------+

노드를 삭제할 때의 다른 시나리오는 자식 노드 중 하나를 부모 위치로 승격시키고 부모 노드의 형제 노드 아래에 자식 노드를 이동시키는 것입니다. 그러나 공간을 위해이 시나리오는이 기사에서 다루지 않습니다.

최종 생각(Final Thoughts)

이 기사의 정보가 사용자에게 유용하기를 희망하지만 SQL에서 중첩 된 집합의 개념은 10 년 이상되어 왔으며 책과 인터넷에서 사용할 수있는 많은 추가 정보가 있습니다. 제 생각에 계층 적 정보를 관리하는 데있어 가장 포괄적 인 정보원은 고급 SQL, Joe Celko 분야에서 매우 존경받는 저자가 작성한 Joe Celko의 "Smarties 용 SQL의 Trees and Hierarchies"입니다. 조 셀코 (Joe Celko)는 중첩 된 세트 모델로 종종 공로를 인정 받았으며이 주제에 관한 가장 많은 저술가입니다. Celko의 저서가 저의 연구에서 귀중한 자원이라는 것을 알았습니다. 이 기사에서는이 기사에서 다루지 않은 고급 항목에 대해 설명하고 인접 목록 및 중첩 세트 모델 외에도 계층 적 데이터를 관리하기위한 추가 방법을 제공합니다.

참고 자료 / 참고 자료 섹션에서 나는 MySQL에서 중첩 된 세트를 처리하기 위해 미리 빌드 된 PHP 라이브러리를 포함하는 한 쌍의 PHP 관련 리소스를 포함하여 계층 적 데이터 관리 연구에 사용할 수있는 웹 리소스를 나열했다. 현재 인접 목록 모델을 사용 중이며 중첩 된 세트 모델을 실험하고 싶은 사용자는 아래에 나열된 데이터베이스 리소스의 계층 적 데이터 저장에서 둘 사이의 변환을위한 샘플 코드를 찾을 수 있습니다.