更新 MySQL 主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2341576/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating MySQL primary key
提问by simplfuzz
I have a table user_interactionswith 4 columns:
我有一个user_interactions有 4 列的表：
user_1
user_2
type
timestamp
The primary key is (user_1,user_2,type)
and I want to change to (user_2,user_1,type)
主键是(user_1,user_2,type)
，我想更改为(user_2,user_1,type)
So what I did was :
所以我所做的是：
drop primary key ...
add primary key (user_2,user_1,type)...
and voila...
瞧……
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.
因此，在我更新主键之前，许多重复项已经出现，并且它们不断地出现。
What to do?
该怎么办？
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).
我现在想要做的是删除重复项并保留最新的timestamp（这是表中的一列）。
And then somehow update the primary key again.
然后以某种方式再次更新主键。
回答by Martin
Next time, use a single "alter table" statement to update the primary key.
下次，使用单个“alter table”语句来更新主键。
alter table xx drop primary key, add primary key(k1, k2, k3);
To fix things:
解决问题：
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.
主要问题是您是否有一些具有相同时间戳的重复项。
回答by frazras
If the primary key happens to be an auto_increment value, you have to remove the auto increment, then drop the primary key then re-add the auto-increment
如果主键恰好是 auto_increment 值，则必须删除自动增量，然后删除主键，然后重新添加自动增量
ALTER TABLE `xx`
MODIFY `auto_increment_field` INT,
DROP PRIMARY KEY,
ADD PRIMARY KEY (new_primary_key);
then add back the auto increment
然后加回自动增量
ALTER TABLE `xx` ADD INDEX `auto_increment_field` (auto_increment_field),
MODIFY `auto_increment_field` int auto_increment;
then set auto increment back to previous value
然后将自动增量设置回以前的值
ALTER TABLE `xx` AUTO_INCREMENT = 5;
回答by Sarfraz
You can use the IGNOREkeyword too, example:
您也可以使用IGNORE关键字，例如：
update IGNORE table set primary_field = 'value'...............
