Блог

Главная - Вопросы по программированию - Лучший способ иерархически удалять элементы из таблицы БД в mysql?

Лучший способ иерархически удалять элементы из таблицы БД в mysql?

#mysql #sql

#mysql #sql

Вопрос:

У меня есть таблица категорий базы данных. Двумя его столбцами являются ‘category_id’ и ‘parent_category’, которые я использую для удаления. Значения этих столбцов выровнены как :

введите описание изображения здесь

например, если я удаляю ‘category_id’ 10, то все его дочерние элементы (на любую глубину) 11,12,13 также должны быть удалены. пожалуйста, предложите мне лучший запрос mysql.

заранее спасибо.

Ответ №1:

Кажется, что самый простой способ заключается в следующем:

  1. Создайте ограничение внешнего ключа между столбцом parent_category и category_id ;

  2. Установите каскадное удаление включенным.

     create table yourTable
( ...
  add constraint
  foreign key (parent_category)
  references yourTable(category_id)
  on delete cascade
)

Это решит вашу проблему совершенно без усилий.

Комментарии:

1. спасибо, это работает, но мне пришлось добавить строку с category_id =0 .

2. Хорошо. Я не думаю, что это действительно необходимо, но я не знаю конкретной ситуации. Вас это беспокоит? Попробуйте сделать parent_category обнуляемым. Это должно сработать.

3. Потому что категории с 0 parent_ids были здесь родительскими.

4. Это кажется разумным. Вы также можете установить для них значение null, если хотите.

5. Теперь это идеально 🙂 . это был тот NULL, которого мне не хватало.

Ответ №2:

Если вы не хотите использовать внешние ключи, вы можете добавить столбец parent_path 

 ALTER TABLE `table`
ADD `parent_path` varchar(255) NOT NULL DEFAULT ''
AFTER `parent_category`, COMMENT='';

Теперь создайте процедуру, которая создает parent_path 

 DELIMITER $$
DROP PROCEDURE IF EXISTS UpdateParentPath$$

CREATE PROCEDURE UpdateParentPath()
    BEGIN
        DECLARE count INT;
        SET count = 1;

        WHILE count > 0 DO
            UPDATE `table` as `t`
                LEFT JOIN `table` as `p` ON `t`.`parent_category` = `p`.`category_id`
            SET `t`.`parent_path` = CONCAT(`p`.`parent_path`, IF(`p`.`parent_path` = '', '', ','), `p`.`category_id`);

            SELECT ROW_COUNT() INTO count;
        END WHILE;
    END$$
DELIMITER ;

Вызовите эту процедуру

 CALL UpdateParentPath();

А теперь… наконец… вы начинаете удалять

 DELETE FROM `table` WHERE `parent_category` = '9' OR `parent_path` LIKE '%,9,%'

Перед каждым удалением или после вставки / обновления необходимо вызывать UpdateParentPath процедуру.