MySQLで階層化されたデータを扱う（再帰的に検索しないで済む方法）
Oracleの場合は、Start With〜Connect By Prior〜というSQLで階層化されたデータを取り扱うことができます。では、MySQLではどうか？下記のサイトでしっかり解説してありました。
Managing Hierarchical Data in MySQL
階層化されたデータをMySQLで扱う（上記の日本語訳）
要約すると、
- 階層化データを扱うには、Adjacency List モデルとネストセットモデルの２つがある
- Adjacency List モデルは直感的でわかりやすい。更新も簡単。
- しかし、Adjacency List モデルで表されたデータを検索するのはやや面倒（MySQLでは。）
- 一方、ネストセットモデルで表されたデータは検索しやすい
- しかし、ネストセットモデルは更新が面倒
といったところです。どちらの方法にもそれぞれメリット・デメリットがありますが、Adjacency List モデルには、階層が深くなる（または深さの制限がない）と階層データとして取得することはできない、という致命的な問題があるようです。
今回、僕がやりたかったのは「メールのスレッド表示データ」の実現だったので、階層が深くなることが十分にありえます。なので、必然的にネストセットモデルを選ぶことにしました。
ネストセットモデルの問題
ネストセットモデルでは親子関係を表すために、左右の番号を使用します。が、その管理が非常に面倒です。上記サイトで紹介されているやり方では、末端にノードが追加されると、それだけで全体の番号の振り直しが必要になる場合があります。これでは、「メールのスレッド表示」をする上で問題です。という訳で、自分なりに解決策を考えました。
- 左右の番号を連番にせずに、あらかじめ大きな値を設定しておく
上記サイトでは、左右の番号（lft,rgt）を[1,20][2,9][10,19]、、としていますが、これは必ずしも連番でなくても良さそうなので[1,10000][2,2499][2500,4999]、、というように番号を振ります。これは、右の値として予め大きな値を設定しておくことで、後でノードが追加になったときに他のノードに影響がでないようにする、という狙いです。
番号の振り方についてもう少し補足します。例えば、以下のようなデータがあったとします。
ここで、左右の値として使用できる番号の範囲を 0〜10001 とします。とすると当然、[乗り物]＝[0,10001]になります。次に[乗り物]の子（車,電車）ですが、[車]＝[1,5000]、[電車]＝[5001,10000]としてしまうと、次に[乗り物]の子が追加になったときに番号を振りなおさなければなりません。なので、あらかじめ１つ分だけ余裕を残しておき [車]＝[1,3333]、[電車]＝[3334,6666]とします（※66667〜10000が余裕分）。[車]の子についても同様に[セダン]＝[2,832]、[バス]＝[833,1665]、[トラック]＝[1666,2498] とします（※2499〜3332が余裕分）。
[乗り物]
+-- [車]
+-- [セダン]
+-- [バス]
+-- [トラック]
+-- [電車]
+-- [在来線]
+-- [新幹線]
このように番号を振ることで、例えば[セダン]の下に子ノードが追加になったときにも、他のノードには一切影響を与えることなく子ノードのみを追加することができます。これでネストセットモデルの番号の管理が大分楽になりました。一方で、以下の点には注意が必要です。
- 番号の余裕分は、ツリーを初めに作った段階とき割り当てるため、後から追加していくと番号が足りなくなる可能性がある
以上を踏まえた上で、左右の値として使用できる番号の範囲の検討が必要です。例えば、0〜1024とした場合は、1024＝2^10 なので、子ノードは最大でも10階層しかもつことができません。今回の「メールのスレッド表示」では、階層が最大12、階層あたりの子ノードが最大6と見積もりましたので、値の範囲としては 0〜16777215（=2^24）としました。
以上、「MySQLで階層化されたデータを扱う」方法についての考察でしたが、Oracleの「Start With〜Connect By Prior〜」の便利さがよくわかりました。Adjacency List モデルなら何も考えずにリンクだけつないどけばいいですからね〜。
