Есть такая табличка статусов слонов, то есть у слона может быть несколько статусов его местонахождения.
status:
id int auto_increment pk
slon_id int
status varchar
date datetime
При программном баге (точнее недоработке) появились дубликаты записей вида
1 1 Розовый слон в Африке 18.11.2016...
2 1 Розовый слон в Африке 18.11.2016...
3 1 розовый слон уже в Зимбабве 19.11.2016...
4 2 Зеленый слон в Европе 15.11.2016...
Интересует SQL решение. Можно ли как-то удалить дубликаты записей? Можно ли удалить дубликаты записей оставив по одному чтобы не потерять историю? (То есть если про Африку и Розового слона несколько записей - удалить все кроме одной).
Ответ
Алгоритм действий:
понять, по каким критериям надо считать записи идентичными
написать запрос на поиск дублирующихся наборов данных (можно не строк, только наборов данных)
понять, как решить, какую из записей надо оставить
соответственно откорректировать запрос, чтобы он выводил все дублирующиеся записи, кроме той, которую надо оставить
написать запрос delete where id in (subquery)
В итоге получается что-то вроде такого, если считать uniqfield полем, которое должно стать уникальным, оставлять строку с минимальным id
delete from tablename where id in (
select id from (
select id from tablename join (
select min(id) as firstdup, uniqfield
from tablename
group by uniqfield
) duplicates using(uniqfield)
where id != firstdup
) subqueryhack
)
Для комбинации из двух полей, чтобы было видно, что меняется
delete from tablename where id in (
select id from (
select id from tablename join (
select min(id) as firstdup, uniqfield1, uniqfield2
from tablename
group by uniqfield1, uniqfield2
) duplicates using(uniqfield1, uniqfield2)
where id != firstdup
) subqueryhack
)
delete в mysql не даст напрямую удалять из той таблицы, которую читает подзапрос, но это обходится при необходимости ещё одним подзапросом.
Если неважно, какие именно строки оставлять, а какие удалять и позволяет версия СУБД - то до mysql 5.7.4 можно было просто повесить уникальный индекс с указанием ignore
ALTER IGNORE TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);
Что удалит все дубли по этим полям, кроме какой-то одной строки. В актуальных версиях ignore поведение удалено и вызывает ошибку. Не вникал, почему удалили, скорей всего из-за неочевидного поведения, какая именно строка будет удалена и общего курса возвращения к SQL-стандарту.
Комментариев нет:
Отправить комментарий