Summary of methods for deleting duplicate records in MySQL Databases

This article summarizes the various SQL statements used to delete duplicate records in the mysql database. The following describes the operation methods with examples. For more information, see.

This article summarizes the various SQL statements used to delete duplicate records in the mysql database. The following describes the operation methods with examples. For more information, see.

MYSQL databases often encounter repeated records, so you need to delete duplicate records in SQL. The following lists four ways to delete duplicate records for different scenarios, hope to help you.

1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleId ).

The Code is as follows:
Select * from people where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)

2. SQL deletes duplicate records. duplicate records are determined based on a single field (peopleId), leaving only the records with the smallest rowid

The Code is as follows:
Delete from people where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1) and rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1)

3. Search for redundant duplicate records in the table (multiple fields)

The Code is as follows:
Select * from vitae a where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)

4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid

The Code is as follows:
Delete from vitae a where (. peopleId,. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) and rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)

Demo data

Table Structure:

The Code is as follows:

Mysql> desc demo; + ------- + ------------------ + ------ + ----- + --------- + ---------------- + | Field | Type | Null | Key | Default | Extra | + ------- + ------------------ + ------ + ----- + --------- + ---------------- + | Id | int (11) 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.hzhuti.com | | 3 | http://www.zhutiy.com | | 4 | http://www.111cn.netn | | 5 | http://www.zhutiy.com | + ---- + ------------------------ + 5 rows in set (0.00 sec)

If you do not have the permission to create a table or create an index, you can use the following method:

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

The Code is as follows:

Mysql> delete from -> 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.hzhuti.com | | 3 | http://www.zhutiy.com | + ---- + ------------------------ + 3 rows in set (0.00 sec)

To delete a new duplicate record, use the following statement:

The Code is as follows:

Mysql> delete from -> 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.hzhuti.com | | 4 | http://www.111cn.netn | | 5 | http://www.zhutiy.com | + ---- + ------------------------ + 3 rows in set (0.00 sec)

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

The Code is as follows:
Mysql> SELECT .* -> 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 the permission to create an index, you can use the following method:

Create a unique key index on the table:

The Code is as follows:

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 order by id; + ---- + ------------------------ + | Id | site | + ---- + ------------------------ + | 1 | http://www.111cn.netn | | 2 | http: // Android theme _ www.hzhuti.com | | 3 | http://www.zhutiy.com | + ---- + ------------------------ + 3 rows in set (0.00 sec)

After a duplicate record is deleted, you can delete the index as needed:

The Code is as follows:
Mysql> alter table demo drop index ukey; Query OK, 3 rows affected (0.37 sec) Records: 3 Duplicates: 0 Warnings: 0

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

Create a new table and insert the data in the original table into the new table:

The Code is as follows:

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.hzhuti.com | | 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 order by id; + ---- + ------------------------ + | Id | site | + ---- + ------------------------ + | 1 | http://www.111cn.netn | | 2 | http: // Android theme _ www.hzhuti.com | | 3 | http://www.zhutiy.com | + ---- + ------------------------ + 3 rows in set (0.00 sec)

Back up the original table and rename the new table to the current table:

The Code is as follows:

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.hzhuti.com | | 3 | http://www.zhutiy.com | + ---- + ------------------------ + 3 rows in set (0.00 sec)

Note: using this method to create a table will lose the index information of the original table!

The Code is as follows:
Mysql> desc demo; + ------- + ------------------ + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ------- + ------------------ + ------ + ----- + --------- + ------- + | Id | int (11) unsigned | NO | 0 | | Site | varchar (100) | NO | + ------- + ------------------ + ------ + ----- + --------- + ------- + 2 rows in set (0.00 sec)

To maintain consistency with the original table information, you can use show create table demo; to view the statement for creating the original table, use the statement for creating the original table, and then use insert... Insert data using the select statement and rename the table.

Instance

I have no intention of importing several test data today. I found that many test data records are the same. Now I want to delete the data,
How can I query the same data record? The following statement can be used to view the same records:

The Code is as follows:
Select count (*) AS c, key_word FROM search_keywrod group by key_word HAVING c> 1

HAVING c> 1 indicates the same number of records.

It is easy to implement the same query, but it is difficult to delete these duplicates because you want to delete the records in your table, some friends may use the temporary table to export the same records to be deleted, and then delete the primary table through the temporary table. Or write a temporary program to delete one of the duplicate records.

The two methods above Delete duplicate records, the biggest trouble is the tedious operation. What we will introduce today is to use MYSQL statements to delete duplicate records without creating temporary tables or writing programs. See the following SQL statement:

The Code is as follows:
SELECT t1.id, t1.key _ word FROM search_keywrod t1 ,( SELECT key_word, MIN (id) AS minid FROM search_keywrod Group by key_word having count (key_word)> 1 ) T2 WHERE t1.key _ word = t2.key _ word AND t1.id = t2.minid

This statement is the same as the first statement, but the benefit of this statement is MIN (id), which can be controlled to delete large id record duplication (MAX ), or delete a small duplicate id record (MIN ).

OK. After the improved statement implements the query, you can use the DELETE FROM statement to DELETE the query.

The Code is as follows:
Delete from search_keywrod WHERE id IN (SELECT id FROM ( SELECT t1.id FROM search_keywrod t1 ,( SELECT key_word, MIN (id) AS minid FROM search_keywrod Group by key_word having count (key_word)> 1 ) T2 WHERE t1.key _ word = t2.key _ word AND t1.id = t2.minid ) T3)

During execution, Please execute multiple times, because each time you delete a record, only one record is deleted. If you repeat a record five times, you have to execute it five times. So execute several times until there is no record to be deleted. In this way, you can directly Delete duplicate mysql records using one statement.