Usuwanie zduplikowanych wierszy w miejscu MySQL, (zakładając, że masz kolumnę sygnatury czasowej do sortowania):
Utwórz tabelę i wstaw kilka wierszy:
create table penguins(foo int, bar varchar(15), baz datetime);
insert into penguins values(1, 'skipper', now());
insert into penguins values(1, 'skipper', now());
insert into penguins values(3, 'kowalski', now());
insert into penguins values(3, 'kowalski', now());
insert into penguins values(3, 'kowalski', now());
insert into penguins values(4, 'rico', now());
select * from penguins;
+------+----------+---------------------+
| foo | bar | baz |
+------+----------+---------------------+
| 1 | skipper | 2014-08-25 14:21:54 |
| 1 | skipper | 2014-08-25 14:21:59 |
| 3 | kowalski | 2014-08-25 14:22:09 |
| 3 | kowalski | 2014-08-25 14:22:13 |
| 3 | kowalski | 2014-08-25 14:22:15 |
| 4 | rico | 2014-08-25 14:22:22 |
+------+----------+---------------------+
6 rows in set (0.00 sec)
Usuń duplikaty w miejscu:
delete a
from penguins a
left join(
select max(baz) maxtimestamp, foo, bar
from penguins
group by foo, bar) b
on a.baz = maxtimestamp and
a.foo = b.foo and
a.bar = b.bar
where b.maxtimestamp IS NULL;
Query OK, 3 rows affected (0.01 sec)
select * from penguins;
+------+----------+---------------------+
| foo | bar | baz |
+------+----------+---------------------+
| 1 | skipper | 2014-08-25 14:21:59 |
| 3 | kowalski | 2014-08-25 14:22:15 |
| 4 | rico | 2014-08-25 14:22:22 |
+------+----------+---------------------+
3 rows in set (0.00 sec)
Gotowe, zduplikowane wiersze są usuwane, zachowywany jest ostatni według sygnatury czasowej.
Dla tych z Was, którzy nie mają sygnatury czasowej ani unikalnej kolumny.
Nie masz timestamp unikatowej kolumny indeksu do sortowania? Żyjesz w stanie degeneracji. Będziesz musiał wykonać dodatkowe czynności, aby usunąć zduplikowane wiersze.
utwórz tabelę pingwinów i dodaj kilka wierszy
create table penguins(foo int, bar varchar(15));
insert into penguins values(1, 'skipper');
insert into penguins values(1, 'skipper');
insert into penguins values(3, 'kowalski');
insert into penguins values(3, 'kowalski');
insert into penguins values(3, 'kowalski');
insert into penguins values(4, 'rico');
select * from penguins;
# +------+----------+
# | foo | bar |
# +------+----------+
# | 1 | skipper |
# | 1 | skipper |
# | 3 | kowalski |
# | 3 | kowalski |
# | 3 | kowalski |
# | 4 | rico |
# +------+----------+
zrobić klon pierwszej tabeli i skopiować do niej.
drop table if exists penguins_copy;
create table penguins_copy as ( SELECT foo, bar FROM penguins );
#add an autoincrementing primary key:
ALTER TABLE penguins_copy ADD moo int AUTO_INCREMENT PRIMARY KEY first;
select * from penguins_copy;
# +-----+------+----------+
# | moo | foo | bar |
# +-----+------+----------+
# | 1 | 1 | skipper |
# | 2 | 1 | skipper |
# | 3 | 3 | kowalski |
# | 4 | 3 | kowalski |
# | 5 | 3 | kowalski |
# | 6 | 4 | rico |
# +-----+------+----------+
Agregat max działa na nowym indeksie moo:
delete a from penguins_copy a left join(
select max(moo) myindex, foo, bar
from penguins_copy
group by foo, bar) b
on a.moo = b.myindex and
a.foo = b.foo and
a.bar = b.bar
where b.myindex IS NULL;
#drop the extra column on the copied table
alter table penguins_copy drop moo;
select * from penguins_copy;
#drop the first table and put the copy table back:
drop table penguins;
create table penguins select * from penguins_copy;
obserwować i porządkować
drop table penguins_copy;
select * from penguins;
+------+----------+
| foo | bar |
+------+----------+
| 1 | skipper |
| 3 | kowalski |
| 4 | rico |
+------+----------+
Elapsed: 1458.359 milliseconds
Co robi ta duża instrukcja usuwania SQL?
Pingwiny stołowe z aliasem „a” pozostają połączone z podzbiorem pingwinów stołowych zwanych aliasami „b”. W prawej tabeli „b”, która jest podzbiorem, znajduje się maksymalny znacznik czasu [lub max moo] pogrupowany według kolumn foo i bar. Jest to dopasowane do tabeli „a” po lewej stronie. (foo, bar, baz) po lewej stronie ma każdy wiersz w tabeli. Podzbiór „b” po prawej stronie ma (maxtimestamp, foo, bar), który jest dopasowywany do lewej tylko na tym, który JEST maksymalny.
Każdy wiersz inny niż maksymalna ma wartość maxtimestamp równą NULL. Przefiltruj te wiersze NULL i masz zestaw wszystkich wierszy pogrupowanych według foo i bar, który nie jest najnowszym bazem sygnatury czasowej. Usuń te.
Przed uruchomieniem wykonaj kopię zapasową tabeli.
Zapobiegaj ponownemu występowaniu tego problemu na tym stole:
Jeśli to zadziałało i ugasiło pożar w „zduplikowanym rzędzie”. Wspaniały. Teraz zdefiniuj nowy złożony unikatowy klucz w tabeli (w tych dwóch kolumnach), aby w pierwszej kolejności zapobiec dodawaniu większej liczby duplikatów.
Podobnie jak w przypadku dobrego układu odpornościowego, złe wiersze nie powinny nawet trafiać do stołu w momencie wstawiania. Później wszystkie programy dodające duplikaty będą emitować swój protest, a kiedy je naprawisz, ten problem nigdy się nie pojawi.