СтатьиРабота с SQL и базами данных

Удаление повторяющихся строк в таблице запросом SQL

Когда возникает задача оптимизации базы данных или меняется ее структура, иногда появляется попутная задача организации уже накопленных данных. Хорошо, если таблица уже при разработке приведена в нормальную форму, и вся система организована так, что она не копит лишней дублирующей информации. Если же это не так, то при доработке такой системы хочется избавиться от всех избыточных данных и сделать все наиболее качественно.

В этой статье рассмотрим задачу удаления дублирующих строк в таблице БД. Сразу же отмечу, что речь идет о необходимости удалить именно повторяющиеся строки. Например, записи в таблице заказов с полями "код заказа", "код товара", "код покупателя", "дата заказа" могут различаться только кодом заказа, так как все же один покупатель в один день может заказать один и тот же товар несколько раз. А главный показатель здесь, что все правильно – наличие ключевого поля.

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

Пример явно избыточной таблицы:

id (код записи) country_id (код страны) city_name (код города)
11Москва
21Хабаровск
31Самара
41Кисловодск
51Хабаровск
61Самара
71Кисловодск
81Кисловодск

Теперь рассмотрим, как можно решить эту проблему. Здесь можно применить несколько методов.


1. Можно написать функцию для сравнения и перебора всех данных. Это долго, да и писать код для одноразового использования не всегда хочется.


2. Другое решение – создать запрос на выборку с группировкой данных, так чтобы получить только уникальные строки:

SELECT country_id, city_name
FROM mytable
GROUP BY country_id, city_name

Получаем следующую выборку:

country_id city_name
1Кисловодск
1Москва
1Самара
1Хабаровск

Затем, полученный набор данных записываем в другую таблицу.


3. В указанных решениях применяется дополнительный программный код или дополнительные таблицы. Однако, было бы удобней сделать все, используя только запросы SQL без дополнительных таблиц. И вот пример такого решения:

DELETE a.* FROM mytable a,
(SELECT
b.country_id, b.city_name, MIN(b.id) mid
FROM mytable b
GROUP BY b.country_id, b.city_name
) c
WHERE
a.country_id = c.country_id
AND a.city_name = c.city_name
AND a.id > c.mid

После выполнения такого запроса в таблице останутся только уникальные записи:

id country_id city_name
11Москва
21Хабаровск
31Самара
41Кисловодск

Теперь разберемся подробнее, как все это работает. При запросе на удаление, необходимо задать условие, которое укажет какие данные нужно удалить, а какие оставить. Нам необходимо удалить все не уникальные записи. Т.е. если существует несколько одинаковых записей (одинаковые они, если у них равны значения country_id и city_name), то нужно взять одну из строк, запомнить ее код и удалить все записи с такими же значениями country_id и city_name, но другим кодом (id).

Строка SQL запроса:

DELETE a.* FROM mytable a,

указывает, что удаление будет производиться из таблицы mytable.

Затем запрос на выборку формирует вспомогательную таблицу, где мы группируем записи так, чтобы все записи были уникальными:

(SELECT
b.country_id, b.city_name, MIN(b.id) mid
FROM mytable b
GROUP BY b.country_id, b.city_name
) c

MIN(b.id) mid – формирует столбец mid (сокращение min id), в который вносятся минимальное значение id, в каждой подгруппе.

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

country_id city_name mid
1Кисловодск4
2Москва1
3Самара3
4Хабаровск2

Теперь мы имеем две таблицы. Одну общую, содержащую все записи. Из нее будут удаляться лишние строки. Вторая содержит информацию о строках, которые нужно сохранить.

Остается только сформировать условие, где указывается: удалить нужно все строки, где совпадают поля country_id и city_name, а id совпадать не будет. В данном случае выбирается минимальное значение id, поэтому удаляются все записи, id которых больше чем выбранный во временную таблицу.


Стоит еще отметить, что описанную операцию можно выполнить при наличии в таблице ключевого поля. Если вдруг встретилась таблица без уникального идентификатора, то просто добавляем его:

ALTER TABLE ` mytable` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY ( `id` )

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

Выполняем все необходимые действия. После того, как операция по очистке таблицы от дубликатов записей выполнена, это поле можно так же удалить.


Комментарии

* 01.04.2016 10:14 Aleksandr [гость]
Хочу обратить внимание на последний пункт, если в таблице не было уникального идентификатора, то его нужно добавить, но не обязательно его делать ключом, все равно потом удаляем, поэтому "ADD PRIMARY KEY ( `id` )" - лишнее действие
01.04.2016 11:47 Антон Сенников [администратор]
День добрый, Aleksandr.
Действительно не обязательно. Ключевое поле используется здесь, чтобы обеспечить однозначно уникальные идентификаторы. Понятно, что атрибут инкрементного поля добавит уникальные коды по возрастанию. Но так как описанная в статье операция выполняются единовременно, то минимизировать код не обязательно. Основная задача здесь - корректное выполнение очистки от повторов записей.
Спасибо за замечание.
01.04.2016 13:12 Aleksandr [гость]
Антон, спасибо за ответ. Конечно же, основная задача - очистить таблицу от повторов - будет выполнена в обоих случаях, я лишь руководствуюсь принципом бритвы Оккама: не множить сущее без необходимости.
Иногда встречаются статьи, которые из-за осознанного или нет пренебрежения авторами данным принципом вводят не очень подготовленного читателя в заблуждение, будто некое действие является таким же обязательным для достижения цели как и другие, являющиеся действительно обязательными.
А если добавлять столбец с уникальным идентификатором не с помощью запроса, а через конструктор таблиц - то установка главного ключа будет явно лишним действием
01.04.2016 16:42 Антон Сенников [администратор]
Александр, почти убедили... Еще раз все пересмотрел, но статью все же оставлю без изменений.
Согласен с тем, что в деле разработки нужно стремиться к оптимизации. Особенно, это актуально там, где нужно экономить время или ресурсы компьютера.
Но в случае этой статьи, важно, чтобы читатель получил однозначно работающий код. Если в таблице, которую он хочет очистить будет присутствовать ключевое поле, то в самом действии не будет необходимости. Если же ключевого поля нет, то возникнет задача не в создании ключевого поля как такового, а в создании отдельного столбца, в котором должны быть уникальные идентификаторы для каждой строки таблицы. При такой постановке вопроса, я думаю, нужно еще дополнительно рассмотреть саму необходимость такого заполнения. После того, как это поле добавлено и заполнено, проследить, корректно ли оно заполнено - соблюдена ли уникальность записей.
Думаю, что лучше поберечь силы читателя, и предоставить ему заведомо (пусть даже с перестраховкой) рабочий вариант.
Конечно для реальной задачи, профессионал, будет более детально разбираться в проблеме, создавая наиболее эффективный вариант решения. И вот тут будет крайне ценен ваш комментарий.

PS
Александр, хочется отметить, что такого рода комментарии очень полезны для создания качественных материалов, действительно полезных читателю. Вы натолкнули на мысль, как-то выделять особенно полезные комментарии, чтобы привлечь внимание пользователей к конкретным нюансам. Займусь этим.
Еще раз, спасибо.
02.12.2016 07:36 Андрей Калинин [гость]
Я сделал бы немного попроще:
DELETE FROM mytable WHERE id NOT IN (SELECT MIN(id) FROM mytable GROUP BY b.country_id, b.city_name);
02.12.2016 21:35 Антон Сенников [администратор]
Андрей, добрый день.
С любопытством изучил ваш код. Хотелось проверить на практике. Там есть небольшая ошибка (отсутствует присвоение второй таблице символу "b") - исправил ее и запустил в следующем виде:

DELETE FROM mytable WHERE id NOT IN (SELECT MIN(id) FROM mytable AS b GROUP BY b.country_id, b.city_name);

Однако происходит ошибка с обращением к возвращенному набору данных из вложенного запроса. Если у вас был рабочий код, уточните пожалуйста. Было бы интересно узнать более оптимальный вариант запроса.
24.01.2017 19:06 Дмитрий Копылов [гость]
Пытаюсь применить Ваш алгоритм (min заменил на max - мне надо оставлять только последние записи). Использую следующий код:

DELETE a.*
FROM T1 AS a,
(SELECT b.[AF], b.[BF], max (b.[Код]) AS maxID
FROM T1 AS b
GROUP BY b.[AF], b.[BF]
) AS c
WHERE a.[AF] = c.[AF] AND a.[BF] = c.[BF] AND a.[код]
24.01.2017 19:09 Дмитрий Копылов [гость]
Почему-то не отправилась вторая половина сообщения...
Так вот, при исполнении алгоритма выдается сообщение: "Невозможно удаление записей из указанных таблиц". При этом, если заменить Delete на Select, выборка производится правильно. В чем может быть причина ошибки?
24.01.2017 19:21 Дмитрий Копылов [гость]
Попробовал код от Андрея:
DELETE *
FROM T1
WHERE Код NOT IN (
SELECT max (Код)
FROM T1
GROUP BY AF, BF);
Все работает прекрасно. Большое спасибо! Так что предыдущий вопрос переходит в разряд чисто академических. Но все равно интересно.
Имя:
Текст комментария:
* В комментариях запрещено публиковать рекламные объявления. Сообщения, содержащие ссылки на сторонние ресурсы добавляется в скрытом режиме. Они будут открыты, если не нарушают установленных правил, после проверки.
Защита от спам-роботов (* Обязателельно укажите ответ на простой вопрос ниже.)
Под каким номером в алфавите буква «Б»? (цифра)