更新MySQL主键 [英] Updating MySQL primary key

问题描述

我有一个表user_interactions，其中有4列:

I have a table user_interactions with 4 columns:

 user_1
 user_2
 type
 timestamp

主键是(user_1,user_2,type)
我想更改为(user_2,user_1,type)

The primary key is (user_1,user_2,type)
and I want to change to (user_2,user_1,type)

所以我做的是:

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

还有瞧...

问题在于数据库位于服务器上.

The problem is that database is live on a server.

因此，在我更新主键之前，许多重复项已经悄悄潜入，并且不断蔓延.

So before I could update the primary key, many duplicates already crept in, and they are continuously creeping in.

该怎么办?

我现在要做的是删除重复项，并保留最新的timestamp(表中的一列).

What I want to do now is to remove duplicates and keep the ones with the latest timestamp (which is a column in the table).

然后以某种方式再次更新主键.

And then somehow update the primary key again.

推荐答案

下次，使用单个更改表"语句更新主键.

Next time, use a single "alter table" statement to update the primary key.

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

要解决的问题:

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;

在执行此操作时，锁应阻止进来进一步的更新.很显然，此过程需要多长时间取决于表的大小.

The lock should stop further updates coming in while your are doing this. How long this takes obviously depends on the size of your table.

主要问题是，如果您有一些重复的时间戳都相同.

The main problem is if you have some duplicates with the same timestamp.

这篇关于更新MySQL主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！