How to delete duplicate records in MySQL

In practical applications, there may be some repeated records that need to delete some fields. I will write down what I can think of now, and hope the experts can add it. 1,
The specific implementation is as follows:
Table CREATE TABLE

--------------------------------------------------------------------
Users_groups create table 'users _ groups '(
'Id' int (10) unsigned not null auto_increment,
'Uid' int (11) not null,
'Gid' int (11) not null,
Primary Key ('id ')
) Engine = InnoDB auto_increment = 15 default charset = utf8

Users_groups.txt content:
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, 14 rows affected (0.05 Sec)
Records: 14 deleted: 0 skipped: 0 Warnings: 0

Mysql> select * From users_groups; query result (14 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

14 rows in SET (0.00 Sec)
Modify according to a brother's suggestion.
Mysql> create temporary table tmp_wrap select * From users_groups group by uid having count (1)> = 1;
Query OK, 7 rows affected (0.11 Sec)
Records: 7 duplicates: 0 Warnings: 0

Mysql> truncate table users_groups;
Query OK, 14 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. Find the duplicate and remove the smallest one. Delete users_groups as a from users_groups as,
(
Select *, min (ID) from users_groups group by uid having 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. Let's take a look at the efficiency of the two methods. Run the following SQL statement: Create index f_uid on users_groups (UID );
Explain select * From users_groups group by uid having count (1)> 1 Union all
Select * From users_groups group by uid having count (1) = 1; explain select * From users_groups as,
(
Select *, min (ID) from users_groups group by uid having 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 scan function.

This article is from "god, let's see it !" Blog. For more information, contact the author!