Удаление дубликатов строк mysql

Удаление дубликатов строк mysql

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

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

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

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

id (код записи) country_id (код страны) city_name (код города)
1 1 Москва
2 1 Хабаровск
3 1 Самара
4 1 Кисловодск
5 1 Хабаровск
6 1 Самара
7 1 Кисловодск
8 1 Кисловодск

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

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_ >
AND a.city_name = c.city_name
AND a.id > c.mid

Читайте также:  Zte axon 7 евросеть

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

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

Теперь разберемся подробнее, как все это работает. При запросе на удаление, необходимо задать условие, которое укажет какие данные нужно удалить, а какие оставить. Нам необходимо удалить все не уникальные записи. Т.е. если существует несколько одинаковых записей (одинаковые они, если у них равны значения 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` )

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

Читайте также:  Intel pentium d 915 sl9da

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

Еще материалы из этого раздела

Комментарии

Александр, почти убедили. Еще раз все пересмотрел, но статью все же оставлю без изменений.
Согласен с тем, что в деле разработки нужно стремиться к оптимизации. Особенно, это актуально там, где нужно экономить время или ресурсы компьютера.
Но в случае этой статьи, важно, чтобы читатель получил однозначно работающий код. Если в таблице, которую он хочет очистить будет присутствовать ключевое поле, то в самом действии не будет необходимости. Если же ключевого поля нет, то возникнет задача не в создании ключевого поля как такового, а в создании отдельного столбца, в котором должны быть уникальные идентификаторы для каждой строки таблицы. При такой постановке вопроса, я думаю, нужно еще дополнительно рассмотреть саму необходимость такого заполнения. После того, как это поле добавлено и заполнено, проследить, корректно ли оно заполнено — соблюдена ли уникальность записей.
Думаю, что лучше поберечь силы читателя, и предоставить ему заведомо (пусть даже с перестраховкой) рабочий вариант.
Конечно для реальной задачи, профессионал, будет более детально разбираться в проблеме, создавая наиболее эффективный вариант решения. И вот тут будет крайне ценен ваш комментарий.

PS
Александр, хочется отметить, что такого рода комментарии очень полезны для создания качественных материалов, действительно полезных читателю. Вы натолкнули на мысль, как-то выделять особенно полезные комментарии, чтобы привлечь внимание пользователей к конкретным нюансам. Займусь этим.
Еще раз, спасибо.

Андрей, добрый день.
С любопытством изучил ваш код. Хотелось проверить на практике. Там есть небольшая ошибка (отсутствует присвоение второй таблице символу "b") — исправил ее и запустил в следующем виде:

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

Однако происходит ошибка с обращением к возвращенному набору данных из вложенного запроса. Если у вас был рабочий код, уточните пожалуйста. Было бы интересно узнать более оптимальный вариант запроса.

Читайте также:  Симс 3 все дополнения вылетает

Пытаюсь применить Ваш алгоритм (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.[код]

Не все СУБД поддерживают обращение в WHERE к созданной динамически колонке. Так что код от Андрей Калинин и проще и более универсальный.

Есть таблица с дубликатами в MySQL: (по полю name)

Необходимо получить таблицу такого вида

10 ответов 10

Запрос для результирующей таблицы:

Для удаления дубликатов подойдет такой прием:

Есть конечно нюансы.

Какие-то экзотические варианты предлагаются.

Удалить из таблицы дубликаты (строки с одинаковыми значениями поля col) с меньшим id

DELETE t1 FROM t t1 LEFT JOIN t t2 ON t1.col = t2.col AND t1.id

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

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

здесь предлагают делать так:

но индекс должен влезть в память.

ну и через временную таблицу естественно есть способ. а так-же через group by.

По идее как то так можно:

А потом сразу создать уникальный индекс по полю чтобы потом этого сделать было не возможно.

Для того чтобы можно было удалить дубликаты записей в таблице, воспользуемся однострочным SQL-запросом:

где: `table` — имя таблицы, email — имя столбца в котором ищем дубликаты!

2й вариант

В случае, если необходимо производить поиск дубликатов по 2-м столбцам.

3й вариант

Когда сложная ситуация, без создания копий таблиц, наверно не обойтись.

Ссылка на основную публикацию
Adblock detector