MySQL で再帰的な子ノードを照会する方法

2年 ago
優斗, 朝陽
1 minute

MySQLでは、再帰的な子ノードの検索は、再帰的なクエリステートメントを使用することで実現できます。MySQL自体には再帰的な検索機能はありませんが、ストアドプロシージャまたはビューを使用してシミュレーションすることができます。

よく使われる方法には、ストアドプロシージャを使用して再帰クエリを実行することがあります。以下にストアドプロシージャの例を示します。

DELIMITER //

CREATE PROCEDURE recursiveQuery(IN parentId INT)
BEGIN
    -- 创建临时表用于存储查询结果
    CREATE TEMPORARY TABLE tempTable (
        id INT,
        name VARCHAR(255),
        parent_id INT
    );

    -- 插入初始节点
    INSERT INTO tempTable
    SELECT id, name, parent_id
    FROM your_table
    WHERE parent_id = parentId;

    -- 递归查询子节点
    INSERT INTO tempTable
    SELECT t.id, t.name, t.parent_id
    FROM your_table t
    INNER JOIN tempTable tt ON t.parent_id = tt.id;

    -- 返回查询结果
    SELECT * FROM tempTable;

    -- 清空临时表
    DROP TABLE tempTable;
END //

DELIMITER ;

上記ストアドプロシージャでは、まず一時テーブルtempTableをクエリ結果を格納するために作成します。次に、最初のノードを挿入し、再帰クエリによって子ノードを一時テーブルに挿入します。最後に、クエリ結果を返して一時テーブルを空にします。

ストアドプロシージャを使用する時は、ストアドプロシージャを呼び出し、元のノードの ID を渡すことで再帰クエリを実現できます。例えば、ストアドプロシージャ recursiveQuery(1) を呼び出すと、ID が 1 のノードとそのすべての子ノードが照会されます。

もう1つの方法は、ビューを使用して再帰的なクエリーを実装することです。次のビュー定義は、その例です。

CREATE VIEW recursiveView AS
WITH RECURSIVE tempView(id, name, parent_id) AS (
    SELECT id, name, parent_id
    FROM your_table
    WHERE parent_id IS NULL
    UNION ALL
    SELECT t.id, t.name, t.parent_id
    FROM your_table t
    INNER JOIN tempView tv ON t.parent_id = tv.id
)
SELECT * FROM tempView;

WITH RECURSIVE tempView AS (
SELECT * FROM table WHERE parent_id IS NULL
UNION ALL
SELECT t.* FROM tempView AS p
INNER JOIN table AS t ON p.id = t.parent_id
)
SELECT * FROM tempView

再帰的なクエリを利用するには、ビューを使用し、ビューrecursiveViewから直接照会することで実現できます。たとえば、SELECT * FROM recursiveViewを実行すると、すべてのノードとその子ノードのクエリ結果が返されます。

ストアドプロシージャやビューを使用していても、再帰クエリはデータ量が多かったり再帰階層が深かったりするとパフォーマンス問題が発生する可能性があるため、注意深く使用する必要があります。

bannerAds