更新MySQL主键

Question

我有一个表 user_interactions，它有4个列：

 user_1
 user_2
 type
 timestamp

主键是(user_1,user_2,type)
我想要改成(user_2,user_1,type)

所以我做的是：

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

然后就做到了......

问题在于数据库是运行在服务器上的。

所以在我更新主键之前，就已经出现了许多重复项，并且它们还在不断地出现。

该怎么办呢？

现在我想要做的是删除重复项并保留最新的timestamp（这是表中的一列）。

然后再某种方式下再次更新主键。

- simplfuzz

---

我突然为我在心里咒骂的每一个数据库管理员感到内疚...... - Ignacio Vazquez-Abrams

下次添加一个与主键相同列的唯一键，然后更新主键。 - knittl

@Ignacio，它已经在服务器上运行了，但那只是一个备用备用服务器 :-）。我不是DBA，但我不会在真正的生产服务器上尝试这个东西 :-)。 - simplfuzz

@knittl，是的，现在我明白了，只是太晚了 :-) - simplfuzz

我一直以为一个表只能有一个主键。你有三个？！也许你想要一个主键和其他两列的索引？ - pixeline

这是一个复合主键。 - Ignacio Vazquez-Abrams

Answer 1

下次使用单个"alter table"语句来更新主键。

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;

在进行此操作时，锁定应该阻止进一步的更新。 这需要的时间显然取决于您的表的大小。

主要问题是如果您有一些具有相同时间戳的重复项。

- Martin

Answer 2

如果主键恰好是自增值，您需要删除自增，然后删除主键，然后重新添加自增。

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

然后加回自动递增

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

然后将自增值设置回先前的值

ALTER TABLE `xx` AUTO_INCREMENT = 5;

- frazras

Answer 3

您可以使用IGNORE关键字，例如：

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

- Sarfraz