がここにありますフィドル。

ここで、 で指定された値は、すべての子孫を選択する親の@pv := '19'に設定する必要があります。id

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

クエリ内の変数の割り当て

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

• 句fromが最初に評価されます。そこで が@pv初期化されます。
• 句は、エイリアスwhereからの取得順に各レコードに対して評価されますfrom。したがって、ここでは、親が子孫ツリー内にあることがすでに識別されているレコードのみを含めるように条件が設定されます (プライマリ親のすべての子孫が段階的に に追加されます@pv)。
• この節の条件はwhere順番に評価され、全体の結果が確定すると評価は中断されます。したがって、2 番目の条件は を親リストに追加するため 2 番目の場所に置く必要があり、これは が最初の条件に合格したid場合にのみ発生します。この関数は、文字列が何らかの理由で偽値を生成する場合でも、この条件が常に真であることを確認するためにのみ呼び出されます。idlengthpv

全体的に、これらの仮定は頼りにするにはリスクが高すぎると感じるかもしれない。ドキュメンテーション警告:

期待どおりの結果が得られる可能性がありますが、これは保証されません [...] ユーザー変数を含む式の評価順序は未定義です。

したがって、上記のクエリでは一貫して動作しますが、条件を追加したり、このクエリをより大きなクエリのビューまたはサブクエリとして使用したりすると、評価順序が変わる可能性があります。これは「機能」です。将来のMySQLリリースでは削除される予定です:

以前のリリースの MySQL では、 以外のステートメントでユーザー変数に値を割り当てることができましたSET。この機能は、下位互換性のために MySQL 8.0 でサポートされていますが、MySQL の将来のリリースでは削除される可能性があります。

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

効率

非常に大きなデータセットの場合、このソリューションは遅くなる可能性があります。find_in_setこの操作は、リスト内の数値を検索する最も理想的な方法ではありません。特に、返されるレコードの数と同じ桁数のサイズに達するリストでは、理想的な方法ではありません。

代替案1: with recursive、connect by

ますます多くのデータベースが実装していますSQL:1999 ISO標準WITH [RECURSIVE]構文再帰クエリの場合（例：Postgres 8.4以降、SQL Server 2005 以降、DB2、オラクル 11gR2+、SQLite 3.8.4 以上、ファイアバード 2.1+、H2、HyperSQL 2.1.0 以上、テラデータ、MariaDB 10.2.2 以上）。そして、バージョン8.0、MySQLもサポートしています使用する構文については、この回答の先頭を参照してください。

一部のデータベースでは、階層検索のための代替の非標準構文が提供されています。たとえばCONNECT BY、オラクル、DB2、インフォミックス、キューブリッドおよびその他のデータベース。

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

代替案2: パス形式の識別子

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

すると、select次のようになります。

select  id,
        name 
from    products
where   id like '19/%'

select      p6.parent_id as parent6_id,
            p5.parent_id as parent5_id,
            p4.parent_id as parent4_id,
            p3.parent_id as parent3_id,
            p2.parent_id as parent2_id,
            p1.parent_id as parent_id,
            p1.id as product_id,
            p1.name
from        products p1
left join   products p2 on p2.id = p1.parent_id 
left join   products p3 on p3.id = p2.parent_id 
left join   products p4 on p4.id = p3.parent_id  
left join   products p5 on p5.id = p4.parent_id  
left join   products p6 on p6.id = p5.parent_id
where       19 in (p1.parent_id, 
                   p2.parent_id, 
                   p3.parent_id, 
                   p4.parent_id, 
                   p5.parent_id, 
                   p6.parent_id) 
order       by 1, 2, 3, 4, 5, 6, 7;