Delete duplicates with a unique index

Question

I inserted between the two table fields A, B, C, D, believing that I created a unique index on A, B, C, D to prevent duplicates. However, I somehow just made a normal index on these. Thus, duplicates were inserted. This is 20 million table entries.

If I change my existing index from normal to unique, or just add a new unique index for A, B, C, D, will duplicates be deleted or will failure be added because there are unique entries? I would check it, but these are 30 million records, and I do not want him to hang up a table or duplicate it.

Answer 1

If you have duplicates in the table and you use

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

the request will fail with error 1062 (duplicate key).

But if you use IGNORE

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

duplicates will be deleted. But the documentation does not indicate which line will be saved:

• IGNORE is a MySQL extension for standard SQL. It controls how ALTER TABLE works if there are duplicates of unique keys in the new table or if warnings appear when strict mode is enabled. If IGNORE not specified, the copy is canceled and rolled back if errors with duplicate keys occur. If IGNORE specified, only one line is used for rows that duplicates a unique key. The remaining conflicting lines are deleted. Invalid values ​​are truncated to the maximum matching value.
  
  Starting in MySQL 5.7.4, the IGNORE clause for ALTER TABLE is deleted and its use causes an error.

( ALTER TABLE Syntax )

If your version is 5.7.4 or higher, you can:

• Copy the data to a temporary table (this does not have to be a temporary technical).
• Truncate the source table.
• Create a UNIQUE INDEX.
• And copy the data back using INSERT IGNORE (which is still available).

 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 during execution of the INSERT ignored. For example, without IGNORE , a row that duplicates an existing UNIQUE or PRIMARY KEY index in a table causes an error with a duplicate key, and the statement is aborted. With IGNORE , the string is discarded and no error occurs. Ignored errors generate warnings instead.

(INSERT syntax)

Also see: INSERT ... SELECT Syntax and IGNORE Comparison Keyword and SQL Strong Mode

Answer 2

if you think there will be duplicates, adding a unique index will fail. first check which duplicates are there:

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

This may be a costly query on 20 mm rows, but you will get all duplicate keys that will not allow you to add a primary index. You can break it down into smaller pieces if you do what is in the subquery: where a='some_value'

For the extracted records, you will have to change something to make the rows unique. If this is done (the query returns 0 rows), you should be safe to add a primary index.

Answer 3

Instead of IGNORE, you can use ON DUPLICATE KEY UPDATE, which will give you control over which values ​​should prevail.

Answer 4

To answer your question: adding a UNIQUE for a column with duplicate values ​​will result in an error.

For example, you can try the following script:

 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' */