はじめに

この記事はHamee Advent Calendar 2021 - Qiita の23日目の記事です。

Calendar for Hamee | Advent Calendar 2021 - Qiita

Hameeではハンズオンや輪読会など様々な勉強会が定期的に開催されています。その中の１つで

私はSQLアンチパターン輪読会に参加しています。その中でナイーブツリー (素朴な木)というものを学びました。ナイーブツリーの章の中でMySQL 8.0から再帰クエリというものが使用できるようになり隣接リスト形式も一般的な選択肢になると記述があり、詳細が書かれていなかったのと再起クエリというものを使ったことがなかったので実際に試してみました。

ナイーブツリー (素朴な木)とは？

リレーショナルデータベースで再帰的な階層構造を表示するときに発生するアンチパターン

階層構造を扱う場合の情報の例

• フォルダ情報
• スレッド形式のコメント欄
• パンくずリスト
• 組織図

例としてフォルダの階層構造を扱うTable情報を考えてみる

CREATE
  TABLE
    IF NOT EXISTS `folder`(
      `id` INT unsigned NOT NULL AUTO_INCREMENT,
      `parent_id` INT unsigned,
      `name` VARCHAR(255) NOT NULL DEFAULT '',
      PRIMARY KEY(`id`)
    ) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
;

-- INSERTは簡単に生成することが可能である
INSERT
  INTO
    `folder` (`id`, `parent_id`, `name`)
VALUES
    (1, NULL, 'Root Folder'),
    (2, 1, 'folder1'),
    (3, 2, 'folder1-1'),
    (4, 2, 'folder1-2'),
    (5, 3, 'folder1-1-1'),
    (6, 4,'folder1-2-1'),
    (7, 1,'folder2'),
    (8, 1,'folder3'),
    (9, 7, 'folder2-1'),
    (10, 4, 'folder1-2-2');

葉ノードまでの階層を取得することを考えてみる

以下のクエリで最終フォルダーまで取得可能である