MySQL でツリー構造を再帰的にクエリして親ノードの子孫を見つける方法

MySQL でのツリー構造の再帰

リレーショナル データベースでは、特に再帰的なクエリが必要な場合、階層データの管理が課題となることがあります。 MySQL には、特定の問題があります。それは、階層の深さに関係なく、指定された親の場所のすべての子孫を効率的に取得する方法です。

問題:

場所に関する次のデータベース スキーマを考えてみましょう。

location (id, ....)
location_parent (location_id, parent_id)

location テーブルは場所情報を保存し、location_parent テーブルは場所間の親子関係を定義します。

ID を持つ場所があるとします。 5. 複数のレベルの子孫を含むすべての子孫を取得したいと考えています。

最初の解決策:

再帰的クエリの 1 つのアプローチは、自己クエリを使用することです。次のように結合します:

<code class="sql">SELECT DISTINCT l.id
FROM location AS l
LEFT JOIN location_parent AS lp ON l.id = lp.child_id
WHERE lp.parent_id = 5
;</code>

このクエリは、場所 5 の直接の子を取得します。ただし、より深い子孫の場合は、毎回前のクエリの結果をクエリとして使用して、クエリを複数回繰り返す必要があります。次へのインプット。この方法は、大規模な階層構造では非効率的であり、実用的ではありません。

推奨される解決策:

この問題に対する推奨される解決策は、再帰的な Common Table Expression (CTE) を使用することです。 CTE は、単一のクエリ内で定義および参照できる一時テーブルです。

<code class="sql">WITH RECURSIVE descendants AS (
    SELECT id, parent_id
    FROM location_parent
    WHERE parent_id = 5
    UNION ALL
    SELECT lp.child_id, lp.parent_id
    FROM descendants AS d
    JOIN location_parent AS lp ON d.id = lp.parent_id
)
SELECT id
FROM descendants
;</code>

このクエリは、場所 5 の直接の子から始まる子孫と呼ばれる再帰的 CTE を作成します。その後、UNION ALL 句が使用されます。以前に選択した子孫の子を追加し、任意の深さのすべての子孫を含むようにクエリを効果的に拡張します。

クエリの 2 番目の部分では、子孫 CTE から id 列を選択します。これにより、すべての子孫のリストが得られます。場所。このアプローチは自己結合方法よりもはるかに効率的で、あらゆる深さの階層構造を処理できます。

以上がMySQL でツリー構造を再帰的にクエリして親ノードの子孫を見つける方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。