mysql删除重复索引_mysql – 删除具有唯一索引的重复项

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

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.

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

>将数据复制到临时表中(从技术上讲,它不需要是临时表).

>截断原始表格.

>创建UNIQUE INDEX.

>并使用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.