階層データに対する再帰的 MySQL クエリを作成する

Question

以下に示すような MySQL テーブルがあります:

＃＃＃名前＃＃＃ 1920 ＃＃＃21＃＃＃ カテゴリー 3 ＃＃＃22＃＃＃ ＃＃＃21＃＃＃ ここで、ID [たとえば ] を指定するだけの MySQL クエリが必要です。その後、そのすべてのサブ ID を取得する必要があります [つまり、結果の ID は '20,21 になります。 ,22 ']....子の階層は不明です。異なる場合があります...

id	親ID
カテゴリー 1	0
カテゴリー 2	19
20		カテゴリー 4
...	...	...
	id=19

for

ループを使用してこれを行う方法は知っていますが、単一の MySQL クエリを使用して同じことを実現するにはどうすればよいでしょうか?

Answer 1

MySQL 8 の場合: 再帰を使用します 構文を使用します。 MySQL 5.x の場合: インライン変数、パス ID、または自己結合を使用します。
MySQL 8 リーリー

parent_id = 19

で指定した値は、子孫を選択する親の

id に設定する必要があります。 MySQL 5.x

共通テーブル式をサポートしていない MySQL のバージョン (バージョン 5.7 まで) の場合、次のクエリを使用してこれを実現できます。 リーリー

これは

ヴァイオリン

です。

ここで、 @pv := '19' で指定した値は、子孫を選択する親の

id

に設定する必要があります。 これは、親に複数の子がいる場合にも機能します。ただし、各レコードは parent_id < id 条件を満たす必要があります。満たさない場合、結果は不完全になります。

クエリ内の変数の割り当て < idこのクエリは、特定の MySQL 構文を使用します。変数は実行中に割り当てられ、変更されます。実行順序については、いくつかの仮定が行われます:

最初に

from

句を評価します。ここで

@pv
• が初期化されます。 where 句は、
from
• エイリアスから取得された順序で各レコードに対して評価されます。したがって、ここで設定する条件には、親が子孫ツリー内にあると識別されているレコードのみが含まれます (主親のすべての子孫が段階的に @pv に追加されます)。 この where 句の条件は順番に評価され、全体的な結果が決定されると評価は中断されます。したがって、2 番目の条件は親リストに ID を追加するため、2 番目の位置になければなりません。これは、ID が最初の条件を通過した場合にのみ発生します。
length
• 関数は、pv 文字列が何らかの理由で false 値を生成した場合でも、この条件が常に true であることを保証するために呼び出されます。 全体として、これらの仮定は信頼するには危険すぎると思われるかもしれません。 ドキュメント
警告:

したがって、上記のクエリと一致していても、たとえば条件を追加したり、このクエリをより大きなクエリ内のビューまたはサブクエリとして使用したりする場合、評価の順序は変わる可能性があります。これは、将来の MySQL バージョン : で削除される

「機能」です。

上で述べたように、MySQL 8.0 以降では、再帰的な with 構文を使用する必要があります。

＃＃＃効率＃＃＃

非常に大規模なデータ セットの場合、この解決策は遅くなる可能性があります。find_in_set

操作はリスト内の数値を検索する最も理想的な方法ではなく、同じ結果を確実に達成できないためです。返されるレコードの数としての目標。

代替案 1: 再帰を使用する、接続

再帰クエリの SQL:1999 ISO 標準 WITH [RECURSIVE] 構文 を実装するデータベースが増えています (例: Postgres 8.4 、SQL Server 2005) , DB2, Oracle 11gR2 , SQLite 3.8.4 , Firebird 2.1 , H2 , HyperSQL 2.1.0 、Teradata、MariaDB 10.2.2 )。 バージョン 8.0 以降、MySQL もサポートします。使用する構文については、この回答の先頭を参照してください。

一部のデータベースには、 CONNECT BY 句など、階層ルックアップ用の代替の非標準構文があります。 102/b14200/queries003.htm" rel="noreferrer">Oracle、DB2、Informix、CUBRID およびその他のデータベース。

MySQL バージョン 5.7 はそのような機能を提供しません。データベース エンジンがこの構文を提供する場合、またはこの構文を提供するデータベース エンジンに移行できる場合、これが間違いなく最良の選択です。そうでない場合は、次の代替案を検討してください。

代替 2: パス スタイル識別子

階層情報 (パス) を含む ID 値を割り当てると、作業がはるかに簡単になります。たとえば、あなたの場合、これは次のようになります:

＃＃＃名前＃＃＃ 1919/11/1919/1/1/1そうすると、

ID
カテゴリー 1
カテゴリー 2
カテゴリー 3
カテゴリー 4

選択

は次のようになります: リーリー 代替案 3: 繰り返しの自己結合

階層ツリーの深さの上限がわかっている場合は、次のような標準の

sql

クエリを使用できます。 リーリー これを参照してくださいフィドル

where

条件は、どの親の子孫を取得するかを指定します。必要に応じて、このクエリをさらに多くのレベルに拡張できます。