Каков наилучший способ очистить самореферентную таблицу MySQL?

У меня есть самореферентная таблица MySQL с рекурсивным parent_id:

CREATE TABLE `recursive` (
  `id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) default NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `data_categorysource_parent_id` (`parent_id`),
  CONSTRAINT `parent_id_refs_id_627b4293`
    FOREIGN KEY (`parent_id`) REFERENCES `data_categorysource` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Во время тестирования я хочу очистить его, но TRUNCATE не работает:

TRUNCATE `recursive` 
/* SQL Error: Cannot delete or update a parent row: a foreign key
constraint fails...

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

Есть ли простой способ обойти это? Я не могу DROP создать таблицу и легко воссоздать ее, поскольку на нее ссылаются другие таблицы (я уже обрезал их, поэтому там не должно быть проблем с целостностью данных).


mysql sql truncate self-reference
person Mat    schedule 05.03.2009    source источник
comment
Если вы УДАЛИТЕ ИЗ recursive ORDER BY id, у вас все получится. Если, однако, у вас есть самоссылающиеся строки, у вас почти нет элегантного выбора: bugs.mysql.com/bug.php?id=7412. Тот, который временно отключает foreign_keys, является лучшим.   -  person Andras Gyomrey    schedule 19.11.2011
comment
Один из немногих случаев, когда оракул работает лучше, так как в оракуле вы можете отказаться от проверки контрастов до тех пор, пока транзакция не будет зафиксирована.   -  person Ian Ringrose    schedule 19.12.2013

Ответы (6)


arrow_upward
34
arrow_downward

Почему нет:

UPDATE 'recursive' SET 'parent_id' = NULL WHERE 'parent_id' IS NOT NULL;
DELETE FROM 'recursive';

?

person Craig Stuntz    schedule 05.03.2009
comment
Вопрос Каков наилучший способ очистить самореферентную таблицу MySQL? или его форма появляется на многих интернет-сайтах и ​​даже несколько раз дублируется на SO, и все же так мало вопросов имеют этот простой, элегантный и правильный ответ. +1 - person Barzee; 28.02.2015

arrow_upward
18
arrow_downward

Если вы просто хотите очистить все это для целей тестирования, используйте:

SET FOREIGN_KEY_CHECKS = 0;

// Execute Query

SET FOREIGN_KEY_CHECKS = 1;

Это полностью обходит любые проверки внешнего ключа.

person Noah Goodrich    schedule 05.03.2009

arrow_upward
1
arrow_downward

Ну, вы можете добавить ON DELETE CASCADE к определению FOREIGN KEY... хотя бы временно. Это позволит вам усечь таблицу, удалив сначала строки, на которые есть ссылки.

Существуют и другие типы ON DELETE, такие как Что ж; по умолчанию ON DELETE NO ACTION.

person Powerlord    schedule 05.03.2009

arrow_upward
0
arrow_downward

Или просто удалите (рекурсивное) ограничение внешнего ключа, затем обрежьте таблицу, а затем повторно добавьте ограничение.

person BradC    schedule 05.03.2009

arrow_upward
-1
arrow_downward

Неоднократно выбирайте строки, которые не отображаются как родительские, и удаляйте их, пока таблица не станет пустой. (Предположим, что циклов нет...)

person Assaf Lavie    schedule 05.03.2009

arrow_upward
-2
arrow_downward

удалить из таблицы_1, где дата (таблица_1_TIME) ‹ (выберите T.t_Date из (выберите max (дата (таблица_1_TIME)) как t_Date из таблицы_1 ) как T)

person BAPU SHINDE    schedule 19.12.2013
comment
Пожалуйста, отформатируйте код более четко и укажите идентификаторы исходного вопроса. - person gwaigh; 19.12.2013