Как выбрать всех родителей node в иерархической таблице mysql?

У меня есть таблица MySQL, которая представляет данные для компонента GUI дерева, вот структура моей таблицы:

treeTable ( 
  id INT NOT NULL PRIMARY KEY, 
  parentId INT, 
  name VARCHAR(255) 
);

parentId является внешним ключом для самостоятельной привязки.

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

Например, предположим, что моя таблица заполнила эти данные:

1, null, 'root'
2, 1   , 'level_1'
3, 2   , 'level_2'

Теперь я хочу получить все родительские узлы node 3 (узлы 1 и 2) и вернуть набор результатов, содержащий все записи дерева. Кто-нибудь может мне помочь?

Ответы

Ответ 1

Хороший вопрос. В Oracle вы использовали бы что-то вроде CONNECT BY .

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

Ответ 2

Было аналогичное обсуждение, которое могло бы помочь в решении этой проблемы.

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

Ответ 3

Посмотрите здесь в разделе "Извлечение единого пути". Но лучше использовать подход вложенного набора, работать с деревом будет намного проще. Также я рекомендую читать " Деревья в базе данных - Расширенные структуры данных".

Ответ 4

Здесь также материализованные пути. Довольно простая концепция, которая действительно агностика базы данных. Намного проще управлять вставками и т.д., В отличие от вложенных наборов, вам не нужно знать, что вы - левые/правые узлы и т.д., Прежде чем вставлять.

Ответ 5

MySQL не поддерживает табличные функции 18.2.1. Сохраненный рутинный синтаксис (это то, что вам нужно, чтобы вернуть произвольный набор результатов).

Без них у вас есть три варианта:

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

Третий вариант имеет очень маленькие строки, даст хорошую производительность и позволит избежать искусственных ограничений.

parentTable (
    id INT NOT NULL,
    parentId INT NOT NULL
);

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