оригинал:Мик Хиллиер.com/articles/horse…
представлять
Большинство разработчиков в какой-то момент имели дело с иерархическими данными в базах данных SQL и понимают, что управление иерархическими данными — это не то, в чем реляционная база данных хороша. Таблица в реляционной базе данных не является иерархической (например, XML), а представляет собой простой мозаичный список. Иерархические данные имеют отношения родитель-потомок, однако таблица реляционной базы данных не может представлять их естественным образом.
В нашем введении в эту тему иерархические данные — это коллекция, в которой каждый элемент имеет одного родителя и 0 или более дочерних элементов (за исключением корневого узла. У него нет родителя). Иерархические данные широко используются во многих приложениях баз данных. Включает форумы и списки рассылки, диаграммы организации бизнеса, таксономии управления контентом и таксономии продуктов. Мы будем использовать следующую иерархию категорий продуктов из виртуального магазина электроники, чтобы представить нашу тему.
Эти категории образуют иерархию, очень похожую на ранее упомянутые примеры. В этой статье мы рассмотрим две модели работы с иерархическими данными в MySQL. Сначала мы начнем с традиционной модели списка смежности.
Модель списка смежности
Обычно категории в приведенном выше примере будут храниться в таблице, подобной следующей (я включу полные операторы создания и вставки, чтобы вы могли следовать им)
CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL
);
INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);
SELECT * FROM category ORDER BY category_id;
+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | ELECTRONICS | NULL |
| 2 | TELEVISIONS | 1 |
| 3 | TUBE | 2 |
| 4 | LCD | 2 |
| 5 | PLASMA | 2 |
| 6 | PORTABLE ELECTRONICS | 1 |
| 7 | MP3 PLAYERS | 6 |
| 8 | FLASH | 7 |
| 9 | CD PLAYERS | 6 |
| 10 | 2 WAY RADIOS | 6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)
В модели списка смежности каждый элемент списка содержит указатель на его родительский узел. Самый верхний элемент в этом примереelectronicsЗначение родительского узла равно NULL. Преимущество модели списка смежности состоит в том, что она проста и понятна.FLASHдаMP3 PLAYERSдети, тоMP3 PLAYERSОпять такиPORTABLE ELECTRONICSребенок,PORTABLE ELECTRONICSОпять такиELECTRONICSдети. В то время как модель списка смежности может быть довольно легко реализована в коде на стороне клиента, использование чистого SQL создает много проблем.
получить полное дерево
При работе с иерархическими данными первой распространенной задачей является отображение всего дерева, обычно с отступом. Самый распространенный способ сделать это в чистом SQl — использовать自联接(self-join)
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';
+-------------+----------------------+--------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS | TUBE | NULL |
| ELECTRONICS | TELEVISIONS | LCD | NULL |
| ELECTRONICS | TELEVISIONS | PLASMA | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)
Найти все листовые узлы
Мы можем найти все листовые узлы (узлы без дочерних элементов) с помощью запроса LEFT JOIN.
SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;
+--------------+
| name |
+--------------+
| TUBE |
| LCD |
| PLASMA |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+--------------+
получить путь
Самосоединения также позволяют нам увидеть полный путь в иерархии:
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';
+-------------+----------------------+-------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+-------------+-------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
+-------------+----------------------+-------------+-------+
1 row in set (0.01 sec)
Основной недостаток такого подхода заключается в том, что вам необходимо выполнять самосоединение на каждом уровне иерархии, что естественным образом снижает производительность запросов по мере увеличения количества уровней и усложнения самосоединения.
Подводные камни модели списка смежности
Использование модели списка смежности в чистом SQL может быть затруднено. Прежде чем смотреть полный путь категории, нам нужно знать, на каком уровне она находится. Кроме того, нам нужно позаботиться об удалении узлов. Потому что возможно, что целое поддерево будет потеряно во время обработки (удалитьportable electronicsклассифицированы, его дети останутся сиротами). Некоторые из этих недостатков можно устранить с помощью клиентского кода или хранимых процедур. Используя язык программирования, мы можем пройти вверх по дереву, чтобы получить полное дерево или один путь. Мы также можем удалить узлы, не вызывая сирот, продвигая дочерний элемент, а затем перемещая оставшиеся дочерние элементы, чтобы они указывали на нового родителя.
Модель вложенного набора
В этой статье я хочу выделить альтернативный подход, обычно называемый嵌套集模型(The Nested Set Model). В модели вложенных множеств мы смотрим на нашу иерархию в новом свете. Не в узлах и строках, а во вложенных контейнерах. Попробуйте описать категорию электроники следующим образом:
Обратите внимание, как поддерживается наша иерархия, потому что родительские категории содержат своих дочерних. Мы представляем эту форму иерархии в таблице, используя левое и правое значения для представления вложенности узлов:
CREATE TABLE nested_category (
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL
);
INSERT INTO nested_category VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
(4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
(9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);
SELECT * FROM nested_category ORDER BY category_id;
+-------------+----------------------+-----+-----+
| category_id | name | lft | rgt |
+-------------+----------------------+-----+-----+
| 1 | ELECTRONICS | 1 | 20 |
| 2 | TELEVISIONS | 2 | 9 |
| 3 | TUBE | 3 | 4 |
| 4 | LCD | 5 | 6 |
| 5 | PLASMA | 7 | 8 |
| 6 | PORTABLE ELECTRONICS | 10 | 19 |
| 7 | MP3 PLAYERS | 11 | 14 |
| 8 | FLASH | 12 | 13 |
| 9 | CD PLAYERS | 15 | 16 |
| 10 | 2 WAY RADIOS | 17 | 18 |
+-------------+----------------------+-----+-----+
из-заleftа такжеrightявляется зарезервированным словом в MySQL, поэтому мы используемlftа такжеrgt. пожалуйста вDev.MySQL.com/doc/MySQL/О…См. полный список зарезервированных слов.
Итак, как мы определяем значение левого и правого? Мы начинаем с внешних узлов в крайнем левом углу и нумеруем до упора вправо.
Этот дизайн также может быть применен к типичному дереву:
Когда мы имеем дело с деревом, мы делаем это слой за слоем слева направо. Прежде чем присвоить узлу правый номер и двигаться вправо, мы спускаемся к его дочерним узлам. Этот метод называетсяалгоритм обхода предварительного заказа.
получить полное дерево
Мы можем получить полное дерево, используя самосоединение, которое связывает родительские узлы с дочерними узлами. Его принцип основан наlftзначение должно быть в его отцеlftа такжеrgtмежду значениями.
SELECT node.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;
+----------------------+
| name |
+----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+----------------------+
В отличие от предыдущего примера модели списка смежности, этому запросу не нужно заботиться об уровне дерева. Нам также не нужно обращать внимание на значение rgt узла в предложении BETWEEN, потому что оно должно находиться в пределах того же родителя, что и значение lft.
Найти все листовые узлы
Поиск всех конечных узлов в модели вложенного множества проще, чем использование метода левого соединения в модели списка смежности. Если вы внимательно посмотрите на таблицу nested_category, то можете заметить, что значения lft и rgt листовых узлов являются последовательными числами. Итак, чтобы найти листовые узлы, мы ищем те узлы, где rgt = lft + 1.
SELECT name
FROM nested_category
WHERE rgt = lft + 1;
+--------------+
| name |
+--------------+
| TUBE |
| LCD |
| PLASMA |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+--------------+
получить путь
Используя модель вложенного набора, мы можем получить путь без использования нескольких самосоединений:
SELECT parent.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'FLASH'
ORDER BY parent.lft;
+----------------------+
| name |
+----------------------+
| ELECTRONICS |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
+----------------------+
Найдите глубину узла
Мы уже знаем, как отображать все дерево уроков, но что, если мы хотим получить глубину каждого узла в дереве, чтобы лучше определить иерархию узлов? Это можно сделать, добавив функцию COUNT и предложение GROUP BY в существующий запрос, отображающий все дерево.
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+----------------------+-------+
| name | depth |
+----------------------+-------+
| ELECTRONICS | 0 |
| TELEVISIONS | 1 |
| TUBE | 2 |
| LCD | 2 |
| PLASMA | 2 |
| PORTABLE ELECTRONICS | 1 |
| MP3 PLAYERS | 2 |
| FLASH | 3 |
| CD PLAYERS | 2 |
| 2 WAY RADIOS | 2 |
+----------------------+-------+
Мы также можем использовать значения глубины с функциями Concat и Repeat String для имени категорий отступлений:
SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+-----------------------+
Конечно, в клиентском приложении вы можете предпочесть использовать значение глубины для непосредственного отображения иерархии. Веб-разработчики могут перебирать все дерево в цикле, а затем добавлять соответствующие значения глубины по мере увеличения и уменьшения значения глубины.<li></li>а также<ul></ul>Этикетка.
глубина поддерева
Когда нам нужна информация о глубине поддерева, мы не можем ограничить узел или родительскую таблицу в самообъединении, потому что это испортит наш результат. Вместо этого мы добавляем третье самосоединение вместе с подзапросом, чтобы определить глубину, которая будет новым началом поддерева:
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;
+----------------------+-------+
| name | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 2 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+-------+
Любой узел, включая корневой узел, может использовать эту функцию по имени. Значение глубины всегда будет относительно именованного узла.
Найти непосредственных потомков узла
Представьте, что вы выставляете ассортимент электронных товаров на веб-сайте розничного продавца. Когда пользователь нажимает на категорию, вы хотите отобразить продукты в этой категории и ее непосредственных подкатегориях, а не все дерево категорий под ней. Для этого нам нужно отобразить узел и его ближайших дочерних элементов, не углубляясь в детали. Например, при показе категории ПОРТАТИВНАЯ ЭЛЕКТРОНИКА мы хотим показывать MP3-ПЛЕЕРЫ, ПРОИГРЫВАТЕЛИ КОМПАКТ-ДИСК и ДВУХСТОРОННИЕ РАДИОСТАНЦИИ, но не ВСПЫШКИ.
Это легко сделать, добавив предложение HAVING к предыдущему запросу.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;
+----------------------+-------+
| name | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+-------+
Если вы не хотите показывать родительский узел, тоHAVING depth <= 1изменить наHAVING depth = 1.
Агрегатные функции во вложенных множествах
Добавим продукт для демонстрации агрегатных функций:
CREATE TABLE product
(
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40),
category_id INT NOT NULL
);
INSERT INTO product(name, category_id) VALUES('20" TV',3),('36" TV',3),
('Super-LCD 42"',4),('Ultra-Plasma 62"',5),('Value Plasma 38"',5),
('Power-MP3 5gb',7),('Super-Player 1gb',8),('Porta CD',9),('CD To go!',9),
('Family Talk 360',10);
SELECT * FROM product;
+------------+-------------------+-------------+
| product_id | name | category_id |
+------------+-------------------+-------------+
| 1 | 20" TV | 3 |
| 2 | 36" TV | 3 |
| 3 | Super-LCD 42" | 4 |
| 4 | Ultra-Plasma 62" | 5 |
| 5 | Value Plasma 38" | 5 |
| 6 | Power-MP3 128mb | 7 |
| 7 | Super-Shuffle 1gb | 8 |
| 8 | Porta CD | 9 |
| 9 | CD To go! | 9 |
| 10 | Family Talk 360 | 10 |
+------------+-------------------+-------------+
Теперь давайте напишем оператор запроса, который может получить дерево классификации и соответствующее количество продуктов:
SELECT parent.name, COUNT(product.name)
FROM nested_category AS node ,
nested_category AS parent,
product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.category_id = product.category_id
GROUP BY parent.name
ORDER BY node.lft;
+----------------------+---------------------+
| name | COUNT(product.name) |
+----------------------+---------------------+
| ELECTRONICS | 10 |
| TELEVISIONS | 5 |
| TUBE | 2 |
| LCD | 1 |
| PLASMA | 2 |
| PORTABLE ELECTRONICS | 5 |
| MP3 PLAYERS | 2 |
| FLASH | 1 |
| CD PLAYERS | 2 |
| 2 WAY RADIOS | 1 |
+----------------------+---------------------+
Это типичный запрос всего дерева, в котором мы добавляем COUNT и GROUP BY, а также ссылку на таблицу продуктов и соединения между узлами и таблицей продуктов в предложении WHERE. Как видите, каждая категория имеет соответствующий номер, а количество подкатегорий отражается в родительской категории.
добавить новый узел
Теперь, когда мы узнали, как запрашивать дерево, теперь мы должны увидеть, как обновить дерево, добавив новый узел. Давайте еще раз взглянем на вложенный граф множеств:
Если мы хотим добавить новый узел между узлами ТЕЛЕВИЗОР и ПОРТАТИВНАЯ ЭЛЕКТРОНИКА, его значения lft и rgt должны быть равны 10 и 11 соответственно, а всем его правым узлам необходимо увеличить значения lft и rgt на 2. Затем мы добавим новые узлы с соответствующими значениями lft и rgt. Хотя это можно сделать с помощью хранимых процедур в MYSQL 5, я предполагаю, что большинство читателей используют версию 4.1, поскольку это последняя стабильная версия. Так что я буду использоватьLOCK TABLESоператор, чтобы изолировать оператор запроса:
LOCK TABLE nested_category WRITE;
SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);
UNLOCK TABLES;
Мы можем использовать наш запрос дерева с отступом для проверки вложенных результатов:
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| GAME CONSOLES |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+-----------------------+
Если мы просто хотим добавить к узлу узел без дочерних элементов, нам нужно немного изменить хранимую процедуру. Давайте добавим новый узел FRS к узлу 2 WAY RADIOS:
LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft FROM nested_category
WHERE name = '2 WAY RADIOS';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;
INSERT INTO nested_category(name, lft, rgt) VALUES('FRS', @myLeft + 1, @myLeft + 2);
UNLOCK TABLES;
В этом примере мы расширяем значение узла справа от нового родительского узла, а затем размещаем узел. Как видите, наш новый узел теперь вложен:
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| GAME CONSOLES |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
| FRS |
+-----------------------+
удалить узел
Последней фундаментальной задачей в модели вложенного набора является удаление узла. Порядок действий при удалении узла зависит от его положения в иерархии; проще удалить листовой узел, чем узел с дочерними элементами, потому что нам нужно иметь дело с узлами-сиротами.
При удалении конечного узла процесс фактически противоположен добавлению узла. Мы удаляем этот узел и уменьшаем ширину узла справа от него:
LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';
DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;
UNLOCK TABLES;
Мы выполняем запрос дерева с отступом, чтобы подтвердить, что узел был удален без нарушения иерархии.
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
| FRS |
+-----------------------+
Следующий метод работает для удаления узла и всех его дочерних элементов:
LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'MP3 PLAYERS';
DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;
UNLOCK TABLES;
Еще раз мы запрашиваем, было ли успешно удалено все поддерево:
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| CD PLAYERS |
| 2 WAY RADIOS |
| FRS |
+-----------------------+
В других сценариях мы должны удалить только родительский узел, а не дочерний. В некоторых случаях дочерние узлы следует повысить до того же уровня, что и родительский узел, который был удален:
LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'PORTABLE ELECTRONICS';
DELETE FROM nested_category WHERE lft = @myLeft;
UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight;
UNLOCK TABLES;
В этом примере мы вычитаем 2 из всех элементов справа от узла (потому что, если бы у него не было дочерних элементов, его ширина была бы равна 2) и 1 из всех его дочерних элементов (чтобы заполнить пробел, вызванный уменьшенным значением lft родителя). . Теперь давайте еще раз подтвердим, что элемент был поднят:
SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+---------------+
| name |
+---------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| CD PLAYERS |
| 2 WAY RADIOS |
| FRS |
+---------------+
Другие сценарии при удалении узлов включают повышение уровня дочернего узла до его родительского и перемещение дочерних узлов под одноуровневыми узлами родителя, но из-за объема статьи эти сценарии не будут рассматриваться в этой статье.
Некоторые последние мысли
Хотя я надеюсь, что информация, представленная в этой статье, может быть вам полезна, концепция вложенных наборов SQL была около 10 лет, поэтому есть много книг в Интернете, которые содержат много дополнительной информации. Я лично думаю, что самое полное и подробное введение в иерархию управления - это книга под названиемДеревья и иерархии Джо Селко в SQL для Smartiesкнига. Она была написана Джо Селко, очень уважаемым автором в области продвинутого SQL. Модель вложенных множеств часто приписывают Джо Селко, который на сегодняшний день является самым плодовитым автором по этому вопросу. Я нашел книгу Селко бесценным ресурсом в моих исследованиях, поэтому я настоятельно рекомендую ее. Эта книга охватывает многие дополнительные темы, которые я не рассмотрел в этой статье, и в то же время предоставляет множество способов управления иерархиями, включая модели списков смежности и модели вложенных наборов.
В следующем разделе «Ссылки/ресурсы» я перечисляю некоторые веб-ресурсы, которые могут быть полезны для ваших исследований по управлению иерархическими данными, включая пару ресурсов, связанных с PHP, и предварительно созданную библиотеку PHP для работы с вложенными множествами в MySQL. Те, кто в настоящее время используют модель списка смежности и хотят поэкспериментировать с моделью вложенных множеств, могут сделать это с помощью перечисленных ниже ресурсов.Storing Hierarchical Data in a DatabaseНайдите пример кода для преобразования между ними.
Ссылки / Ресурсы
- Деревья и иерархии Джо Селко в SQL для Smarties - ISBN 1-55860-920-2
- Storing Hierarchical Data in a Database
- A Look at SQL Trees
- SQL Lessons
- Nontraditional Databases
- Trees in SQL
- Trees in SQL (2)
- Converting an adjacency list model to a nested set model Nested Sets in PHP Demonstration (German) A Nested Set Library in PHP