mysql - 使用唯一索引删除重复项

Question

我在两个表字段 A、B、C、D 之间插入，相信我已经在 A、B、C、D 上创建了唯一索引以防止重复。然而我以某种方式简单地对这些做了一个正常的索引。因此插入了重复项。这是2000万条记录的表。

如果我将现有索引从普通索引更改为唯一索引，或者只是为 A、B、C、D 添加新的唯一索引，重复项是否会被删除，或者由于存在唯一记录而添加会失败？我会测试它，但它有 3000 万条记录，我不想弄乱表格或重复它。

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 is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only one row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.
  
  As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error.

( ALTER TABLE Syntax )

如果您的版本是 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;

If you use the IGNORE modifier, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors generate warnings instead.

(INSERT Syntax)

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