Summary of methods for deleting duplicate records in MySQL database

MySQL database, often encounter duplicate records, then need to delete duplicate records SQL, the following list of four ways to delete duplicate records, for different situations, I hope to help you.

1, look for redundant records in the table, duplicate records are based on a single field (Peopleid) to judge

The code is as follows	Copy Code
SELECT * from people where Peopleid to (select Peopleid from People GROUP by Peopleid have count (Peopleid) > 1 )

2, SQL delete duplicate records, duplicate records are based on a single field (Peopleid) to judge, leaving only the smallest ROWID records

The code is as follows	Copy Code
Delete from people where Peopleid on (select Peopleid from People GROUP by Peopleid have count (Peopleid) > 1) and rowID not in (select min (rowid) from people GROUP by Peopleid have Count (Peopleid) >1)

3. Find redundant records in the table (multiple fields)

The code is as follows	Copy Code
SELECT * from Vitae a WHERE (A.PEOPLEID,A.SEQ) in (select Peopleid,seq to Vitae GROUP by PEOPLEID,SEQ has count (*) > 1)

4, delete redundant records in the table (multiple fields), leaving only the smallest ROWID records

The code is as follows	Copy Code
Delete from Vitae a where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq to Vitae GROUP by PEOPLEID,SEQ have Count (*) & Gt 1) and rowID (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)

Demo Data

Table structure:

The code is as follows

Copy Code

Mysql> DESC Demo; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | ID | int (one) unsigned | NO | PRI | NULL | auto_increment | | site | varchar (100) | NO | MUL | | | +-------+------------------+------+-----+---------+----------------+ 2 rows in Set (0.00 sec) Data: Mysql> SELECT * FROM demo order by ID; +----+------------------------+ | ID | site | +----+------------------------+ | 1 | HTTP://WWW.111CN.NETN | | 2 | http://Android Theme _www.111cn.net | | 3 | http://www.zhutiy.com | | 4 | HTTP://WWW.111CN.NETN | | 5 | http://www.zhutiy.com | +----+------------------------+ 5 rows in Set (0.00 sec)

When you do not create a table or create INDEX permissions, you can use the following method:

If you want to delete older duplicate records, you can use the following statement:

code is as follows

copy code

mysql> Delete from a  -> using demo as A, demo as B -> where (a.id > b.ID) -> and (A.site = B.site); Query OK, 2 rows affected (0.12 sec)   Mysql> SELECT * FROM demo order by ID; +----+------------------------+ | id | site | +----+------------------------+ | 1 | http://www.111cn.netn | | 2 | http://Android theme _www.111cn.net | | 3 | http://www.zhutiy.com | +----+------------------------+ 3 Rows in Set (0.00 sec)

If you want to delete newer duplicate records, you can use the following statement:

code is as follows

copy code

mysql> Delete from a  -> using demo as A, demo as B -> where (a.ID < b.id) -> and (A.site = B.site); Query OK, 2 rows affected (0.12 sec)   Mysql> SELECT * FROM demo order by ID; +----+------------------------+ | id | site | +----+------------------------+ | 2 | http://Android theme _www.111cn.net | | 4 | http://www.111cn.netn | | 5 | http://www.zhutiy.com | +----+------------------------+ 3 Rows in Set (0.00 sec)

You can use the following statement to confirm the duplicate records that will be deleted:

The code is as follows	Copy Code
Mysql> SELECT a.* -> from demo A, demo b -> WHERE a.id > b.ID -> and (a.site = B.site); +----+------------------------+ | ID | site | +----+------------------------+ | 1 | HTTP://WWW.111CN.NETN | | 3 | http://www.zhutiy.com | +----+------------------------+ 2 rows in Set (0.00 sec)

If you have permission to create an index, you can use the following method:

To create a unique key index on a table:

code is as follows

copy code

mysql> Alter Ignore table demo add unique index Ukey (site); Query OK, 5 rows affected (0.46 sec) records:5 duplicates:2 warnings:0   Mysql> select * FROM demo-id; +----+------------------------+ | id | site | +----+------------------------+ | 1 | http://www.111cn.netn | | 2 | http://Android theme _www.111cn.net | | 3 | http://www.zhutiy.com | +----+------------------------+ 3 Rows in Set (0.00 sec)

After a duplicate record is deleted, you can delete the index if necessary:

The code is as follows	Copy Code
Mysql> ALTER TABLE demo DROP INDEX Ukey; Query OK, 3 rows affected (0.37 sec) Records:3 duplicates:0 warnings:0

If you have permission to create a table, you can use the following method:

Create a new table, and then insert the data that is not duplicated in the original table into the new table:

The code is as follows

Copy Code

Mysql> CREATE TABLE Demo_new as SELECT * from demo group by site; Query OK, 3 rows affected (0.19 sec) Records:3 duplicates:0 warnings:0 Mysql> Show tables; +----------------+ | Tables_in_test | +----------------+ | Demo | | Demo_new | +----------------+ 2 rows in Set (0.00 sec) Mysql> SELECT * FROM demo order by ID; +----+------------------------+ | ID | site | +----+------------------------+ | 1 | HTTP://WWW.111CN.NETN | | 2 | http://Android Theme _www.111cn.net | | 3 | http://www.zhutiy.com | | 4 | HTTP://WWW.111CN.NETN | | 5 | http://www.zhutiy.com | +----+------------------------+ 5 rows in Set (0.00 sec) Mysql> SELECT * from Demo_new the order by ID; +----+------------------------+ | ID | site | +----+------------------------+ | 1 | HTTP://WWW.111CN.NETN | | 2 | http://Android Theme _www.111cn.net | | 3 | http://www.zhutiy.com | +----+------------------------+ 3 Rows in Set (0.00 sec)

Then, back up the original table and rename the new table to the current table:

The code is as follows

Copy Code

Mysql> Rename table demo to Demo_old, Demo_new to demo; Query OK, 0 rows affected (0.04 sec) Mysql> Show tables; +----------------+ | Tables_in_test | +----------------+ | Demo | | Demo_old | +----------------+ 2 rows in Set (0.00 sec) Mysql> SELECT * FROM demo order by ID; +----+------------------------+ | ID | site | +----+------------------------+ | 1 | HTTP://WWW.111CN.NETN | | 2 | http://Android Theme _www.111cn.net | | 3 | http://www.zhutiy.com | +----+------------------------+ 3 Rows in Set (0.00 sec)

Note: Tables created in this manner will lose the index information of the original table!

The code is as follows	Copy Code
Mysql> DESC Demo; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | ID | int (one) unsigned | NO | | 0 | | | site | varchar (100) | NO | | | | +-------+------------------+------+-----+---------+-------+ 2 rows in Set (0.00 sec)

If you want to keep the same information as the original table, you can use the show create table demo; To view the creation statement of the original table, and then create a new table using the creation statement of the original table, and then use the INSERT ... SELECT statement to insert the data, and then rename the table.

instance

Today I did not intend to import a few test data, found that the test data, there are many data records are the same, I now want to delete this data,
How do I query for the same record of data? That said, the following statements can be viewed with the same record:

The code is as follows	Copy Code
SELECT COUNT (*) as C, Key_word from Search_keywrod GROUP by Key_word has C > 1

In which having C >1 represents the same number of records will have the same.

The same query is easier to implement, but to remove these duplicates, it's probably a bit of a hassle, because you want to delete the records in your own table, some friends may use the temporary tables, take the same records that need to be deleted, lead to the temporary table, and then delete the main table through the temporary table. or write a temporary program to delete one of the duplicate records.

The above two ways to delete duplicate records, the biggest trouble is the cumbersome operation. What we are introducing today is the use of MySQL's own statements, do not create temporary tables, do not write programs to delete their own duplicate records. Consider the following SQL statement:

The code is as follows	Copy Code
SELECT T1.id, T1.key_word From Search_keywrod T1, ( SELECT Key_word, MIN (ID) as MiniD From Search_keywrod GROUP by Key_word has COUNT (Key_word) > 1 ) T2 WHERE T1.key_word = T2.key_word and t1.id = T2.minid

This statement is the same as our first statement, but the benefit of this statement is min (ID), you can control whether to delete large ID duplicates (MAX), or delete small ID duplicate records (MIN).

OK, the improved statement implements the query and can now be removed using the DELETE from statement.

The code is as follows	Copy Code
DELETE from Search_keywrod the WHERE ID in (The SELECT ID from ( SELECT t1.id From Search_keywrod T1, ( SELECT Key_word, MIN (ID) as MiniD from Search_keywrod GROUP by Key_word has COUNT (Key_word) > 1 ) T2 WHERE T1.key_word = T2.key_word and t1.id = T2.minid ) T3)

When executing, please do more than once, because each time you delete, only delete duplicate records, if you repeat one record five times, then you will perform five times. So execute it a few times until there is no record to delete, so you can delete the duplicate MySQL record function directly from a statement.