Mise à jour de la clé primaire de MySQL

Question

J'ai une table user_interactions avec 4 colonnes :

 user_1
 user_2
 type
 timestamp

La clé primaire est (user_1,user_2,type)
et je veux passer à (user_2,user_1,type)

Ce que j'ai fait, c'est.. :

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

et voilà...

Le problème est que la base de données se trouve sur un serveur.

Ainsi, avant que je puisse mettre à jour la clé primaire, de nombreux doublons s'étaient déjà glissés dans le système, et ils continuent de s'y glisser.

Que faire ?

Ce que je veux faire maintenant, c'est supprimer les doublons et conserver ceux qui sont les plus récents. timestamp (qui est une colonne du tableau).

Puis, d'une manière ou d'une autre, mettre à jour la clé primaire.

Answer 1

La prochaine fois, utilisez une seule instruction "alter table" pour mettre à jour la clé primaire.

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

Pour réparer les choses :

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;

Le verrouillage devrait empêcher l'arrivée d'autres mises à jour pendant que vous effectuez cette opération. Le temps que cela prend dépend évidemment de la taille de votre table.

Le principal problème se pose lorsque vous avez des doublons avec le même horodatage.

Answer 2

Si la clé primaire est une valeur auto-incrémentée, vous devez supprimer l'auto-incrément, puis supprimer la clé primaire et réintroduire l'auto-incrément.

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

puis rajouter l'incrémentation automatique

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

puis remettre l'incrémentation automatique à la valeur précédente

ALTER TABLE `xx` AUTO_INCREMENT = 5;

Answer 3

Vous pouvez utiliser le IGNORE mot-clé aussi, exemple :

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