У меня есть иерархическая система, которая хранит уровень и родительский идентификатор элемента в базе данных. Я пытаюсь выяснить, как я могу найти все дочерние строки того, который выбирает пользователь. Вот пример структуры таблицы:
+----+---------+----------+-------+ | Id | label | parentid | level | +----+---------+----------+-------+ | 1 | honda | 0 | 1 | | 2 | accord | 1 | 2 | | 3 | civic | 1 | 2 | | 4 | element | 1 | 2 | | 5 | v4 | 2 | 3 | | 6 | v6 | 2 | 3 | +----+---------+----------+-------+ Итак, если кто-то удалит honda , как я могу удалить все иерархию? База данных будет только до уровня 3.
Refer, Рекурсивный MySQL-запрос с реляционным innoDB
Вчера я опубликовал ответ для того же
Обновить:
Ниже приведена function основанная на вышеуказанной ссылке, но была настроена для ваших требований DELETE
CREATE PROCEDURE `delete_relations`(`_id` INT) BEGIN DECLARE delete_row_ids varchar(200); DECLARE id, id2 varchar(200); DECLARE rows_affected int; SET delete_row_ids = _id; SET id = _id; SET id2 = id; WHILE id2 IS NOT NULL DO SET id = NULL; SELECT GROUP_CONCAT( hierarchical_data.id ), CONCAT( GROUP_CONCAT( hierarchical_data.ID ),',',delete_row_ids) INTO id, delete_row_ids FROM hierarchical_data WHERE FIND_IN_SET( parentid , id2 ) GROUP BY parentid ; SET id2 = id; END WHILE; DELETE FROM hierarchical_data where FIND_IN_SET(hierarchical_data.id, delete_row_ids); SELECT row_count() INTO rows_affected; if rows_affected > 0 THEN SELECT delete_row_ids as row_ids_deleted; ELSE SELECT 'NO_ROWS_DELETED'; END IF; END//
SQLFiddle
использование
CALL delete_relations(1)
Чтобы удалить все записи и их отношения для id = 1 honda
Надеюсь, что это полезно
Заменить :id помощью :id корневой записи, который необходимо удалить, в следующем:
DELETE FROM table WHERE id = :id OR id IN ( -- first level SELECT DISTINCT id sid FROM table WHERE parentid = :id -- second level UNION SELECT DISTINCT t2.id sid FROM table t1 INNER JOIN table t2 ON t1.id = t2.parentid WHERE t1.parentid = :id -- third level UNION SELECT DISTINCT t3.id sid FROM table t1 INNER JOIN table t2 ON t1.id = t2.parentid INNER JOIN table t3 ON t2.id = t3.parentid WHERE t1.id = :id )
Обратите внимание, что у вас есть 3 уровня, но корневой узел занимает один из них, а это значит, что вам нужно только выбрать детей и внуков. Вышеуказанный запрос делает еще один уровень (третий), поэтому вы можете удалить часть после -- third level до закрывающей круглой скобки.
Предполагая, что вы находитесь на MySQL 5, у которого есть триггеры, вы можете написать небольшой перед триггером удаления, чтобы достичь этого легко. Я точно не проверял синтаксис, но будет соответствовать более или менее следующим:
CREATE TRIGGER tr_tablename_bd BEFORE DELETE ON tablename FOR EACH ROW BEGIN DELETE FROM tablename WHERE parentID = deleted.id END;
Вышеприведенный код также будет работать с неограниченными уровнями, а не только с 3 уровнями. Вам нужно будет удалить строку, и триггер будет удалять строки данных на нижележащих уровнях или строках, которые отмечают удаленную строку как parentID.
Однако я смутно помню проблему с MySQL, в которой ему не удалось запустить каскадные триггеры. Значение MySQL не запускает триггер из-за действия на другой триггер. Если эта ошибка все еще существует в выпуске MySQL, который вы используете (или даже последнее издание), тогда этот код не будет работать. Тем не менее, теоретически это совершенно нормально и будет работать над любой другой базой данных, в которой есть триггеры. Для MySQL вам просто нужно проверить, удалось ли решить эту ошибку!