MySQLの再帰クエリを使ってツリー構造をどのように適用するか

2年 ago
結衣, 春花
1 minute

MySQL でリカーシブクエリー（再帰的クエリ）を使用してツリー構造を実装するには、リカーシブクエリーステートメントまたはストアドプロシージャを使用します。

  1. 再帰クエリ文

再帰的な問い合わせでは、再帰的に自分自身を参照できるWITH RECURSIVE句を使用しています。以下に、木構造を問い合わせるための再帰的なクエリを使用した例を示します。

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name
  FROM your_table
  WHERE id = 1
  UNION ALL
  SELECT t.id, t.parent_id, t.name
  FROM your_table t
  JOIN tree ON t.parent_id = tree.id
)
SELECT * FROM tree;

上記の例では、your_table はツリー構造のデータを格納する表で、id と parent_id がノードを繋ぐフィールドであり、name はノードの名前です。再帰クエリー文はルートノードから始まり、各ノードとその子ノードを階層的に取得します。

  1. ストアドプロシージャを使用する。

再帰的なクエリで木構造を処理する必要がある場合は、より複雑なロジックや処理が必要になるので、代わりにストアドプロシージャを使用できます。以下にストアドプロシージャを使用した、木構造をクエリする例を示します。

DELIMITER //

CREATE PROCEDURE get_tree(IN node_id INT)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE cur_id INT;
  DECLARE cur_parent_id INT;
  DECLARE cur_name VARCHAR(255);
  DECLARE cur CURSOR FOR 
    SELECT id, parent_id, name
    FROM your_table
    WHERE parent_id = node_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  CREATE TEMPORARY TABLE IF NOT EXISTS temp_tree (
    id INT,
    parent_id INT,
    name VARCHAR(255)
  );

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO cur_id, cur_parent_id, cur_name;
    IF done THEN
      LEAVE read_loop;
    END IF;

    INSERT INTO temp_tree VALUES (cur_id, cur_parent_id, cur_name);
    CALL get_tree(cur_id);
  END LOOP;

  CLOSE cur;

  SELECT * FROM temp_tree;
  DROP TEMPORARY TABLE IF EXISTS temp_tree;
END//

DELIMITER ;

上記では、get_treeストアドプロシージャはノードIDをパラメータとし、そのノードと子ノードをカーソルで検索して結果をテンポラリテーブルに挿入し、その後、子ノードの子ノードを再帰的にget_treeストアドプロシージャを使用して検索し、子ノードがなくなるまで続けます。最後にテンポラリテーブルから結果を取得します。

ストアドプロシジャを使用すると、階層構造のクエリロジックをより柔軟に処理できます。ただし、再帰的なクエリステートメントと比較して、少し複雑になることがあります。

再帰クエリ文でもストアドプロシージャでも、実際のビジネスニーズに合わせて調整・最適化が可能。

bannerAds