階層構造のテーブル設計｜隣接リスト・閉包テーブル・経路列挙の比較

階層構造のテーブル設計とは、組織・カテゴリ・フォルダのような親子関係（ツリー）をリレーショナルデータベースの表でどう持つかを決めることで、代表的な方式は隣接リスト・経路列挙・入れ子集合・閉包テーブルの4つです。 再帰CTE（WITH RECURSIVE）が使える今は、まず隣接リストを基本にし、子孫の一括取得が重くなったときに閉包テーブルを検討する、という順番が分かりやすい進め方です。

この記事では、同じ架空の組織ツリーを4方式で作り、6つの操作の SQL を2つのデータベースで流して結果を並べます。循環参照で再帰CTEが止まらなくなる状態の再現と止め方、PostgreSQL の ltree との違いも扱います。

確認環境：PostgreSQL 18.4、MySQL 8.0.46（いずれも Linux 上、確認日 2026-10-06）。本文の4方式の SQL は、両方で同じ文を流し、結果の行が一致することを確かめています。

この記事の結論

• 基本は隣接リスト（parent_id を持つ形）。追加・移動・削除が1行の更新で済み、子孫や祖先は再帰CTEで取れます。
• 子孫の一括取得が多く、再帰の時間が気になるときは閉包テーブル。10万ノードでの計測では、約2.2万件の子孫取得が隣接リスト＋再帰CTEで約31〜38ミリ秒、閉包テーブルで約3ミリ秒でした。代わりに行数は約7.8倍に増えました。
• 経路列挙は、パンくず表示や「この配下すべて」を LIKE で手軽に取りたいとき。PostgreSQL なら ltree 型が同じ考え方を型として提供します。
• 入れ子集合は、読み取りが中心でほとんど更新しないツリー向け。追加や移動のたびに多くの行の番号を振り直します。
• 隣接リストは循環参照を作れてしまうので、移動の前に「新しい親が自分の配下にいないか」を確かめます。

階層構造の4方式の早見表

題材にする組織ツリー

次の8ノードの架空の組織を、4方式すべてで同じ id・同じ名前で登録します。

graph TD
  n1[1 本社] --> n2[2 開発部]
  n1 --> n3[3 営業部]
  n2 --> n4[4 基盤課]
  n2 --> n5[5 アプリ課]
  n5 --> n6[6 Web係]
  n5 --> n7[7 モバイル係]
  n3 --> n8[8 東日本課]

4方式のテーブル定義と初期データ

PostgreSQL 18.4 と MySQL 8.0.46 の両方でそのまま流せます。

-- 1) 隣接リスト
CREATE TABLE org_adj (
  id        INT PRIMARY KEY,
  parent_id INT NULL,
  name      VARCHAR(50) NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES org_adj(id) ON DELETE CASCADE
);
CREATE INDEX idx_org_adj_parent ON org_adj (parent_id);
INSERT INTO org_adj (id, parent_id, name) VALUES
 (1, NULL, '本社'), (2, 1, '開発部'), (3, 1, '営業部'), (4, 2, '基盤課'),
 (5, 2, 'アプリ課'), (6, 5, 'Web係'), (7, 5, 'モバイル係'), (8, 3, '東日本課');

-- 2) 経路列挙
CREATE TABLE org_path (
  id   INT PRIMARY KEY,
  name VARCHAR(50)  NOT NULL,
  path VARCHAR(255) NOT NULL UNIQUE
);
INSERT INTO org_path (id, name, path) VALUES
 (1, '本社', '1/'), (2, '開発部', '1/2/'), (3, '営業部', '1/3/'), (4, '基盤課', '1/2/4/'),
 (5, 'アプリ課', '1/2/5/'), (6, 'Web係', '1/2/5/6/'), (7, 'モバイル係', '1/2/5/7/'), (8, '東日本課', '1/3/8/');

-- 3) 入れ子集合
CREATE TABLE org_ns (
  id   INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  lft  INT NOT NULL,
  rgt  INT NOT NULL
);
INSERT INTO org_ns (id, name, lft, rgt) VALUES
 (1, '本社', 1, 16), (2, '開発部', 2, 11), (4, '基盤課', 3, 4), (5, 'アプリ課', 5, 10),
 (6, 'Web係', 6, 7), (7, 'モバイル係', 8, 9), (3, '営業部', 12, 15), (8, '東日本課', 13, 14);

-- 4) 閉包テーブル
CREATE TABLE org_node (
  id   INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);
CREATE TABLE org_closure (
  ancestor   INT NOT NULL,
  descendant INT NOT NULL,
  depth      INT NOT NULL,
  PRIMARY KEY (ancestor, descendant),
  FOREIGN KEY (ancestor)   REFERENCES org_node(id) ON DELETE CASCADE,
  FOREIGN KEY (descendant) REFERENCES org_node(id) ON DELETE CASCADE
);
CREATE INDEX idx_org_closure_desc ON org_closure (descendant);
INSERT INTO org_node (id, name) SELECT id, name FROM org_adj;
-- 隣接リストから閉包テーブルの全行を作る（自分自身への行 depth = 0 も含める）
INSERT INTO org_closure (ancestor, descendant, depth)
WITH RECURSIVE c AS (
  SELECT id AS ancestor, id AS descendant, 0 AS depth FROM org_adj
  UNION ALL
  SELECT c.ancestor, a.id, c.depth + 1
  FROM c JOIN org_adj a ON a.parent_id = c.descendant
)
SELECT ancestor, descendant, depth FROM c;

8ノードに対して、閉包テーブルは 22 行になりました。経路列挙の経路は末尾にも区切りを付けています。'1/2' で前方一致させると '1/20/' にも当たってしまうため、'1/2/' のように区切りで終わらせます。

入れ子集合の lft・rgt は、ツリーを上から左回りにたどり、ノードに入るときと出るときに番号を振ったものです。配下のノードは必ず親の lft と rgt の間に入ります。

子孫を取得する（開発部の配下すべて）

-- 隣接リスト
WITH RECURSIVE sub AS (
  SELECT id, name, 0 AS depth FROM org_adj WHERE id = 2
  UNION ALL
  SELECT c.id, c.name, s.depth + 1
  FROM org_adj c JOIN sub s ON c.parent_id = s.id
)
SELECT id, name, depth FROM sub ORDER BY depth, id;

-- 経路列挙
SELECT n.id, n.name FROM org_path n
 WHERE n.path LIKE CONCAT((SELECT path FROM org_path WHERE id = 2), '%') ORDER BY n.path;

-- 入れ子集合
SELECT n.id, n.name FROM org_ns n JOIN org_ns p ON n.lft BETWEEN p.lft AND p.rgt
 WHERE p.id = 2 ORDER BY n.lft;

-- 閉包テーブル
SELECT n.id, n.name, c.depth FROM org_closure c JOIN org_node n ON n.id = c.descendant
 WHERE c.ancestor = 2 ORDER BY c.depth, n.id;

隣接リストと閉包テーブルの結果（PostgreSQL の表示）です。

 id |    name    | depth 
----+------------+-------
  2 | 開発部     |     0
  4 | 基盤課     |     1
  5 | アプリ課   |     1
  6 | Web係      |     2
  7 | モバイル係 |     2
(5 rows)

経路列挙と入れ子集合も同じ5行（id は 2、4、5、6、7 の順）を返しました。自分自身を除きたいときは、隣接リストと閉包テーブルは depth > 0、経路列挙と入れ子集合は n.id <> 2 を足します。

祖先を取得する（Web係から本社まで）

-- 隣接リスト
WITH RECURSIVE up AS (
  SELECT id, parent_id, name, 0 AS distance FROM org_adj WHERE id = 6
  UNION ALL
  SELECT p.id, p.parent_id, p.name, u.distance + 1
  FROM org_adj p JOIN up u ON p.id = u.parent_id
)
SELECT id, name, distance FROM up ORDER BY distance DESC;

-- 経路列挙（自分の経路が、相手の経路で始まっていれば祖先）
SELECT a.id, a.name FROM org_path n JOIN org_path a ON n.path LIKE CONCAT(a.path, '%')
 WHERE n.id = 6 ORDER BY a.path;

-- 入れ子集合
SELECT a.id, a.name FROM org_ns n JOIN org_ns a ON a.lft <= n.lft AND a.rgt >= n.rgt
 WHERE n.id = 6 ORDER BY a.lft;

-- 閉包テーブル
SELECT n.id, n.name, c.depth AS distance FROM org_closure c JOIN org_node n ON n.id = c.ancestor
 WHERE c.descendant = 6 ORDER BY c.depth DESC;

 id |   name   | distance 
----+----------+----------
  1 | 本社     |        3
  2 | 開発部   |        2
  5 | アプリ課 |        1
  6 | Web係    |        0
(4 rows)

4方式とも、本社 → 開発部 → アプリ課 → Web係 の4行でした。パンくずリストはこの結果を名前でつなげば作れます。

深さを求める

-- 隣接リスト（根から下りながら数える）
WITH RECURSIVE t AS (
  SELECT id, name, 0 AS depth FROM org_adj WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.depth + 1 FROM org_adj c JOIN t ON c.parent_id = t.id
)
SELECT id, name, depth FROM t ORDER BY id;

-- 経路列挙（区切り文字の数 − 1）
SELECT id, name, CHAR_LENGTH(path) - CHAR_LENGTH(REPLACE(path, '/', '')) - 1 AS depth
FROM org_path ORDER BY id;

-- 入れ子集合（自分を囲むノードの数 − 1）
SELECT n.id, n.name, COUNT(*) - 1 AS depth
FROM org_ns n JOIN org_ns a ON a.lft <= n.lft AND a.rgt >= n.rgt
GROUP BY n.id, n.name ORDER BY n.id;

-- 閉包テーブル（いちばん遠い祖先までの距離）
SELECT n.id, n.name, MAX(c.depth) AS depth
FROM org_closure c JOIN org_node n ON n.id = c.descendant
GROUP BY n.id, n.name ORDER BY n.id;

 id |    name    | depth 
----+------------+-------
  1 | 本社       |     0
  2 | 開発部     |     1
  3 | 営業部     |     1
  4 | 基盤課     |     2
  5 | アプリ課   |     2
  6 | Web係      |     3
  7 | モバイル係 |     3
  8 | 東日本課   |     2
(8 rows)

4方式で同じ8行でした。

ノードを追加する（アプリ課の下に「テスト係」）

-- 隣接リスト
INSERT INTO org_adj (id, parent_id, name) VALUES (9, 5, 'テスト係');

-- 経路列挙（親の経路の後ろに自分の id を付ける）
INSERT INTO org_path (id, name, path)
SELECT 9, 'テスト係', CONCAT(path, '9/') FROM org_path WHERE id = 5;

-- 入れ子集合（アプリ課の rgt = 10 の位置に、2つ分のすき間を空ける）
UPDATE org_ns SET rgt = rgt + 2 WHERE rgt >= 10;
UPDATE org_ns SET lft = lft + 2 WHERE lft > 10;
INSERT INTO org_ns (id, name, lft, rgt) VALUES (9, 'テスト係', 10, 11);

-- 閉包テーブル（親の祖先すべてと自分をつなぐ行 + 自分自身の行）
INSERT INTO org_node (id, name) VALUES (9, 'テスト係');
INSERT INTO org_closure (ancestor, descendant, depth)
SELECT ancestor, 9, depth + 1 FROM org_closure WHERE descendant = 5
UNION ALL SELECT 9, 9, 0;

入れ子集合は、末端に1件足すだけでも、本社・開発部・アプリ課・営業部・東日本課の5行の番号が変わりました。

 id |    name    | lft | rgt 
----+------------+-----+-----
  1 | 本社       |   1 |  18
  2 | 開発部     |   2 |  13
  4 | 基盤課     |   3 |   4
  5 | アプリ課   |   5 |  12
  6 | Web係      |   6 |   7
  7 | モバイル係 |   8 |   9
  9 | テスト係   |  10 |  11
  3 | 営業部     |  14 |  17
  8 | 東日本課   |  15 |  16
(9 rows)

閉包テーブルには、深さ3のノード1件に対して4行が入りました。

 ancestor | descendant | depth 
----------+------------+-------
        1 |          9 |     3
        2 |          9 |     2
        5 |          9 |     1
        9 |          9 |     0
(4 rows)

ノードを移動する（アプリ課を配下ごと営業部へ）

-- 隣接リスト
UPDATE org_adj SET parent_id = 3 WHERE id = 5;

-- 経路列挙（配下すべての経路の先頭 '1/2/' を '1/3/' に付け替える）
UPDATE org_path SET path = CONCAT('1/3/', SUBSTRING(path, CHAR_LENGTH('1/2/') + 1))
 WHERE path LIKE '1/2/5/%';

-- 入れ子集合（アプリ課は lft = 5、rgt = 12、幅 8）
UPDATE org_ns SET lft = -lft, rgt = -rgt WHERE lft BETWEEN 5 AND 12;  -- 1. 動かす部分を負の数にして退避
UPDATE org_ns SET lft = lft - 8 WHERE lft > 12;                       -- 2. 抜けた分を詰める
UPDATE org_ns SET rgt = rgt - 8 WHERE rgt > 12;
UPDATE org_ns SET rgt = rgt + 8 WHERE rgt >= 9;                       -- 3. 営業部の rgt（詰めた後は 9）の位置を空ける
UPDATE org_ns SET lft = lft + 8 WHERE lft > 9;
UPDATE org_ns SET lft = -lft + 4, rgt = -rgt + 4 WHERE lft < 0;       -- 4. 退避した部分を戻す（ずらす量 = 9 − 5）

-- 閉包テーブル
-- 1. 配下の各ノードと、配下の外にいる祖先とのつながりを消す
DELETE FROM org_closure
 WHERE descendant IN (SELECT descendant FROM (SELECT descendant FROM org_closure WHERE ancestor = 5) s1)
   AND ancestor NOT IN (SELECT descendant FROM (SELECT descendant FROM org_closure WHERE ancestor = 5) s2);
-- 2. 新しい親の祖先すべて × 配下すべて の組を足す
INSERT INTO org_closure (ancestor, descendant, depth)
SELECT sup.ancestor, sub.descendant, sup.depth + sub.depth + 1
FROM org_closure sup CROSS JOIN org_closure sub
WHERE sup.descendant = 3 AND sub.ancestor = 5;

移動後の経路列挙と入れ子集合です。

 id |    name    |   path   
----+------------+----------
  1 | 本社       | 1/
  2 | 開発部     | 1/2/
  4 | 基盤課     | 1/2/4/
  3 | 営業部     | 1/3/
  5 | アプリ課   | 1/3/5/
  6 | Web係      | 1/3/5/6/
  7 | モバイル係 | 1/3/5/7/
  9 | テスト係   | 1/3/5/9/
  8 | 東日本課   | 1/3/8/
(9 rows)

 id |    name    | lft | rgt 
----+------------+-----+-----
  1 | 本社       |   1 |  18
  2 | 開発部     |   2 |   5
  4 | 基盤課     |   3 |   4
  3 | 営業部     |   6 |  17
  8 | 東日本課   |   7 |   8
  5 | アプリ課   |   9 |  16
  6 | Web係      |  10 |  11
  7 | モバイル係 |  12 |  13
  9 | テスト係   |  14 |  15
(9 rows)

閉包テーブルで営業部（3）の配下を取り直すと、移したノードが入っています。

 id |    name    | depth 
----+------------+-------
  3 | 営業部     |     0
  5 | アプリ課   |     1
  8 | 東日本課   |     1
  6 | Web係      |     2
  7 | モバイル係 |     2
  9 | テスト係   |     2
(6 rows)

閉包テーブルの DELETE で副問い合わせをもう1段くるんでいるのは、MySQL 向けの書き方です。くるまずに同じテーブルを直接参照すると、MySQL 8.0.46 では次のエラーになりました（PostgreSQL はどちらの書き方でも動きます）。

ERROR 1093 (HY000) at line 1: You can't specify target table 'org_closure' for update in FROM clause

入れ子集合の移動は6文で、番号は例の値に合わせた定数です。途中で止まると番号が崩れるため、必ず1つのトランザクションで流します。

ノードを削除する（アプリ課を配下ごと）

-- 隣接リスト（ON DELETE CASCADE で配下も消える）
DELETE FROM org_adj WHERE id = 5;

-- 経路列挙
DELETE FROM org_path WHERE path LIKE '1/3/5/%';

-- 入れ子集合（アプリ課は lft = 9、rgt = 16、幅 8）
DELETE FROM org_ns WHERE lft BETWEEN 9 AND 16;
UPDATE org_ns SET lft = lft - 8 WHERE lft > 16;
UPDATE org_ns SET rgt = rgt - 8 WHERE rgt > 16;

-- 閉包テーブル（org_closure の行は ON DELETE CASCADE で消える）
DELETE FROM org_node
 WHERE id IN (SELECT descendant FROM (SELECT descendant FROM org_closure WHERE ancestor = 5) s);

4方式とも、残ったのは本社・開発部・営業部・基盤課・東日本課の5件でした。閉包テーブルは 11 行になりました。

 ancestor | descendant | depth 
----------+------------+-------
        1 |          1 |     0
        1 |          2 |     1
        1 |          3 |     1
        1 |          4 |     2
        1 |          8 |     2
        2 |          2 |     0
        2 |          4 |     1
        3 |          3 |     0
        3 |          8 |     1
        4 |          4 |     0
        8 |          8 |     0
(11 rows)

配下を残して1つ上の親につなぎ直したい場合、隣接リストなら「子の parent_id を、消すノードの親の id に UPDATE してから DELETE」の2文で、両方のデータベースで Web係とモバイル係が開発部の直下に付け替わることを確かめました。

6操作の文の数を並べる

10万ノードで子孫取得の時間を比べる

「再帰CTEは重い」と言われることがありますが、どの程度かを PostgreSQL 18.4 で測りました。各ノードが5つの子を持つ形で 100,000 ノードを作り（最大の深さ 8）、同じ構造を閉包テーブルにも入れています。時間は EXPLAIN (ANALYZE, TIMING OFF) の Execution Time を3回取った範囲で、手元の1台での参考値です。

CREATE TABLE big_adj (id int PRIMARY KEY, parent_id int REFERENCES big_adj(id), name text NOT NULL);
INSERT INTO big_adj
SELECT i, CASE WHEN i = 1 THEN NULL ELSE (i - 2) / 5 + 1 END, 'node' || i
FROM generate_series(1, 100000) i;
CREATE INDEX ON big_adj (parent_id);

数百件までの配下なら、隣接リスト＋再帰CTEでも 1 ミリ秒前後でした。差が開くのは数万件の配下を一度に取る場合で、そのぶん閉包テーブルは行数とサイズが7倍以上になります。「配下を丸ごと取る画面や集計が多いか」と「サイズと更新の手間を受け入れられるか」 で決めるのが現実的です。

循環参照で再帰CTEが止まらない状態と止め方

隣接リストは、外部キーがあっても「自分の配下を自分の親にする」更新を受け付けます。開発部（2）の親を、配下の Web係（6）にしてみます。

UPDATE org_adj SET parent_id = 6 WHERE id = 2;

この状態で開発部の子孫を取る再帰CTE（前述と同じ文）を流すと、2 → 5 → 6 → 2 → … と回り続けます。

MySQL は cte_max_recursion_depth（確認した値は既定の 1000）で自動的に止まります。PostgreSQL は自動では止まらなかったので、次のいずれかで止めます。

一方、根（parent_id IS NULL）から下りる再帰CTEは、どちらのデータベースでも止まらずに終わりました。ただし、輪になった部分はどこからもたどれなくなり、結果から消えます。

 id |   name   | depth 
----+----------+-------
  1 | 本社     |     0
  3 | 営業部   |     1
  8 | 東日本課 |     2
(3 rows)

エラーが出ないまま5件が見えなくなるので、こちらのほうが気づきにくい状態です。

CYCLE 句で止める（PostgreSQL）

WITH RECURSIVE sub AS (
  SELECT id, name, 0 AS depth FROM org_adj WHERE id = 2
  UNION ALL
  SELECT c.id, c.name, s.depth + 1
  FROM org_adj c JOIN sub s ON c.parent_id = s.id
) CYCLE id SET is_cycle USING path
SELECT id, name, depth, is_cycle, path FROM sub ORDER BY depth, id;

 id |    name    | depth | is_cycle |       path        
----+------------+-------+----------+-------------------
  2 | 開発部     |     0 | f        | {(2)}
  4 | 基盤課     |     1 | f        | {(2),(4)}
  5 | アプリ課   |     1 | f        | {(2),(5)}
  6 | Web係      |     2 | f        | {(2),(5),(6)}
  7 | モバイル係 |     2 | f        | {(2),(5),(7)}
  2 | 開発部     |     3 | t        | {(2),(5),(6),(2)}
(6 rows)

CYCLE 句は、指定した列（id）が経路の中で2回目に出た行に is_cycle = true を付け、そこから先へ進みません。WHERE NOT is_cycle を足せば重複の行も外せますし、WHERE is_cycle にすれば循環しているデータを探す SQL になります。MySQL 8.0.46 で同じ文を流すと構文エラー（ERROR 1064）になりました。

深さの上限で止める（PostgreSQL・MySQL 共通）

WITH RECURSIVE sub AS (
  SELECT id, name, 0 AS depth FROM org_adj WHERE id = 2
  UNION ALL
  SELECT c.id, c.name, s.depth + 1
  FROM org_adj c JOIN sub s ON c.parent_id = s.id
  WHERE s.depth < 5
)
SELECT id, name, depth FROM sub ORDER BY depth, id;

両方のデータベースで止まりましたが、結果は 10 行になり、同じノードが深さ違いで2回ずつ出ました（開発部が depth 0 と 3、基盤課が 1 と 4 など）。深さの上限は「止める」ための安全装置で、重複を取り除くものではない点に注意します。

移動の前に確かめる

循環は作らせないのがいちばんです。移動の UPDATE の前に、新しい親が動かすノードの配下に含まれていないかを確かめます。

-- 新しい親(6)が、動かすノード(2)の配下に含まれていないか
WITH RECURSIVE sub AS (
  SELECT id FROM org_adj WHERE id = 2
  UNION ALL
  SELECT c.id FROM org_adj c JOIN sub s ON c.parent_id = s.id
)
SELECT COUNT(*) AS new_parent_is_descendant FROM sub WHERE id = 6;

 new_parent_is_descendant 
--------------------------
                        1
(1 row)

1 が返ったら、その移動は受け付けません。0 のときだけ UPDATE します。確認と UPDATE は同じトランザクションで行います。

PostgreSQL の ltree との違い

ltree は PostgreSQL の拡張で、経路列挙を専用の型として扱えます。ラベルをドットでつないだ経路を持ち、祖先・子孫の判定に専用の演算子が使えます。

CREATE EXTENSION IF NOT EXISTS ltree;
CREATE TABLE org_ltree (
  id   int PRIMARY KEY,
  name varchar(50) NOT NULL,
  path ltree NOT NULL
);
CREATE INDEX idx_org_ltree_path ON org_ltree USING gist (path);
INSERT INTO org_ltree VALUES
 (1, '本社', '1'), (2, '開発部', '1.2'), (3, '営業部', '1.3'), (4, '基盤課', '1.2.4'),
 (5, 'アプリ課', '1.2.5'), (6, 'Web係', '1.2.5.6'), (7, 'モバイル係', '1.2.5.7'), (8, '東日本課', '1.3.8');

-- 子孫（<@ は「左が右の子孫か、同じ」）
SELECT id, name, path, nlevel(path) - 1 AS depth FROM org_ltree WHERE path <@ '1.2' ORDER BY path;
-- 祖先（@> は「左が右の祖先か、同じ」）
SELECT id, name, path FROM org_ltree WHERE path @> '1.2.5.6' ORDER BY path;
-- 移動（アプリ課を営業部の下へ）
UPDATE org_ltree SET path = '1.3' || subpath(path, nlevel('1.2')) WHERE path <@ '1.2.5';

 id |    name    |  path   | depth 
----+------------+---------+-------
  2 | 開発部     | 1.2     |     1
  4 | 基盤課     | 1.2.4   |     2
  5 | アプリ課   | 1.2.5   |     2
  6 | Web係      | 1.2.5.6 |     3
  7 | モバイル係 | 1.2.5.7 |     3
(5 rows)

 id |   name   |  path   
----+----------+---------
  1 | 本社     | 1
  2 | 開発部   | 1.2
  5 | アプリ課 | 1.2.5
  6 | Web係    | 1.2.5.6
(4 rows)

公式ドキュメント（ltree）では、ラベルは 1000 文字まで、経路は 65535 ラベルまでとされています。ラベルに日本語の名前をそのまま入れるより、今回のように id を入れておくと、名称変更で経路を書き換えずに済みます。

確かめて分かったこと

• 4方式・6操作の SQL は、PostgreSQL 18.4 と MySQL 8.0.46 で同じ文のまま動き、結果の行も一致しました。 違いが出たのは、同じテーブルを副問い合わせで参照する DELETE（MySQL は ERROR 1093）と CYCLE 句（MySQL は構文エラー）でした。
• 数百件の配下なら、隣接リスト＋再帰CTEは約 1 ミリ秒でした。 閉包テーブルとの差がはっきり出たのは約2.2万件の配下を取る場合です。
• 閉包テーブルは 10万ノード・深さ 8 で約 78万行、サイズは約7倍になりました。 深いツリーほど行数が増えます。
• 循環ができても、根から下りる再帰CTEはエラーにならず、輪の部分が結果から消えました。 止まらなくなるのは、輪の中のノードを起点にしたときです。
• 深さの上限だけで止めると、同じノードが繰り返し出ました。 重複を外すには CYCLE 句か、移動前の確認が要ります。
• MySQL の自己参照の ON DELETE CASCADE でも、アプリ課の削除で配下の3件が一緒に消えました。

よくある質問

Q. 隣接リストと閉包テーブルの違いは？ A. 隣接リストは「直接の親」だけを各行に持ち、閉包テーブルは「すべての祖先と子孫の組」を別テーブルに持ちます。前者は更新が軽く、後者は配下の一括取得が結合1回で済みます。

Q. 経路列挙と閉包テーブルはどちらを選ぶ？ A. テーブルを増やさず LIKE で手軽に書きたいなら経路列挙、外部キーで整合性を守りたいなら閉包テーブルです。経路列挙は経路の中の id が実在するかをデータベースが確かめられません。

Q. 再帰CTEが終わらないときは？ A. データに循環がある可能性が高いです。PostgreSQL は CYCLE 句を付けて is_cycle が true の行を探し、MySQL は ERROR 3636 が出たら深さの上限つきの文で経路を確かめます。

Q. 階層の深さに上限はある？ A. 隣接リストそのものに上限はありませんが、MySQL の再帰CTEは cte_max_recursion_depth（既定 1000）を超えるとエラーになります。経路列挙は列の長さ（今回は 255 文字）が上限を決めます。

Q. 途中で方式を変えられる？ A. 隣接リストを元にすれば変えられます。記事中の「隣接リストから閉包テーブルの全行を作る」INSERT のように、再帰CTEで他の方式のデータを作り直せます。隣接リストを正として残し、閉包テーブルを読み取り用に併用する形も取れます。

まとめ

• 基本は隣接リスト。追加・移動・削除が1行で済み、子孫・祖先は再帰CTEで取れる。
• 配下の一括取得が多く件数も大きいなら閉包テーブル。10万ノードの計測で約10倍速く、行数は約7.8倍。
• 経路列挙は LIKE で手軽、入れ子集合は読み取り中心で更新が少ないツリー向け。
• 入れ子集合の移動は UPDATE 6文、閉包テーブルの移動は DELETE と INSERT。必ずトランザクションで。
• 隣接リストは循環を作れる。移動前に「新しい親が配下にいないか」を確かめる。
• 止め方は PostgreSQL が CYCLE 句、MySQL は cte_max_recursion_depth と深さの条件。
• PostgreSQL では ltree が経路列挙を型と演算子で支える。

参考・出典

• PostgreSQL 18 Documentation「WITH Queries (Common Table Expressions)」 https://www.postgresql.org/docs/18/queries-with.html
• PostgreSQL 18 Documentation「ltree」 https://www.postgresql.org/docs/18/ltree.html
• MySQL 8.4 Reference Manual「WITH (Common Table Expressions)」 https://dev.mysql.com/doc/refman/8.4/en/with.html

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。