Действительно ли возможно запросить таблицу древовидной структуры в MySQL в едином запросе на глубину?

Техника Celko (вложенные наборы) довольно хороша. Я также использовал таблицу смежности с полями "предок" и "потомок" и "расстояние" (например, у прямых детей/родителей есть расстояние 1, у внуков/бабушки и дедушки есть расстояние 2, и т.д.).

Это должно сохраняться, но довольно легко сделать для вставок: Вы используете транзакцию, затем помещаете прямую ссылку (родитель, ребенок, distance=1) в таблицу, затем ВСТАВЛЯЕТЕ, ИГНОРИРУЮТ ВЫБОР существующего parent& дети путем добавления расстояний (я могу потянуть SQL, когда у меня есть шанс), который хочет индекс на каждом из этих 3 полей для производительности. То, где этот подход становится ужасным, для удалений..., в основном необходимо отметить все объекты, которые были затронуты и затем восстанавливают их. Но преимущество этого состоит в том, что это может обработать произвольные графы без петель, тогда как вложенная модель набора может только сделать прямые иерархии (например, каждый объект кроме корня имеет одного и только одного родителя).

SQL не является полным по Тьюрингу языком, что означает, что Вы не собираетесь быть способными выполнить этот вид цикличного выполнения. Можно сделать некоторые очень умные вещи с SQL и древовидными структурами, но я не могу думать о способе описать строку, которая имеет определенный идентификатор "в его иерархии" для иерархии произвольной глубины.

Ваш лучший выбор - что-то вроде того, какой предложенный @Dan, который должен просто проложить себе путь через дерево в некотором другом, более способном языке. Можно на самом деле генерировать строку запроса на языке общего назначения с помощью цикла, где запрос является просто некоторой замысловатой серией соединений (или подзапросы), который отражает глубину иерархии, которую Вы ищете. Это было бы более эффективно, чем цикличное выполнение и несколько запросов.

Вы почти определенно собираетесь хотеть использовать некоторую рекурсию для этого. И если бы Вы делаете это, тогда это было бы тривиально (на самом деле легче) для получения всего дерева, а не битов его на фиксированную глубину.

В действительно грубом псевдокоде Вы захотите что-то вдоль этих строк:

getChildren(parent){
    children = query(SELECT * FROM table WHERE parent_id = parent.id)
    return children
}

printTree(root){
    print root
    children = getChildren(root)
    for child in children {
        printTree(child)
    }
}

, Хотя на практике Вы редко хотели бы сделать что-то вроде этого. Это будет довольно неэффективно, так как это выполняет один запрос для каждой строки в таблице, таким образом, это только будет разумно или для маленьких таблиц или для деревьев, которые не вкладываются слишком глубоко. Честно говоря, в любом случае Вы, вероятно, хотите ограничить глубину.

Однако, учитывая популярность этих видов структуры данных, может быть некоторый материал MySQL, чтобы помочь Вам с этим, конкретно сократить числа запросов, которые необходимо сделать.

Редактирование: думая об этом, имеет очень мало смысла делать все эти запросы. Если Вы читаете всю таблицу так или иначе, то можно просто хлебать все это в RAM - предположение, что это является достаточно маленьким!