MySQL removes duplicate data

Today a classmate asked me MySQL to remove duplicate data, I made a test by the way recorded:

To view the table structure:

Mysql> desc testdelete;+-------+-------------+------+-----+---------+----------------+| Field | Type        | Null | Key | Default | Extra          |+-------+-------------+------+-----+---------+----------------+| ID    | int     | NO   | PRI | NULL    | auto_increment | | one   | varchar (40) | YES  |     | NULL    |                | | |   | varchar (40) | YES  |     | NULL    |                | | three | varchar (40) | YES  |     | NULL    |                | +-------+-------------+------+-----+---------+----------------+4 rows in Set (0.10 sec)

Data for the table:

Mysql> SELECT * FROM testdelete;+----+------+------+-------+| ID | One  | |  three |+----+------+------+-------+|  1 | A    | A    | A     | |  2 | B    | B    | B     | |  3 | C    | C    | C     | |  4 | D    | D    | D     | |  5 | E    | E    | E     | |  6 | A    | A    | B     | | 12 | A    | A    | A     | | 13 | A    | A    | A     | | 14 | A    | A    | A     | | 15 | A    | A    | A     |+----+------+------+-------+10 rows in Set (0.00 sec)

The next test is:

1. Query duplicate data based on one column ( repeat according to single row )

SELECT *  from  WHERE in (theSELECTfrom GROUP by has COUNT>1

Results:

2. Delete duplicate records in the table: ( one with the lowest retention ID based on single-column deletion )

Deletefrom Testdeletewhere one in (SELECT one from Testdelete GROUP by one has             COUNT (one) > 1) and ID not in    (the SELECT                    MIN (id) from Testdelete GROUP by one has                  COUNT (one) > 1)

Error:

Cause: Probably because you can't do it directly in the query's statement.

WORKAROUND: Wrap the query one level:

Deletefrom Testdeletewhere One in (select one from                                  testdelete                   GROUP by one has                   COUNT (one) > 1) a) and ID not-in (select-MIN(ID) from testdel Ete GROUP by one has COUNT (one) > 1) b)        

Results:

(5 row (s) affected)
Execution time:00:00:00:094
Transfer time:00:00:00:000
Total time:00:00:00:094

To view the data again:

Restores the data.

3. Repeat according to One,two,three: ( Repeat according to single multiple judgment )

Results

4. Delete duplicate data from a table ( delete based on multiple columns and keep the lowest ID )

DELETE fromTestdeleteWHERE(One,two,three)inch(SELECTOne , both, three  from(SELECTOne , both, three  fromTestdeleteGROUP  byOne,two,three having COUNT(*)> 1) a) andId not inch(SELECT                    MIN(ID) from(SELECT                          MIN(ID) asID fromTestdeleteGROUP  byOne,two,three having COUNT(*)> 1) b)

Results:

(4 row (s) affected)
Execution time:00:00:00:125
Transfer time:00:00:00:000
Total time:00:00:00:125

View data:

Data restore

5. Find redundant duplicate records (multiple fields) in the table, without the record with the smallest ID ( check duplicates without the smallest ID based on multiple fields )

SELECT * fromTestdelete aWHERE(A.one,a.two,a.three)inch(SELECTOne , both, three  fromTestdeleteGROUP  byOne,two,three having COUNT(*)> 1)     andId not inch(SELECT                    MIN(ID) asID fromTestdeleteGROUP  byOne,two,three having COUNT(*)> 1)

Results:

MySQL removes duplicate data