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

Ответ 1

Да, это возможно, это называется измененным обходным деревом предварительного порядка, как описано выше

Деревья и иерархии Джо Селько в SQL для Smarties

Здесь приведен рабочий пример (в PHP)

http://www.sitepoint.com/article/hierarchical-data-database/2/

Ответ 2

Вот несколько ресурсов:

• http://forums.mysql.com/read.php?10,32818,32818#msg-32818
• Управление иерархическими данными в MySQL
• http://lists.mysql.com/mysql/201896

В принципе, вам нужно будет сделать какой-то курсор в хранимой процедуре или запросе или создать таблицу смежности. Я бы избегал рекурсии за пределами db: в зависимости от того, насколько глубоко ваше дерево, это может стать очень медленным/отрывочным.

Ответ 3

Ответ Дэниела Бердсли не так уж плох, когда основные вопросы, которые вы задаете, - это "все мои дети" и "что все мои родители".

В ответ на Алекс Вайнштейн этот метод фактически приводит к меньшему обновлению узлов в родительском движении, чем в технике Celko. В методе Celko, если уровень 2 node в крайнем левом углу переходит на уровень 1 node в крайнем правом углу, то почти все node в дереве нуждаются в обновлении, а не только в node детях.

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

Я бы сохранил их так, чтобы запрос был

SELECT FROM table WHERE ancestors LIKE "1,2,6%"

Это означает, что mysql может использовать индекс в столбце "предки", который он не сможет сделать с ведущим%.

Ответ 4

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

Представьте, что у вас были записи, подобные этому (отступы подразумевают иерархию, а числа - id, предки.

• 1, "1"
  • 2, "2,1"
    • 5, "5,2,1"
    • 6, "6,2,1"
      • 7, "7,6,2,1"
      • 11, "11,6,2,1"
  • 3, "3,1"
    • 8, "8,3,1"
    • 9, "9,3,1"
    • 10, "10,3,1"

Затем выберите потомки id: 6, просто сделайте это

SELECT FROM table WHERE ancestors LIKE "%6,2,1"

Сохранение столбца предков в актуальном состоянии может быть больше проблем, чем это стоит вам, но это выполнимое решение в любой БД.

Ответ 5

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

Это нужно поддерживать, но довольно легко сделать для вставок: вы используете транзакцию, затем помещаете прямую ссылку (родительский, дочерний, расстояние = 1) в таблицу, а затем INSERT IGNORE SELECTION существующего родительского и ампер; дети, добавляя расстояния (я могу подтянуть SQL, когда у меня есть шанс), который хочет получить индекс для каждого из трех полей для производительности. Там, где этот подход становится уродливым, для удаления... вы в основном должны отмечать все элементы, которые были затронуты, а затем перестраивать их. Но преимуществом этого является то, что он может обрабатывать произвольные ациклические графики, тогда как вложенная модель набора может выполнять только прямые иерархии (например, каждый элемент, кроме корня, имеет один и только один родитель).

Ответ 6

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

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

Ответ 7

Это может быть сделано, и это не так сложно для SQL. Я ответил на этот вопрос и представил рабочий пример, используя здесь процедурный код mysql:

MySQL: как найти листы в определенном node

Бут: Если вы удовлетворены, вы должны отметить один из ответов, как принято.

Ответ 8

Я использовал процедуру "С эмулятором", описанную в https://stackoverflow.com/questions/27013093/recursive-query-emulation-in-mysql (предоставляется https://stackoverflow.com/users/1726419/yossico). До сих пор я получил очень хорошие результаты (производительность мудрая), но у меня нет большого количества данных или большого количества потомков для поиска в/для.

Ответ 9

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

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

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 помогут вам в этом, в частности, сократить количество запросов, которые вам нужно сделать.

Изменить: подумав об этом, очень мало смысла делать все эти запросы. Если вы все равно читаете всю таблицу, тогда вы можете просто разложить все это на ОЗУ - считая это достаточно маленьким!