どのようにMySQL再帰クエリでツリー構造を実現するか

2年 ago
優斗, 朝陽
1 minute

MySQLは再帰クエリによって木構造を直接サポートしませんが、実際には再帰クエリを使用して実装できます。

再帰クエリを実現するためによく用いられる方法の 1 つに、自己結合 (Self Join) があります。id（カテゴリ ID）と parent_id（親カテゴリ ID）という 2 つの列を含む categories というテーブルがあるとします。以下はその例です。

CREATE TABLE categories (
    id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(100)
);

INSERT INTO categories (id, parent_id, name) VALUES
(1, NULL, 'Category 1'),
(2, 1, 'Category 1.1'),
(3, 1, 'Category 1.2'),
(4, 2, 'Category 1.1.1'),
(5, 2, 'Category 1.1.2'),
(6, 3, 'Category 1.2.1'),
(7, 3, 'Category 1.2.2');

以下のクエリは階層構造を再帰的に照会するために自己結合を使用します：

SELECT 
    CONCAT( REPEAT('  ', COUNT(parent.name) - 1), node.name) AS name
FROM
    categories AS node,
    categories AS parent
WHERE
    node.left_id BETWEEN parent.left_id AND parent.right_id
GROUP BY
    node.name
ORDER BY
    node.left_id;

これは、以下の出力と同様のツリー構造の結果を返します。

Category 1
  Category 1.1
    Category 1.1.1
    Category 1.1.2
  Category 1.2
    Category 1.2.1
    Category 1.2.2

このクエリでは、WHERE句を使用して左のIDと右のIDの間のノードをリンクし、表それ自体に結合することで、それらのリンクが確立されます。次に、GROUP BY句を使用して、階層構造に基づいて結果をグループ化し、CONCAT関数とREPEAT関数を使用して適切なインデントを生成します。

この方法には、木の深さが浅い場合に適しているという注意点があります。より複雑な木の構造に対しては、ストアドプロシージャを利用する、PHPやPythonなどのプログラミング言語で再帰クエリを処理するといった、他の方法が必要になる可能性があります。

bannerAds