Usar la consulta MySQL para recorrer filas para hacer un árbol recursivo

Tengo una tabla de lista de materiales configurada de esta manera:
artículo – padre

El resultado final cuando visualizo la lista de materiales es que se muestra así:

item 1 - parent 0 item 2 - parent 1 item 3 - parent 1 

El resultado final también podría ser de varios niveles como este:

 item 3 - parent 0 item 4 - parent 3 item 76 - parent 3 

Y puede continuar hasta el infinito:

 item 76 - parent 0 item 46 - parent 76 item 46 - parent 0 item 25 - parent 46 

En este momento, o solo obtengo 1 nivel de la base de datos:

SELECT * FROM bom WHERE parentId = $itemId (shorthand)

O saque cada fila de la mesa y use mi función recursiva para ordenar solo las que necesito, pero esto es obviamente ineficiente ya que solo necesito 10 filas, pero obtengo 10,000 registros. La salida de la función recursiva solo creará un árbol como este:

 item 1 item 2 item 3 item 4 item 76 item 46 item 25 

Todo lo que sé es que estoy empezando en el ítem 1. El ítem 5 podría tener un padre de 11; no tienen que ir en secuencia Quiero obtener todas las twigs de niños en el árbol. ¿Cómo podría hacer esta consulta en mysql?

    El 24 de octubre de 2011, alguien publicó una pregunta en el DBA StackExchange sobre el recorrido de árbol en MySQL . El SQL para MySQL no puede soportarlo.

    Escribí tres (3) procedimientos almacenados ( GetParentIDByID, GetAncestry y GetFamilyTree ) en mi respuesta a esa pregunta . Espero que esta información te ayude a construir lo que estás buscando.

    Bill Karwin ha publicado una presentación de diapositivas sobre datos jerárquicos en MySQL. Si cambiar el diseño de su base de datos es una opción, existen otras formas atractivas de almacenar sus datos para facilitar la consulta. Los enfoques que cubre son:

    • Lista de adyacencia
    • Enumeración de ruta
    • Conjuntos nesteds
    • Tabla de cierre

    La Diapositiva 69 tiene una buena tabla que muestra los pros y los contras de cada método, por lo que le sugiero que primero mire esa diapositiva para ver qué enfoque podría funcionar para usted, luego regrese y vea los detalles de cómo implementarlo. Tenga en cuenta que el diseño que ha elegido (lista de adyacencia) es el único de los cuatro diseños presentados que dificulta la consulta de un subárbol.

    Habiendo dicho eso, si no puede cambiar su diseño o si desea seguir con la lista de adyacencia, entonces tengo que estar de acuerdo con Didier en que debe consultar el artículo de Quassnoi ” Consultas jerárquicas en MySQL” . Es un artículo muy claro y explica cómo escribir la consulta de manera eficiente.

    AFAIK, no es trivial hacer esto con MySQL.

    Aquí hay un buen conjunto de artículos al respecto:

    http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/