使用唯一索引删除重复项

Question

我在两个表之间插入了字段A、B、C、D，认为我已经创建了一个唯一索引来防止重复。但是我不知怎么的只是简单地创建了一个普通索引。所以会有重复记录被插入。这是一个2000万条记录的表。

如果我将现有的索引从普通索引更改为唯一索引或者简单地添加一个针对A、B、C、D的新唯一索引，那么重复记录将被删除吗？还是添加失败，因为唯一记录已经存在了？我想测试一下，但是它有3000万条记录，我既不希望弄乱表格，也不希望重复它。

- user3649739

---

你需要使用 IGNORE 关键字 - 否则它会失败。在一个小的测试表上进行测试。 - Paul Spiegel

IGNORE是MySQL对标准SQL的扩展。它控制ALTER TABLE在新表中存在唯一键重复或启用严格模式时发生警告的情况下如何工作... - ALTER TABLE Syntax - Paul Spiegel

@PaulSpiegel 那很有道理。我尝试复制Table1，仅结构，然后添加唯一索引，在原始表和新索引表之间进行插入，但失败了。我这样做是因为在30百万条记录上更改或添加唯一索引需要太长时间。所以现在我在“Insert”后面添加了“Ignore”，它运行得非常好。 - user3649739

Answer 1

如果您的表中有重复项并且您使用

ALTER TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);

查询将会因为1062错误（重复键）而失败。但是如果使用IGNORE，则会忽略该错误。

-- (only works before MySQL 5.7.4)
ALTER IGNORE TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);

重复行将被删除。但是文档没有指定哪一行会被保留：

• IGNORE 是 MySQL 对标准 SQL 的扩展。如果新表中存在唯一键上的重复项，或启用了严格模式时发生警告，则它控制 ALTER TABLE 的工作方式。如果未指定 IGNORE，则如果出现重复键错误，则复制将中止并回滚。如果指定了 IGNORE，则仅使用一个唯一键上有重复的行。其他冲突行将被删除。不正确的值将被截断为最接近的可接受值。
  
  从 MySQL 5.7.4 开始，ALTER TABLE 的 IGNORE 子句已被删除，并且其使用会产生错误。

(ALTER TABLE 语法)

如果您的版本是 5.7.4 或更高版本 - 您可以：

• 将数据复制到临时表中（技术上不需要是临时的）。
• 清空原始表。
• 创建唯一的索引。
• 并使用INSERT IGNORE将数据复制回来（仍然可用）。

CREATE TABLE tmp_data SELECT * FROM mytable;
TRUNCATE TABLE mytable;
ALTER TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);
INSERT IGNORE INTO mytable SELECT * from tmp_data;
DROP TABLE tmp_data;

如果您使用IGNORE修饰符，则在执行INSERT语句时发生的错误将被忽略。例如，如果没有IGNORE，则表中重复现有UNIQUE索引或PRIMARY KEY值的行会导致重复键错误并中止语句。使用IGNORE，该行将被丢弃，不会发生错误。被忽略的错误会生成警告。

(INSERT Syntax)

还请参阅：INSERT ... SELECT Syntax和Comparison of the IGNORE Keyword and Strict SQL Mode

- Paul Spiegel

---

这真是糟心至极。我一直在为了在修改唯一索引以使用先前的不同列之前摆脱重复而苦恼。这帮了我大忙——谢谢。 - Mitya

看起来他们“修复”了这个功能，所以无论你是否使用IGNORE，它仍然会抛出一个“无法写入;表中有重复键”的错误。 - RVP

@RVP 在5.7.4及以上版本中，您仍然可以使用“INSERT IGNORE ... SELECT ...”复制数据。请参见更新。 - Paul Spiegel

相同的问题和相同的解决方案。谢谢。 - Rais Alam

Answer 2

如果您认为会有重复数据，添加唯一索引将失败。先检查哪些数据是重复的：

select * from
(select a,b,c,d,count(*) as n from table_name group by a,b,c,d) x
where x.n > 1

这可能是一个对2000万行数据进行的昂贵查询，但可以获得所有重复键，这将防止您添加主索引。 如果在子查询中使用where in语句：where a='some_value'，则可以将其拆分成更小的块。 对于检索到的记录，您必须更改某些内容以使行唯一。如果已完成此操作（查询返回0行），则应安全地添加主索引。

- verhie

Answer 3

你可以使用ON DUPLICATE KEY UPDATE代替IGNORE，这样你就可以控制哪些值应该优先。

- Oriol Vilaseca

Answer 4

回答你的问题 - 在一个有重复值的列上添加一个UNIQUE约束将会抛出一个错误。

例如，你可以尝试以下脚本：

CREATE TABLE `USER` (
  `USER_ID` INT NOT NULL,
  `USERNAME` VARCHAR(45) NOT NULL,
  `NAME` VARCHAR(45) NULL,
  PRIMARY KEY (`USER_ID`));

INSERT INTO USER VALUES(1,'apple', 'woz'),(2,'apple', 'jobs'),
(3,'google', 'sergey'),(4,'google', 'larry');

ALTER TABLE `USER` 
ADD UNIQUE INDEX `USERNAME_UNIQUE` (`USERNAME` ASC);
/*
Operation failed: There was an error while applying the SQL script to the database.
ERROR 1062: Duplicate entry 'apple' for key 'USERNAME_UNIQUE'
*/

- Sarath Chandra