MySQL. Выявление и удаление несвязанных записей
http://oooportal.ru/?cat=article&id=1344
Задача
У вас есть связанные таблицы (например, имеющие связь «главная-подчиненная»). Но вы подозреваете, что некоторые строки ни с чем не связаны и могут быть удалены.
Решение
Используйте LEFT JOIN для определения отсутствия соответствий и удалите выявленные значения, применяя приемы из рецепта 12.20. Или используйте процедуру замены таблицы, которая выбирает связанные записи в новую таблицу и заменяет ею исходную.
Обсуждение
В предыдущем разделе было рассказано о том, как одновременно удалять связанные записи из нескольких таблиц, используя связь, существующую между таблицами. Иногда возникает обратная задача: необходимо удалить записи на основе отсутствия связи. Такие ситуации обычно возникают, если у вас есть таблицы, которые предполагаются сопоставленными друг другу, но некоторые из записей не связаны ни с какими записями другой таблицы.Это может произойти случайно, например, при удалении родительской записи без удаления соответствующих дочерних, или наоборот. А может быть и ожидаемым последствием какого-то умышленного действия. Предположим, что форум он-лайн использует родительскую таблицу, в которой перечислены все темы обсуждений, и дочернюю таблицу, в которой хранятся все сообщения по заданной теме. Если удалить из дочерней таблицы старые записи, это может привести к тому, что у какой-то родительской записи больше не будет ни одной дочерней. Отсутствие недавних сообщений по теме, вероятно, означает, что дискуссия затихла, и родительскую запись можно удалять из таблицы тем. Тогда вы удаляете все множество дочерних записей, четко осознавая, что операция может оставить родительские записи без дочерних и сделает их кандидатами на удаление.
Однако сейчас перед вами связанные таблицы с несвязанными записями. Для восстановления целостности таблиц необходимо выявить записи, не имеющие соответствий в связанной таблице, и удалить их:
- Для выявления несвязанных записей используйте LEFT JOIN, так как это задача отсутствия соответствий.
- Для удаления записей, идентификаторы которых не соответствуют записям связанной таблицы, используйте для удаления записей из нескольких связанных таблиц приемы.
В примерах будут использоваться таблицы дистрибутивов программного обеспечения swdist_head и swdist_item. Создайте таблицы в их исходном состоянии, используя сценарий swdist_create.sql из каталога joins дистрибутива recipes. Они будут выглядеть так:
mysql> SELECT * FROM swdist_head;
+-----------+--------------+-----------+------------+
| dist_id | name | ver_num | rel_date |
+--------+---------------+-------------+------------+
| 1 | DB Gadgets | 1.59 | 1996-03-25 |
| 2 | NetGizmo | 3.02 | 1998-11-10 |
| 3 | DB Gadgets | 1.60 | 1998-12-26 |
| 4 | DB Gadgets | 1.61 | 1998-12-28 |
| 5 | NetGizmo | 4.00 | 2001-08-04 |
+-------+---------------+-----------+---------------+
mysql> SELECT * FROM swdist_item;
+-----------+-------------------+
| dist_id | dist_file |
+-----------+-------------------+
| 1 | README |
| 1 | db-gadgets.sh |
| 3 | README |
| 3 | README.linux |
| 3 | db-gadgets.sh |
| 4 | README |
| 4 | README.linux |
| 4 | README.solaris |
| 4 | db-gadgets.sh |
| 2 | README.txt |
| 2 | NetGizmo.exe |
| 5 | README.txt |
| 5 | NetGizmo.exe |
+-----------+-------------------+
На данный момент записи таблиц полностью соответствуют друг другу: для каждого значения dist_id из родительской таблицы существует как минимум одна дочерняя запись, а у каждой дочерней записи есть родительская.
Чтобы нарушить целостность связи для наших целей, удалим несколько строк из каждой таблицы:
mysql> DELETE FROM swdist_head WHERE dist_id IN (1,4);
mysql> DELETE FROM swdist_item WHERE dist_id IN (2,5);
В результате в обеих таблицах появятся несвязанные записи:
mysql> SELECT * FROM swdist_head;
+-----------+--------------+-----------+-------------+
| dist_id | name | ver_num | rel_date |
+-----------+--------------+-----------+-------------+
| 2 | NetGizmo | 3.02 | 1998-11-10 |
| 3 | DB Gadgets | 1.60 | 1998-12-26 |
| 5 | NetGizmo | 4.00 | 2001-08-04 |
+-----------+--------------+-----------+-------------+
mysql> SELECT * FROM swdist_item;
+-----------+-----------------+
| dist_id | dist_file |
+-----------+-----------------+
| 1 | README |
| 1 | db-gadgets.sh |
| 3 | README |
| 3 | README.linux |
| 3 | db-gadgets.sh |
| 4 | README |
| 4 | README.linux |
| 4 | README.solaris |
| 4 | db-gadgets.sh |
+-----------+-----------------+
Беглый осмотр показывает, что только для дистрибутива 3 есть записи в двух таблицах. Дистрибутивы 2 и 5 из таблицы swdist_head не сопоставлены никаким записям из таблицы swdist_item. И наоборот, дистрибутивам 1 и 4 таблицы swdist_item не соответствуют никакие записи из таблицы swdist_head.
Теперь необходимо выявить несвязанные записи (каким-то способом, отличным от визуального контроля) и удалить их. Это задача для LEFT JOIN. Например, чтобы найти родительские записи без дочерних в таблице swdist_head, используем такой запрос:
mysql> SELECT swdist_head.dist_id AS 'unmatched swdist_head IDs'
-> FROM swdist_head LEFT JOIN swdist_item
-> ON swdist_head.dist_id = swdist_item.dist_id
-> WHERE swdist_item.dist_id IS NULL;
+-----------------------------+
| unmatched swdist_head IDs |
+-----------------------------+
| 2 |
| 5 |
+-----------------------------+
И наоборот, чтобы найти идентификаторы «осиротевших» дочерних записей из таблицы swdist_item, поменяйте таблицы местами:
mysql> SELECT swdist_item.dist_id AS 'unmatched swdist_item IDs'
-> FROM swdist_item LEFT JOIN swdist_head
-> ON swdist_item.dist_id = swdist_head.dist_id
-> WHERE swdist_head.dist_id IS NULL;
+------------------------------+
| unmatched swdist_item IDs |
+------------------------------+
| 1 |
| 1 |
| 4 |
| 4 |
| 4 |
| 4 |
+------------------------------+
Заметьте, что в данном случае идентификатор появится в списке несколько раз, если у отсутствующего родителя было несколько потомков. В зависимости от способа удаления несвязанных записей вы можете захотеть использовать DISTINCT для выбора идентификатора каждой несвязанной дочерней записи только единожды:
mysql> SELECT DISTINCT swdist_item.dist_id AS 'unmatched swdist_item IDs'
-> FROM swdist_item LEFT JOIN swdist_head
-> ON swdist_item.dist_id = swdist_head.dist_id
-> WHERE swdist_head.dist_id IS NULL;
+-------------------------------+
| unmatched swdist_item IDs |
+-------------------------------+
| 1 |
| 4 |
+-------------------------------+
После выявления несвязанных записей остается только избавиться от них. Можно применить один из способов:
• Использование идентификаторов в многотабличном предложении DELETE. Вы будете одновременно удалять строки только из одной таблицы, но синтаксис этой формы DELETE все же удобен, так как он позволяет идентифицировать удаляемые записи путем соединения связанных таблиц.
• Запуск программы, которая выбирает несвязанные идентификаторы и использует их для формирования предложений DELETE.Чтобы использовать многотабличное предложение DELETE для удаления несвязанных записей, просто возьмите предложение SELECT, используемое для выявления этих записей, и замените все начало до ключевого слова FROM на DELETE имя_таблицы. Например, предложение SELECT, выбирающее родительские записи без дочерних, выглядит так:
SELECT swdist_head.dist_id AS 'unmatched swdist_head IDs'
FROM swdist_head LEFT JOIN swdist_item
ON swdist_head.dist_id = swdist_item.dist_id
WHERE swdist_item.dist_id IS NULL;
Соответствующее предложение DELETE будет таким:
DELETE swdist_head
FROM swdist_head LEFT JOIN swdist_item
ON swdist_head.dist_id = swdist_item.dist_id
WHERE swdist_item.dist_id IS NULL;
Запрос, определяющий потомков без родителей, выглядит так:
SELECT swdist_item.dist_id AS 'unmatched swdist_item IDs'
FROM swdist_item LEFT JOIN swdist_head
ON swdist_item.dist_id = swdist_head.dist_id
WHERE swdist_head.dist_id IS NULL;
Соответствующее предложение DELETE удаляет их:
DELETE swdist_item
FROM swdist_item LEFT JOIN swdist_head
ON swdist_item.dist_id = swdist_head.dist_id
WHERE swdist_head.dist_id IS NULL;
Чтобы удалить несвязанные записи из программы, выберите список идентификаторов и преобразуйте его в набор предложений DELETE. Рассмотрим программу на Perl, которая делает это сначала для родительской таблицы, а затем для дочерней:
#! /usr/bin/perl -w
use strict;
use lib qw(/usr/local/apache/lib/perl);
use Cookbook;
my $dbh = Cookbook::connect ();
# Определение идентификаторов родительских записей без дочерних
my $ref = $dbh->selectcol_arrayref (
"SELECT swdist_head.dist_id
FROM swdist_head LEFT JOIN swdist_item
ON swdist_head.dist_id = swdist_item.dist_id
WHERE swdist_item.dist_id IS NULL");
# selectcol_arrayref() возвращает ссылку на список. Преобразуем ссылку в список,
# который будет пуст, если $ref – это undef или указывает на пустой список.
my @val = ($ref ? @{$ref} : ());# Используем список идентификаторов для удаления записей по всем идентификаторам
# сразу. Если список пуст – не волнуемся, удалять нечего.
if (@val)
{
# формируем список заполнителей "?", разделенных запятыми, по одному на значение
my $where = "WHERE dist_id IN (" . join (",", ("?") x @val) . ")";
$dbh->do ("DELETE FROM swdist_head $where", undef, @val);
}
# Повторяем процедуру для дочерней таблицы. Используем SELECT DISTINCT,
# чтобы каждый идентификатор выбирался один раз.
$ref = $dbh->selectcol_arrayref (
"SELECT DISTINCT swdist_item.dist_id
FROM swdist_item LEFT JOIN swdist_head
ON swdist_item.dist_id = swdist_head.dist_id
WHERE swdist_head.dist_id IS NULL");
@val = ($ref ? @{$ref} : ());
if (@val)
{
# формируем список заполнителей "?", разделенных запятыми, по одному на значение
my $where = "WHERE dist_id IN (" . join (",", ("?") x @val) . ")";
$dbh->do ("DELETE FROM swdist_item $where", undef, @val);
}
$dbh->disconnect ();
exit (0);
Программа использует IN() для удаления всех необходимых записей из указанной таблицы разом.
Для формирования предложений DELETE также можно использовать mysql; сценарий, показывающий, как это делается, приведен в каталоге joins дистрибутива recipes.
Другой подход к решению задачи реализуется процедурой замены таблицы. Этот метод подходит к вопросу с другой стороны. Вместо того чтобы находить и удалять несвязанные записи, будем находить и хранить связанные. Например, можно использовать соединение для выбора связанные записей в новую таблицу. Затем заменить ею исходную таблицу. Несвязанные записи не подхватываются соединением, то есть фактически удаляются при замене исходной таблицы новой.
Процедура замены таблицы работает так. Для таблицы swdist_head создаем новую таблицу с той же структурой:
CREATE TABLE tmp
(
dist_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # идентификатор дистрибутива
name VARCHAR(40), # название дистрибутиваver_num NUMERIC(5,2), # номер версии
rel_date DATE NOT NULL, # дата выпуска
PRIMARY KEY (dist_id)
);
Затем выбираем в таблицу tmp те записи swdist_head, для которых есть соответствия в таблице swdist_item:
INSERT IGNORE INTO tmp
SELECT swdist_head.*
FROM swdist_head, swdist_item
WHERE swdist_head.dist_id = swdist_item.dist_id;
Обратите внимание на то, что запрос использует INSERT IGNORE; родительская запись может соответствовать нескольким дочерним, но нам нужен только один экземпляр ее идентификатора. (Признаком неиспользования IGNORE является завершение запроса с ошибкой типа «duplicate key».)
Наконец, заменяем исходную таблицу новой:
DROP TABLE swdist_head;
ALTER TABLE tmp RENAME TO swdist_head;
Процедура замены дочерней таблицы таблицей, содержащей только связанные дочерние записи, аналогична, но без необходимости использования IGNORE – каждому потомку соответствует только один предок:
CREATE TABLE tmp
(
dist_id INT UNSIGNED NOT NULL, # идентификатор родительского дистрибутива
dist_file VARCHAR(255) NOT NULL # имя файла дистрибутива
);
INSERT INTO tmp
SELECT swdist_item.*
FROM swdist_head, swdist_item
WHERE swdist_head.dist_id = swdist_item.dist_id;
DROP TABLE swdist_item;
ALTER TABLE tmp RENAME TO swdist_item;