Удаление связанных строк в нескольких таблицах
Задача
Вы хотите удалить связанные строки из нескольких таблиц. Например, если у вас есть таблицы, связанные как «главная-подчиненная» или «предок-потомок», то удаление родительской записи обычно требует удаления всех соответствующих дочерних записей.
Решение
Есть несколько вариантов решения задачи. MySQL 4.0 поддерживает каскадное удаление, разрешая многотабличный синтаксис DELETE. Можно заменять таблицу новыми версиями, которые содержат только те записи, которые не должны быть удалены. Можно написать программу, формирующую соответствующие предложения DELETE для каждой таблицы, или использовать для этого mysql.
Обсуждение
Приложения, использующие связанные таблицы, часто должны обрабатывать их одновременно. Предположим, что вы используете MySQL для записи информации о содержимом имеющихся у вас дистрибутивов программ.
Главная (родительская) таблица хранит название дистрибутива, номер версии и дату выпуска. Подчиненная (или дочерняя) таблица приводит информацию о файлах дистрибутива, представляя собой ведомость содержимого дистрибутива. Для обеспечения соответствия родительской и дочерней записей в каждой родительской записи имеется уникальный идентификатор, который хранится и в дочерней записи. Таблицы можно определить так:
CREATE TABLE swdist_head
(
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)
);
CREATE TABLE swdist_item
(
dist_id INT UNSIGNED NOT NULL, # идентификатор родительского дистрибутива
dist_file VARCHAR(255) NOT NULL # имя файла дистрибутива
);
В примерах данного раздела будем полагать, что таблицы содержат такие записи:
mysql> SELECT * FROM swdist_head ORDER BY name, ver_num;
+--------+--------------+-----------+---------------+
| dist_id | name | ver_num | rel_date |
+--------+--------------+-----------+---------------+
| 1 | DB Gadgets | 1.59 | 1996-03-25 |
| 3 | DB Gadgets | 1.60 | 1998-12-26 |
| 4 | DB Gadgets | 1.61 | 1998-12-28 |
| 2 | NetGizmo | 3.02 | 1998-11-10 |
| 5 | NetGizmo | 4.00 | 2001-08-04 |
+-------+---------------+------------+--------------+
mysql> SELECT * FROM swdist_item ORDER BY dist_id, dist_file;
+--------+-------------------+
| dist_id | dist_file |
+--------+-------------------+
| 1 | db-gadgets.sh |
| 1 | README |
| 2 | NetGizmo.exe |
| 2 | README.txt |
| 3 | db-gadgets.sh |
| 3 | README |
| 3 | README.linux |
| 4 | db-gadgets.sh |
| 4 | README |
| 4 | README.linux |
| 4 | README.solaris |
| 5 | NetGizmo.exe |
| 5 | README.txt |
+--------+-------------------+
Таблицы описывают дистрибутивы трех версий DB Gadgets и двух версий NetGizmo. Но таблицы трудно воспринимать по отдельности, так что давайте используем соединение строк двух таблиц для вывода информации об указанном дистрибутиве. Например, следующий запрос выводит данные, хранимые для DB Gadgets версии 1.60:
mysql> SELECT swdist_head.dist_id, swdist_head.name,
-> swdist_head.ver_num, swdist_head.rel_date, swdist_item.dist_file
-> FROM swdist_head, swdist_item
-> WHERE swdist_head.name = 'DB Gadgets' AND swdist_head.ver_num = 1.60
-> AND swdist_head.dist_id = swdist_item.dist_id;
+--------+-------------+------------+---------------+-----------------+
| dist_id | name | ver_num | rel_date | dist_file |
+--------+--------------+----------+---------------+------------------+
| 3 | DB Gadgets | 1.60 | 1998-12-26 | README |
| 3 | DB Gadgets | 1.60 | 1998-12-26 | README.linux |
| 3 | DB Gadgets | 1.60 | 1998-12-26 | db-gadgets.sh |
+-------+---------------+-----------+--------------+------------------+
Аналогично при удалении дистрибутива необходимо обработать обе таблицы. DB Gadgets 1.60 имеет идентификатор 3, поэтому одним из способов избавления от него был бы запуск вручную таких предложений DELETE для каждой из таблиц:
mysql> DELETE FROM swdist_head WHERE dist_id = 3;
mysql> DELETE FROM swdist_item WHERE dist_id = 3;
Это просто и быстро, но если вы забудете о том, что необходимо выполнить удаление в двух таблицах (что гораздо вероятнее, чем вы можете подумать), то могут возникнуть проблемы. Нарушится ссылочная целостность: возникнут родительские записи без дочерних или дочерние записи, ссылающиеся на несуществующих родителей. Кроме того, ручное удаление неудобно в тех случаях, когда нужно удалить большое количество дистрибутивов или когда вы заранее не знаете, какие дистрибутивы необходимо удалить. Предположим, вы хотите произвести чистку и удалить все старые записи, оставив только последнюю версию каждого дистрибутива. (Например, из таблицы, содержащей данные о дистрибутивах DB Gadgets версий 1.59, 1.60 и 1.61, будут удалены записи для версий 1.59 и 1.60.) Вероятно, вы будете определять, какие именно дистрибутивы должны быть удалены, при помощи запроса, выбирающего идентификаторы старых дистрибутивов. Но что делать дальше? Запрос может вывести множество идентификаторов, и вы вряд ли захотите удалять каждый дистрибутив вручную. Этого и не нужно. Есть ряд возможностей удаления записей из нескольких таблиц:• Использовать многотабличный синтаксис DELETE, доступный в версии MySQL 4.0.0. Вы сможете написать запрос, который занимается идентификацией и удалением записей двух таблиц одновременно. Не нужно помнить о необходимости запуска нескольких предложений DELETE при каждом удалении записей из связанных таблиц.
• Подойти к задаче с другой стороны: выбрать записи, которые не должны быть удалены, в новые таблицы, затем заменить исходные таблицы новыми. Результат будет таким же, как при удалении ненужных записей.
• Использовать программу для определения идентификаторов дистрибутивов, которые подлежат удалению, и формирования соответствующих предложений DELETE. Можно использовать самостоятельно написанную программу или уже существующую, например, mysql.
В оставшейся части раздела подробно описаны все упомянутые способы и их применение для решения задачи удаления старых дистрибутивов. Каждый пример будет удалять записи из таблиц swdist_head и swdist_item, поэтому необходимо создавать их и заполнять записями перед опробованием каждого из методов, чтобы всегда начинать работу с одной и той же точки. Для этого можно воспользоваться сценарием swdist_create.sql из каталога joins дистрибутива recipes. Сценарии, реализующие все способы удаления записей из нескольких таблиц, находятся в этом же каталоге.
Каскадное удаление при помощи многотабличного предложения DELETE
Начиная с MySQL версии 4.0.0 предложение DELETE поддерживает синтаксис, позволяющий идентифицировать записи для удаления в нескольких таблицах и удалить их все в одном предложении. Чтобы использовать эту возможность для удаления дистрибутивов программ из таблиц swdist_head и swdist_item, определим идентификаторы удаляемых дистрибутивов, затем применим список к таблицам.
Начнем с определения того, какая версия каждого дистрибутива является последней, и выберем названия и номера версий в отдельную таблицу. Следующий запрос выбирает название каждого дистрибутива и его последнюю версию:
mysql> CREATE TABLE tmp
-> SELECT name, MAX(ver_num) AS newest
-> FROM swdist_head
-> GROUP BY name;
Получившаяся таблица выглядит так:
mysql> SELECT * FROM tmp;
+--------------+---------+
| name | newest |
+--------------+---------+
| DB Gadgets | 1.61 |
| NetGizmo | 4.00 |
+--------------+---------+
Теперь определим идентификаторы тех дистрибутивов, которые старше перечисленных в таблице tmp:
mysql> CREATE TABLE tmp2
-> SELECT swdist_head.dist_id, swdist_head.name, swdist_head.ver_num
-> FROM swdist_head, tmp
-> WHERE swdist_head.name = tmp.name AND swdist_head.ver_num < tmp.newest;
Обратите внимание на то, что на самом деле необходимо выбрать только столбец dist_id в tmp2. Пример выбирает и название, и номер версии, чтобы вы, посмотрев на tmp2, могли легко убедиться в том, что выбранные идентификаторы действительно соответствуют старым дистрибутивам, которые следует удалить:
mysql> SELECT * FROM tmp2;
+--------+--------------+-----------+
| dist_id | name | ver_num |
+--------+--------------+-----------+
| 1 | DB Gadgets | 1.59 |
| 3 | DB Gadgets | 1.60 |
| 2 | NetGizmo | 3.02 |
+--------+--------------+----------+
Таблица не содержит идентификаторов для DB Gadgets 1.61 и NetGizmo 4.00, представляющих последние дистрибутивы.
Теперь применим список идентификаторов таблицы tmp2 к таблице дистрибутивов, используя многотабличное предложение DELETE. Общая форма этого предложения такова:
DELETE список_таблиц1 FROM список_таблиц2 WHERE условия;
В списке список_таблиц1 перечислены имена таблиц, из которых нужно удалить записи. В списке список_таблиц2 указаны таблицы, используемые в инструкции WHERE, задающей условия идентификации удаляемых записей.
Каждый из списков таблиц может включать одно или несколько имен, разделенных запятыми. В нашем случае таблицы, из которых удаляются записи, – это swdist_head и swdist_item. Таблицы, используемые для идентификации удаляемых записей, – это те же swdist_head и swdist_item, а также tmp2:
mysql> DELETE swdist_head, swdist_item
-> FROM tmp2, swdist_head, swdist_item
-> WHERE tmp2.dist_id = swdist_head.dist_id
-> AND tmp2.dist_id = swdist_item.dist_id;
Результирующие таблицы будут такими:
mysql> SELECT * FROM swdist_head;
+--------+--------------+-----------+--------------+
| dist_id | name | ver_num | rel_date |
+--------+--------------+-----------+--------------+
| 4 | DB Gadgets | 1.61 | 1998-12-28 |
| 5 | NetGizmo | 4.00 | 2001-08-04 |
+--------+--------------+-----------+--------------+
mysql> SELECT * FROM swdist_item;
+--------+-------------------+
| dist_id | dist_file |
+--------+-------------------+
| 4 | README |
| 4 | README.linux |
| 4 | README.solaris |
| 4 | db-gadgets.sh |
| 5 | README.txt |
| 5 | NetGizmo.exe |
+-------+--------------------+
В рассматриваемых таблицах предложение DELETE работает так, как и предполагалось. Но знайте, что если таблицы будут содержать родительские записи, которые следует удалить, но для которых нет соответствующих дочерних записей, то ничего не получится. Инструкция WHERE не найдет соответствий для родительской записи в дочерней и, следовательно, не выберет родительскую запись для удаления. Чтобы обеспечить выбор и удаление родительской записи даже при отсутствии у нее дочерних записей, используйте LEFT JOIN:
mysql> DELETE swdist_head, swdist_item
-> FROM tmp2 LEFT JOIN swdist_head ON tmp2.dist_id = swdist_head.dist_id
-> LEFT JOIN swdist_item ON swdist_head.dist_id = swdist_item.dist_id;
Выполнение многотабличного удаления путем замены таблицы
Можно удалить связанные строки нескольких таблиц, выбрав только записи, которые не подлежат удалению, в новые таблицы и затем заменив исходные таблицы новыми. Это особенно удобно, если вы хотите удалить больше записей, чем останется для хранения.
Начнем с создания двух таблиц tmp_head и tmp_item, имеющих ту же структуру, что и таблицы swdist_head и swdist_item:
CREATE TABLE tmp_head
(
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)
);
CREATE TABLE tmp_item
(
dist_id INT UNSIGNED NOT NULL, # идентификатор родительского дистрибутива
dist_file VARCHAR(255) NOT NULL # имя файла дистрибутива
);
Затем определим идентификаторы дистрибутивов, которые вы хотите хранить (то есть последнюю версию каждого дистрибутива). Идентификаторы определяются при помощи запросов, похожих на только что описанные в разделе про многотабличные удаления:
mysql> CREATE TABLE tmp
-> SELECT name, MAX(ver_num) AS newest
-> FROM swdist_head
-> GROUP BY name;
mysql> CREATE TABLE tmp2
-> SELECT swdist_head.dist_id
-> FROM swdist_head, tmp
-> WHERE swdist_head.name = tmp.name AND swdist_head.ver_num = tmp.newest;
Теперь выберем в новые таблицы те записи, которые должны остаться:
mysql> INSERT INTO tmp_head
-> SELECT swdist_head.*
-> FROM swdist_head, tmp2
-> WHERE swdist_head.dist_id = tmp2.dist_id;
mysql> INSERT INTO tmp_item
-> SELECT swdist_item.*
-> FROM swdist_item, tmp2
-> WHERE swdist_item.dist_id = tmp2.dist_id;
Наконец, заменим исходные таблицы новыми:
mysql> DROP TABLE swdist_head;
mysql> ALTER TABLE tmp_head RENAME TO swdist_head;
mysql> DROP TABLE swdist_item;
mysql> ALTER TABLE tmp_item RENAME TO swdist_item;
Выполнение многотабличного удаления из программы
Два предыдущих способа удаления связанных строк из нескольких таблиц используют только средства SQL. Другой подход заключается в написании программы, формирующей для вас предложения DELETE. Программа должна определить ключевые значения (идентификаторы дистрибутивов) для удаляемых записей и обработать ключи, преобразовав их в соответствующие предложения DELETE. Определять идентификаторы можно тем же способом, что и в предыдущих разделах, но у вас появляется некоторая свобода при выборе того, как использовать их для удаления записей:
• Обрабатывать каждый идентификатор отдельно. Формировать предложения DELETE, удаляющие записи из таблиц по одному идентификатору за раз.
• Обрабатывать идентификаторы как группу. Сформировать инструкцию IN(), содержащую все идентификаторы, и использовать ее для каждой таблицы для одновременного удаления всех совпадающих значений.
• Если список идентификаторов очень велик, разбить его на более мелкие группы для создания менее громоздких инструкций IN().• Можно решить задачу и подойдя к ней с другой стороны. Выберем идентификаторы тех дистрибутивов, которые следует сохранить, и используем их для формирования инструкции NOT IN(), удаляющей все остальные дистрибутивы. Обычно это менее эффективно, поскольку MySQL не применяет индекс для операций NOT IN().
Я покажу, как реализовать каждый из методов на Perl.
Для каждого из трех первых вариантов начнем с формирования списка идентификаторов дистрибутивов для удаляемых записей:
# Определение последней версии каждого дистрибутива
$dbh->do ("CREATE TABLE tmp
SELECT name, MAX(ver_num) AS newest
FROM swdist_head
GROUP BY name");
# Определение идентификаторов более старых версий, чем указанная.
my $ref = $dbh->selectcol_arrayref (
"SELECT swdist_head.dist_id
FROM swdist_head, tmp
WHERE swdist_head.name = tmp.name
AND swdist_head.ver_num < tmp.newest");
# selectcol_arrayref() возвращает ссылку на список. Преобразуем ссылку в список,
# который будет пуст, если $ref – это undef или указывает на пустой список.
my @val = ($ref ? @{$ref} : ());
На данный момент @val содержит список значений идентификаторов удаляемых записей. Для их индивидуальной обработки выполните такой цикл:
# Используем список идентификаторов для удаления записей,
# по одному идентификатору единовременно.
foreach my $val (@val)
{
$dbh->do ("DELETE FROM swdist_head WHERE dist_id = ?", undef, $val);
$dbh->do ("DELETE FROM swdist_item WHERE dist_id = ?", undef, $val);
}
Цикл генерирует предложения вроде таких:
DELETE FROM swdist_head WHERE dist_id = '1'
DELETE FROM swdist_item WHERE dist_id = '1'
DELETE FROM swdist_head WHERE dist_id = '3'
DELETE FROM swdist_item WHERE dist_id = '3'
DELETE FROM swdist_head WHERE dist_id = '2'
DELETE FROM swdist_item WHERE dist_id = '2'
Недостаток этого способа в том, что для больших таблиц список идентификаторов может быть весьма объемистым, и придется формировать множество предложений DELETE. Для повышения эффективности объедините идентификаторы в одну инструкцию IN(), где укажите их все сразу. Сгенерируйтесписок идентификаторов тем же способом, что и раньше, затем обрабатывайте список так:
# Используем список идентификаторов для удаления записей по всем идентификаторам
# сразу. Если список пуст – не волнуемся, удалять нечего.
if (@val)
{
# формируем список заполнителей "?", разделенных запятыми, по одному на значение
my $where = "WHERE dist_id IN (" . join (",", ("?") x @val) . ")";
$dbh->do ("DELETE FROM swdist_head $where", undef, @val);
$dbh->do ("DELETE FROM swdist_item $where", undef, @val);
}
Теперь формируется всего одно предложение DELETE для каждой таблицы:
DELETE FROM swdist_head WHERE dist_id IN ('1','3','2')
DELETE FROM swdist_item WHERE dist_id IN ('1','3','2')
Если список идентификаторов невероятно велик, может возникнуть опасность формирования предложения DELETE, длина которого будет превышать максимальную разрешенную для запроса (по умолчанию – один мегабайт).
В данном случае можно разбить список идентификаторов на мелкие группы и использовать каждую из них для построения короткой инструкции IN():
# Используем список идентификаторов для удаления записей,
# используя единовременно часть списка.
my $grp_size = 1000; # количество идентификаторов для одновременного удаления
for (my $i = 0; $i < @val; $i += $grp_size)
{
my $j = (@val < $i + $grp_size ? @val : $i + $grp_size);
my @group = @val[$i .. $j-1];
# формируем список заполнителей "?", разделенных запятыми, по одному на значение
my $where = "WHERE dist_id IN (" . join (",", ("?") x @group) . ")";
$dbh->do ("DELETE FROM swdist_head $where", undef, @group);
$dbh->do ("DELETE FROM swdist_item $where", undef, @group);
}
Все предыдущие программные методы находят идентификаторы удаляемых записей, затем удаляют их. Можно получить тот же результат, используя обратную логику: выбирать идентификаторы записей, которые планируется хранить, и удалять все остальное. Такой подход удобен в тех случаях, когда вы предполагаете, что останется меньше записей, чем будет удалено. Для реализации такой логики определяем новейшую версию каждого дистрибутива и находим соответствующие идентификаторы. Затем используем список идентификаторов для формирования инструкции NOT IN():
# Определяем новейшую версию для каждого дистрибутива
$dbh->do ("CREATE TABLE tmp
SELECT name, MAX(ver_num) AS newest
FROM swdist_head
GROUP BY name");
# Определяем идентификаторы этих версий.
my $ref = $dbh->selectcol_arrayref (
"SELECT swdist_head.dist_id
FROM swdist_head, tmp
WHERE swdist_head.name = tmp.name
AND swdist_head.ver_num = tmp.newest");
# selectcol_arrayref() возвращает ссылку на список. Преобразуем ссылку в список,
# который будет пуст, если $ref – это undef или указывает на пустой список.
my @val = ($ref ? @{$ref} : ());
# используем список идентификаторов для удаления записей всех "остальных"
# идентификаторов сразу. Инструкция WHERE пуста, если пуст список
# (в этом случае ни одну запись не нужно хранить, все могут быть удалены).
my $where = "";
if (@val)
{
# формируем список заполнителей "?", разделенных запятыми, по одному на значение
$where = "WHERE dist_id NOT IN (" . join (",", ("?") x @val) . ")";
}
$dbh->do ("DELETE FROM swdist_head $where", undef, @val);
$dbh->do ("DELETE FROM swdist_item $where", undef, @val);
Обратите внимание, что при использовании такой обратной логики необходимо использовать весь список идентификаторов в одной инструкции NOT IN(). При попытке разбить список на более мелкие группы и использовать NOT IN() для каждой из них содержимое таблиц будет полностью уничтожено, даже если вы этого не хотите.
Выполнение многотабличного удаления с помощью mysql
Если ключи, указывающие, какие записи нужно удалить, не содержат кавычек и других специальных символов, то вы можете формировать предложения DELETE при помощи mysql. Ключи таблиц дистрибутивов (значения dist_id) целые, так что они допускают применение этого решения. Сформируйте список идентификаторов с помощью тех же запросов, которые описаны в разделе, посвященном многотабличному предложению DELETE, затем используйте этот список для создания предложений DELETE:
CREATE TABLE tmp
SELECT name, MAX(ver_num) AS newest
FROM swdist_headGROUP BY name;
CREATE TABLE tmp2
SELECT swdist_head.dist_id
FROM swdist_head, tmp
WHERE swdist_head.name = tmp.name AND swdist_head.ver_num < tmp.newest;
SELECT CONCAT('DELETE FROM swdist_head WHERE dist_id=',dist_id,';') FROM tmp2;
SELECT CONCAT('DELETE FROM swdist_item WHERE dist_id=',dist_id,';') FROM tmp2;
Если эти предложения содержатся в файле swdist_mysql_delete.sql, выполните его, чтобы вывести ряд предложений DELETE:
% mysql -N cookbook < swdist_mysql_delete.sql > tmp
Файл tmp будет таким:
DELETE FROM swdist_head WHERE dist_id=1;
DELETE FROM swdist_head WHERE dist_id=3;
DELETE FROM swdist_head WHERE dist_id=2;
DELETE FROM swdist_item WHERE dist_id=1;
DELETE FROM swdist_item WHERE dist_id=3;
DELETE FROM swdist_item WHERE dist_id=2;
Выполним его содержимое так:
% mysql cookbook < tmp