Aktualizacja klucza podstawowego MySQL

103

Mam tabelę user_interactionsz 4 kolumnami:

 user_1
 user_2
 type
 timestamp

Klucz podstawowy to (user_1,user_2,type)
i chcę zmienić na(user_2,user_1,type)

Więc co zrobiłem to:

drop primary key ...  
add primary key (user_2,user_1,type)...

i voila ...

Problem polega na tym, że baza danych działa na serwerze.

Więc zanim mogłem zaktualizować klucz podstawowy, wiele duplikatów już wkradło się i wciąż się wkradają.

Co robić?

Chcę teraz usunąć duplikaty i zachować te z najnowszymi timestamp(czyli kolumną w tabeli).

A potem jakoś ponownie zaktualizuj klucz podstawowy.

mysql primary-key

— simplfuzz
źródło

16

Nagle czuję się źle z powodu każdego DBA, którego przeklinałem pod nosem ...

— Ignacio Vazquez-Abrams

5

następnym razem dodaj unikalny klucz z tymi samymi kolumnami co klucz podstawowy, a następnie zaktualizuj klucz podstawowy

— knittl

1

@Ignacio, jest na żywo na serwerze, ale to jest serwer zapasowy :-). Nie jestem DBA, ale nie spróbuję tego na NAPRAWDĘ live serwerze :-)

— simplfuzz

1

@knittl, tak właśnie myślałem, ale bardzo późno :-)

— simplfuzz

4

@pixeline: To złożony klucz podstawowy.

— Ignacio Vazquez-Abrams

231

Następnym razem użyj pojedynczej instrukcji „alter table”, aby zaktualizować klucz podstawowy.

alter table xx drop primary key, add primary key(k1, k2, k3);

Aby naprawić rzeczy:

create table fixit (user_2, user_1, type, timestamp, n, primary key( user_2, user_1, type) );
lock table fixit write, user_interactions u write, user_interactions write;

insert into fixit 
select user_2, user_1, type, max(timestamp), count(*) n from user_interactions u 
group by user_2, user_1, type
having n > 1;

delete u from user_interactions u, fixit 
where fixit.user_2 = u.user_2 
  and fixit.user_1 = u.user_1 
  and fixit.type = u.type 
  and fixit.timestamp != u.timestamp;

alter table user_interactions add primary key (user_2, user_1, type );

unlock tables;

Blokada powinna zatrzymać dalsze aktualizacje, gdy to robisz. Jak długo to potrwa, zależy oczywiście od wielkości twojego stołu.

Główny problem polega na tym, że masz kilka duplikatów z tą samą sygnaturą czasową.

— Jaskółka oknówka
źródło

11

Jeśli klucz podstawowy ma wartość auto_increment, musisz usunąć automatyczną inkrementację, a następnie upuścić klucz podstawowy i ponownie dodać autoinkrementację

ALTER TABLE `xx`
MODIFY `auto_increment_field` INT, 
DROP PRIMARY KEY, 
ADD PRIMARY KEY (new_primary_key);

następnie dodaj z powrotem automatyczną inkrementację

ALTER TABLE `xx` ADD INDEX `auto_increment_field` (auto_increment_field),
MODIFY `auto_increment_field` int auto_increment;

następnie ustaw automatyczną inkrementację z powrotem do poprzedniej wartości

ALTER TABLE `xx` AUTO_INCREMENT = 5;

— frazras
źródło

2

Możesz również użyć IGNOREsłowa kluczowego, na przykład:

 update IGNORE table set primary_field = 'value'...............

— Sarfraz
źródło