Удаление дубликатов из таблицы

Задача
Вы хотите удалить дубликаты из таблицы, чтобы она содержала только уникальные записи.

Решение
Выберите уникальные строки из таблицы в другую таблицу и замените ею исходную. Или добавьте в таблицу уникальный индекс, используя ALTER TABLE, в результате чего дубликаты исчезнут. Или примените DELETE ... LIMIT n для удаления всех, кроме одного, экземпляров повторяющихся строк.

Обсуждение
Если при создании таблицы вы забыли определить уникальный индекс, предотвращающий наличие дубликатов в таблице, то в дальнейшем вы можетестолкнуться с необходимостью как-то избавиться от дубликатов. Таблица cat_mailing из примеров предыдущих разделов как раз относится к подобным таблицам, так как содержит по несколько записей об одних и тех же людях:

mysql> SELECT * FROM cat_mailing ORDER BY last_name, first_name;

+-----------+--------------+-----------------------------+
| last_name | first_name | street |
+-----------+--------------+-----------------------------+
| Baxter | Wallace | 57 3rd Ave. |
| BAXTER | WALLACE | 57 3rd Ave.


|
| Baxter | Wallace | 57 3rd Ave., Apt 102 |
| Brown | Bartholomew | 432 River Run |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
| Pinter | Marlene | 9 Sunset Trail |
+----------+---------------+-----------------------------+

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

• Выберите уникальные строки таблицы в другую таблицу, затем используйте новую таблицу для замены исходной. В результате дубликаты будут удалены. Такой способ подходит, когда под дубликатами понимаются полностью совпадающие строки.

• Добавьте в таблицу уникальный индекс при помощи ALTER TABLE. Эта операция устранит повторяющиеся строки, основываясь на содержимом индексированных столбцов.

• Дубликаты из указанного набора повторяющихся строк можно удалить с помощью предложения DELETE ...


LIMIT n, удалив все строки кроме одной.

В этом разделе подробно рассмотрены все перечисленные способы. Размышляя о том, какой из них выбрать в определенных условиях, помните, что применимость метода к конкретной задаче часто определяется ответами на два вопроса:

• Требуется ли, чтобы таблица имела уникальный индекс?

• Если столбец, в котором встречаются дубликаты, допускает использование NULL, удалит ли метод повторяющиеся значения NULL?

Удаление дубликатов путем замены таблицы
Одним из способов удаления дубликатов из таблицы является выбор ее уникальных записей в новую таблицу, имеющую такую же структуру. Затем исходная таблица заменяется новой. Если строка считается дубликатом другой строки только в случае их полного совпадения, то можно использовать для выбора уникальных строк SELECT DISTINCT:

mysql> CREATE TABLE tmp SELECT DISTINCT * FROM cat_mailing;
mysql> SELECT * FROM tmp ORDER BY last_name, first_name;

+-----------+--------------+-----------------------------+
| last_name | first_name | street |
+-----------+--------------+-----------------------------+
| Baxter | Wallace | 57 3rd Ave.


|
| Baxter | Wallace | 57 3rd Ave., Apt 102 |
| Brown | Bartholomew | 432 River Run |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
+-----------+--------------+-----------------------------+

Этот способ работает в отсутствие индекса (хотя для больших таблиц он может быть медленным), а из таблиц, содержащих повторяющиеся значения NULL, он такие дубликаты удаляет. Обратите внимание на то, что в данном случае немного отличающиеся значениями street строки для Wallace Baxter считаются различными.

Если дубликаты определяются только относительно подмножества столбцов таблицы, создайте новую таблицу, имеющую уникальный индекс, а затем выберите в нее строки, используя INSERT IGNORE.

mysql> CREATE TABLE tmp (
-> last_name CHAR(40) NOT NULL,
-> first_name CHAR(40) NOT NULL,
-> street CHAR(40) NOT NULL,
-> PRIMARY KEY (last_name, first_name));
mysql> INSERT IGNORE INTO tmp SELECT * FROM cat_mailing;
mysql> SELECT * FROM tmp ORDER BY last_name, first_name;

+-----------+--------------+-----------------------------+
| last_name | first_name | street |
+-----------+--------------+-----------------------------+
| Baxter | Wallace | 57 3rd Ave. |
| Brown | Bartholomew | 432 River Run |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
+----------+---------------+-----------------------------+

Индекс не допускает вставки записей с дублирующимися значениями ключа в tmp, а IGNORE указывает MySQL на то, что не следует завершаться с ошибкой в случае обнаружения дубликата. Недостатком метода является то, что если индексированные столбцы могут содержать значения NULL, то необходимо использовать индекс UNIQUE вместо PRIMARY KEY, и тогда не будут удаляться повторяющиеся ключи со значениями NULL (индекс UNIQUE допускает несколько значений NULL).

После создания новой таблицы tmp, содержащей только уникальные строки, используйте ее для замены исходной таблицы cat_mailing. В результате cat_mailing больше не будет содержать дубликатов:

mysql> DROP TABLE cat_mailing;
mysql> ALTER TABLE tmp RENAME TO cat_mailing;

Удаление дубликатов путем добавления индекса
Чтобы удалить дубликаты из таблицы непосредственно «на месте», добавьте в таблицу уникальный индекс при помощи ALTER TABLE, используя ключевое слово IGNORE для того, чтобы указать на необходимость удаления записей с дублирующимися значениями ключа в процессе построения индекса. Исходная таблица cat_mailing без индекса выглядит так:

mysql> SELECT * FROM cat_mailing ORDER BY last_name, first_name;

+-----------+------------+-------------------------------+
| last_name | first_name | street |
+-----------+------------+-------------------------------+
| Baxter | Wallace | 57 3rd Ave. |
| BAXTER | WALLACE | 57 3rd Ave. |
| Baxter | Wallace | 57 3rd Ave., Apt 102 |
| Brown | Bartholomew | 432 River Run |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
| Pinter | Marlene | 9 Sunset Trail |
+----------+---------------+-----------------------------+

Добавьте уникальный индекс и посмотрите, как это подействует на содержимое таблицы:

mysql> ALTER IGNORE TABLE cat_mailing
-> ADD PRIMARY KEY (last_name, first_name);
mysql> SELECT * FROM cat_mailing ORDER BY last_name, first_name;

+-----------+--------------+-----------------------------+
| last_name | first_name | street |
+-----------+--------------+-----------------------------+
| Baxter | Wallace | 57 3rd Ave. |
| Brown | Bartholomew | 432 River Run |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
+----------+---------------+-----------------------------+

Если индексированные столбцы могут содержать значения NULL, то вместо индекса PRIMARY KEY нужно использовать UNIQUE. Тогда индекс не будет удалять повторяющиеся значения NULL.

Удаление дубликатов определенной строки
Начиная с версии MySQL 3.22.7, вы можете использовать инструкцию LIMIT для ограничения действия предложения DELETE на подмножество строк, которое оно в противном случае удалило бы. В такой форме предложение можно применять для удаления повторяющихся записей. Предположим, что у вас есть таблица t с таким содержимым:

+-------+
| color |
+-------+
| blue |
| green |
| blue |
| blue |
| red |
| green |
| red |
+-------+

В таблице трижды присутствует blue (голубой) и дважды – green (зеленый) и red (красный). Чтобы удалить дополнительные экземпляры каждого цвета, выполните:

mysql> DELETE FROM t WHERE color = 'blue' LIMIT 2;
mysql> DELETE FROM t WHERE color = 'green' LIMIT 1;
mysql> DELETE FROM t WHERE color = 'red' LIMIT 1;
mysql> SELECT * FROM t;

+-------+
| color |
+-------+
| blue |
| green |
| red |
+-------+

Прием работает в отсутствие уникального индекса и удаляет повторяющиеся значения NULL. Его удобно использовать, если вам нужно удалить дубликаты только для определенного множества строк таблицы. Однако если следует удалить много различных наборов дубликатов, подобную процедуру вряд ли захочется проводить вручную. Процесс можно автоматизировать, используя методы для выявления дубликатов. В том рецепте мы создали функцию make_dup_count_query() для генерирования запроса, подсчитывающего количество повторяющихся значений в указанном множестве столбцов таблицы:

sub make_dup_count_query
{
my ($tbl_name, @col_name) = @_;
return (
"SELECT COUNT(*)," . join (",", @col_name)
. "\nFROM $tbl_name"
. "\nGROUP BY " . join (",", @col_name)
. "\nHAVING COUNT(*) > 1"
);
}

Можно написать еще одну функцию delete_dups(), использующую make_dup_count_query() для определения того, какие значения таблицы повторяются и как часто. Из этой информации можно понять, сколько дубликатов следует удалить при помощи DELETE … LIMIT n, чтобы в таблице остался только один экземпляр записи. Функция delete_dups() выглядит так:

sub delete_dups
{
my ($dbh, $tbl_name, @col_name) = @_;
# Создать и выполнить запрос, который находит дубликаты
my $dup_info = $dbh->selectall_arrayref (
make_dup_count_query ($tbl_name, @col_name)
);
return unless defined ($dup_info);
# Для каждого повторяющегося множества значений удалить все вхождения строк,
# содержащих эти значения, кроме одного
foreach my $row_ref (@{$dup_info})
{
my ($count, @col_val) = @{$row_ref};
next unless $count > 1;
# Построить строку условия для сравнения значений, не забывая про IS NULL
my $str;
for (my $i = 0; $i < @col_name; $i++)
{
$str .= " AND " if $str;
$str .= defined ($col_val[$i])
? "$col_name[$i] = " . $dbh->quote ($col_val[$i])
: "$col_name[$i] IS NULL";
}
$str = "DELETE FROM $tbl_name WHERE $str LIMIT " . ($count - 1);
$dbh->do ($str);
}
}

Предположим, что у нас есть таблица employee со следующими записями:

mysql> SELECT * FROM employee;

+-----------+--------------+
| name | department |
+-----------+--------------+
| Fred | accounting |
| Fred | accounting |
| Fred | accounting |
| Fred | accounting |
| Bob | shipping |
| Mary Ann | shipping |
| Mary Ann | shipping |
| Mary Ann | sales |
| Mary Ann | sales |
| Mary Ann | sales |
| Mary Ann | sales |
| Mary Ann | sales |
| Mary Ann | sales |
| Boris | NULL |
| Boris | NULL |
+-----------+-------------+

Для того чтобы использовать функцию delete_dups() для удаления дубликатов в столбцах name и department таблицы employee, вызовите ее так:

delete_dups ($dbh, "employee", "name", "department");

Функция delete_dups() вызывает функцию make_dup_count_query() и выполняет формируемый ею запрос SELECT. Для таблицы employee этот запрос выводит такой результат:

+-------------+------------+--------------+
| COUNT(*) | name | department |
+-------------+------------+--------------+
| 2 | Boris | NULL |
| 4 | Fred | accounting |
| 6 | Mary Ann | sales |
| 2 | Mary Ann | shipping |
+-------------+------------+--------------+

Функция delete_dups() использует данную информацию для формирования следующих предложений DELETE:

DELETE FROM employee
WHERE name = 'Boris' AND department IS NULL LIMIT 1
DELETE FROM employee
WHERE name = 'Fred' AND department = 'accounting' LIMIT 3
DELETE FROM employee
WHERE name = 'Mary Ann' AND department = 'sales' LIMIT 5
DELETE FROM employee
WHERE name = 'Mary Ann' AND department = 'shipping' LIMIT 1

В целом, способ с использованием DELETE ... LIMIT n, вероятно, работает медленнее, чем удаление дубликатов при помощи создания второй таблицы или добавления уникального индекса. Эти методы хранят данные на сервере и позволяют ему выполнить всю работу. DELETE ... LIMIT n требует большого объема взаимодействия клиента с сервером, так как использует запрос SELECT для извлечения информации о дубликатах, а затем ряд предложений DELETE для удаления копий дублирующихся строк.

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Обработка дубликатов на этапе создания записи
Подсчет и выявление дубликатов
Предотвращение появления дубликатов в таблице
Устранение дубликатов из результата запроса
Устранение дубликатов из результата самообъединения