Выявление и удаление несвязанных записей
http://oooportal.ru/?cat=article&id=1344
Задача
У вас есть связанные таблицы (например, имеющие связь «главная-подчиненная»). Но вы подозреваете, что некоторые строки ни с чем не связаны и могут быть удалены.
Решение
Используйте LEFT JOIN
для определения отсутствия соответствий и удалите выявленные значения, применяя приемы из рецепта 12.20. Или используйте процедуру замены таблицы, которая выбирает связанные записи в новую таблицу и заменяет ею исходную.
Обсуждение
В предыдущем разделе было рассказано о том, как одновременно удалять связанные записи из нескольких таблиц, используя связь, существующую между таблицами. Иногда возникает обратная задача: необходимо удалить записи на основе отсутствия связи. Такие ситуации обычно возникают, если у вас есть таблицы, которые предполагаются сопоставленными друг другу, но некоторые из записей не связаны ни с какими записями другой таблицы.Это может произойти случайно, например, при удалении родительской записи без удаления соответствующих дочерних, или наоборот. А может быть и ожидаемым последствием какого-то умышленного действия. Предположим, что форум он-лайн использует родительскую таблицу, в которой перечислены все темы обсуждений, и дочернюю таблицу, в которой хранятся все сообщения по заданной теме. Если удалить из дочерней таблицы старые записи, это может привести к тому, что у какой-то родительской записи больше не будет ни одной дочерней. Отсутствие недавних сообщений по теме, вероятно, означает, что дискуссия затихла, и родительскую запись можно удалять из таблицы тем. Тогда вы удаляете все множество дочерних записей, четко осознавая, что операция может оставить родительские записи без дочерних и сделает их кандидатами на удаление.
Однако сейчас перед вами связанные таблицы с несвязанными записями. Для восстановления целостности таблиц необходимо выявить записи, не имеющие соответствий в связанной таблице, и удалить их:
Для выявления несвязанных записей используйте LEFT JOIN, так как это задача отсутствия соответствий.
Для удаления записей, идентификаторы которых не соответствуют записям связанной таблицы, используйте для удаления записей из нескольких связанных таблиц приемы.
В примерах будут использоваться таблицы дистрибутивов программного обеспечения swdist_head и swdist_item. Создайте таблицы в их исходном состоянии, используя сценарий swdist_create.sql из каталога joins дистрибутива recipes. Они будут выглядеть так:
На данный момент записи таблиц полностью соответствуют друг другу: для каждого значения dist_id из родительской таблицы существует как минимум одна дочерняя запись, а у каждой дочерней записи есть родительская.
Чтобы нарушить целостность связи для наших целей, удалим несколько строк из каждой таблицы:
В результате в обеих таблицах появятся несвязанные записи:
Беглый осмотр показывает, что только для дистрибутива 3 есть записи в двух таблицах. Дистрибутивы 2 и 5 из таблицы swdist_head не сопоставлены никаким записям из таблицы swdist_item. И наоборот, дистрибутивам 1 и 4 таблицы swdist_item не соответствуют никакие записи из таблицы swdist_head.
Теперь необходимо выявить несвязанные записи (каким-то способом, отличным от визуального контроля) и удалить их. Это задача для LEFT JOIN. Например, чтобы найти родительские записи без дочерних в таблице swdist_head, используем такой запрос:
И наоборот, чтобы найти идентификаторы «осиротевших» дочерних записей из таблицы swdist_item, поменяйте таблицы местами:
Заметьте, что в данном случае идентификатор появится в списке несколько раз, если у отсутствующего родителя было несколько потомков. В зависимости от способа удаления несвязанных записей вы можете захотеть использовать DISTINCT для выбора идентификатора каждой несвязанной дочерней записи только единожды:
После выявления несвязанных записей остается только избавиться от них. Можно применить один из способов:
• Использование идентификаторов в многотабличном предложении DELETE. Вы будете одновременно удалять строки только из одной таблицы, но синтаксис этой формы DELETE все же удобен, так как он позволяет идентифицировать удаляемые записи путем соединения связанных таблиц.
• Запуск программы, которая выбирает несвязанные идентификаторы и использует их для формирования предложений DELETE.Чтобы использовать многотабличное предложение DELETE для удаления несвязанных записей, просто возьмите предложение SELECT, используемое для выявления этих записей, и замените все начало до ключевого слова FROM на DELETE имя_таблицы. Например, предложение SELECT, выбирающее родительские записи без дочерних, выглядит так:
Соответствующее предложение DELETE будет таким:
Запрос, определяющий потомков без родителей, выглядит так:
Соответствующее предложение DELETE удаляет их:
Чтобы удалить несвязанные записи из программы, выберите список идентификаторов и преобразуйте его в набор предложений DELETE. Рассмотрим программу на Perl, которая делает это сначала для родительской таблицы, а затем для дочерней:
Программа использует IN() для удаления всех необходимых записей из указанной таблицы разом.
Для формирования предложений DELETE также можно использовать mysql; сценарий, показывающий, как это делается, приведен в каталоге joins дистрибутива recipes.
Другой подход к решению задачи реализуется процедурой замены таблицы. Этот метод подходит к вопросу с другой стороны. Вместо того чтобы находить и удалять несвязанные записи, будем находить и хранить связанные. Например, можно использовать соединение для выбора связанные записей в новую таблицу. Затем заменить ею исходную таблицу. Несвязанные записи не подхватываются соединением, то есть фактически удаляются при замене исходной таблицы новой.
Процедура замены таблицы работает так. Для таблицы swdist_head создаем новую таблицу с той же структурой:
Затем выбираем в таблицу tmp те записи swdist_head, для которых есть соответствия в таблице swdist_item:
Обратите внимание на то, что запрос использует INSERT IGNORE; родительская запись может соответствовать нескольким дочерним, но нам нужен только один экземпляр ее идентификатора. (Признаком неиспользования IGNORE является завершение запроса с ошибкой типа «duplicate key».)
Наконец, заменяем исходную таблицу новой:
Процедура замены дочерней таблицы таблицей, содержащей только связанные дочерние записи, аналогична, но без необходимости использования IGNORE – каждому потомку соответствует только один предок:
Last updated