Delete duplicate record SQL statements in MySQL

Delete a duplicate record method:

1. Create a new temporary table

The code is as follows	Copy Code
CREATE TABLE tmp as SELECT * from Youtable Group by name (name is not expected to have duplicate columns)

2. Delete the original table

The code is as follows	Copy Code
drop table Youtable

3. Renaming tables

The code is as follows	Copy Code
ALTER TABLE TMP rename Youtable

But there is a problem with this method, the final table transformed by the temporary table, the table structure is inconsistent with the original, and needs to be changed manually. This problem, to be solved.
Delete duplicate record method two:

1. Create a new temporary table

The code is as follows	Copy Code
CREATE TABLE tmp as SELECT * from Youtable GROUP by name (name is not expected to have duplicate columns)

2. Empty the original table

The code is as follows	Copy Code
TRUNCATE TABLE youtable

3. Insert the temporary table into the youtable

The code is as follows	Copy Code
INSERT into tablename SELECT * from Temp

4. Delete temporary tables

The code is as follows	Copy Code
DROP TABLE Temp

Delete Duplicate record method three:

The code is as follows	Copy Code
Delete table where ID not in (select min (ID) from table group by name (name: Duplicate field))

Delete a duplicate record method four:

Specifically implemented as follows:

The code is as follows

Copy Code

Table Create Table ------------  -------------------------------------------------------- Users_groups CREATE TABLE ' users_groups ' ( ' ID ' int (a) unsigned not NULL auto_increment, ' UID ' int (one) is not NULL, ' GID ' int (one) is not NULL, PRIMARY KEY (' id ') ) Engine=innodb auto_increment=15 DEFAULT Charset=utf8 Users_groups.txt content: 1,11,502 2,107,502 3,100,503 4,110,501 5,112,501 6,104,502 7,100,502 8,100,501 9,102,501 10,104,502 11,100,502 12,100,501 13,102,501 14,110,501 mysql> load Data infile ' c:\users_groups.txt ' into table users_groups fields Terminated by ', ' lines terminated by ' n '; Query OK, rows affected (0.05 sec) Records:14 deleted:0 skipped:0 warnings:0 Mysql> select * from Users_groups; Query result (Records) ID UID GID 1 11 502 2 107 502 3 100 503 4 110 501 5 112 501 6 104 502 7 100 502 8 100 501 9 102 501 10 104 502 11 100 502 12 100 501 13 102 501 14 110 501 Rows in Set (0.00 sec)

Modified according to a brother's suggestion.

The code is as follows

Copy Code

Mysql> Create temporary table tmp_wrap SELECT * from Users_groups GROUP by UID has count (1) >= 1; Query OK, 7 rows affected (0.11 sec) Records:7 duplicates:0 warnings:0 mysql> truncate TABLE users_groups; Query OK, rows affected (0.03 sec) mysql> INSERT INTO users_groups select * from Tmp_wrap; Query OK, 7 rows affected (0.03 sec) Records:7 duplicates:0 warnings:0 Mysql> select * from Users_groups; Query result (7 records) ID UID GID 1 11 502 2 107 502 3 100 503 4 110 501 5 112 501 6 104 502 9 102 501 mysql> drop table tmp_wrap; Query OK, 0 rows affected (0.05 sec)

2. There is also a very streamlined approach.

Look for duplicates and get rid of the smallest one.

The code is as follows	Copy Code
Delete Users_groups as a from users_groups as a, ( Select *,min (ID) from Users_groups GROUP by UID has count (1) > 1 ) as B where A.uid = B.uid and a.id > b.id; (7 row (s) affected) (0 ms taken) Query result (7 records) ID UID GID 1 11 502 2 107 502 3 100 503 4 110 501 5 112 501 6 104 502 9 102 501

3. Now look at the efficiency of these two approaches.
Run the following SQL statement

The code is as follows

Copy Code

Create INDEX F_uid on users_groups (UID); Explain select * from Users_groups GROUP by UID has count (1) > 1 UNION ALL SELECT * FROM Users_groups GROUP by UID has count (1) = 1; Explain select * from Users_groups as a, ( Select *,min (ID) from Users_groups GROUP by UID has count (1) > 1 ) as B where A.uid = B.uid and a.id > b.id; Query result (3 records) ID select_type table Type possible_keys key Key_len ref rows Extra 1 PRIMARY users_groups index (NULL) F_UID 4 (NULL) 14 2 UNION users_groups index (NULL) F_UID 4 (NULL) 14 (NULL) UNION result <union1,2> all (NULL) (NULL) (NULL) (NULL) (NULL) Query result (3 records) ID select_type table Type possible_keys key Key_len ref rows Extra 1 PRIMARY <derived2> All (NULL) (NULL) (NULL) (NULL) 4 1 PRIMARY a ref primary,f_uid F_uid 4 B.uid 1 Using where 2 DERIVED users_groups index (NULL) F_UID 4 (NULL) 14

Obviously the second one is less than the first one to scan.

When no table is created or index permissions are created

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.net | | 2 | http://111cn.net | | 3 | http://www.111cn.net | | 4 | http://www.111cn.net | | 5 | http://www.111cn.net | +----+------------------------+ 5 rows in Set (0.00 sec) Mysql> SELECT * from Demo_new the order by ID; +----+------------------------+ | ID | site | +----+------------------------+ | 1 | http://www.111cn.net | | 2 | http://111cn.net | | 3 | http://www.111cn.net | +----+------------------------+ 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 * to demo order by ID; +----+------------------------+ | id | site                    | +----+------------------------+ |  1 | http://www.111cn.net  | |  2 | http://111cn.net        | |  3 | http://www.111cn.net | +----+------------------------+ 3 rows in Set (0.00 sec)