MySQL与ALTER IGNORE TABLE的重复错误 [英] MySQL Duplicate error with ALTER IGNORE TABLE

问题描述

我的MySQL中有一个表与重复。我尝试删除重复项并保留一个条目。我没有主键

我可以通过以下方式找到重复项：

  select user_id，server_id，count（*）as NumDuplicates 
 from user_server 
 group by user_id，server_id 
 have NumDuplicates> 1 
  

但不能删除它们：

  ALTER IGNORE TABLE`user_server` 
 ADD UNIQUE INDEX（`user_id`，`server_id`）; 
  

即使SET foreign_key_checks = 0;不管用。
错误代码：1062.键user_id_3的重复条目142-20

MySQL版本：5.5.18
引擎：InnoDB

是否有其他方式？

解决方案

可能最简单的方法是要复制表的结构，将唯一索引添加到新表，然后执行以下操作：

  INSERT IGNORE INTO new_table SELECT * FROM old_table 
  

---

删除重复项每个）不创建临时表，你可以这样做：

  ALTER TABLE`user_server` ADD`id` INT NOT NULL AUTO_INCREMENT第一关键
 
 DELETE us2 FROM user_server us1 
 JOIN user_server us2 USING（user_id，server_id）
 WHERE us1.id< us2.id; 
 
 ALTER TABLE`user_server` DROP`id`; 
  

I have a table in my MySQL with duplicates. I try to delete the duplicates and keep one entry. I don't have a primary key

I can finde the duplicates by:

select user_id, server_id, count(*) as NumDuplicates
from user_server
group by user_id, server_id
having NumDuplicates > 1

But can't delete them with:

ALTER IGNORE TABLE `user_server`  
ADD UNIQUE INDEX (`user_id`, `server_id`);

Even SET foreign_key_checks = 0; is not working. Error Code: 1062. Duplicate entry '142-20' for key 'user_id_3'

MySQL version: 5.5.18 Engine: InnoDB

Is there an other way?

解决方案

Probably the easiest way is to copy the structure of the table, add the unique index to the new table and then do:

INSERT IGNORE INTO new_table SELECT * FROM old_table

---

To delete the duplicates (except one of each) without creating a temp table, you can do that:

ALTER TABLE `user_server` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

DELETE us2 FROM user_server us1
 JOIN user_server us2 USING (user_id, server_id)
 WHERE us1.id < us2.id;

ALTER TABLE `user_server` DROP `id`;

这篇关于MySQL与ALTER IGNORE TABLE的重复错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！