MYSQL中删除重复记录的最简单方法sql代码
应用中有时候会有 删除表中重复记录的需求，现在在这里举个例子，分享下我的解决方案，如有不完善的地方，喜欢大家能留言给我，大家一起进步。
例子：
CREATE TABLE `users` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL,
PRIMARY KEY (`id`)
)
表中name字段存在重复，解决思路首先是如何找到重复的记录然后将其删除；或者用逆向思维，找到要保留的数据，将是剩下的数据删除。
一般有两个方法：
1. 是用中间表来实现
1） 使用 create table like 复制出来一个中间表 ，然后用insert into select 把不重复的表导入到中间表中，然后再用中间表替代旧表。 具体实现如下
create table tmp_users like users;
Insert into tmp_users select min(`id`), `name` from users group by name ;
drop table users ;
alter table tmp_users rename users;
2） 使用 create table select 直接复制出来一个含有数据的中间表 然后用中间表替代旧表。具体实现如下
create table tmp_users select min(`id`), `name` from users group by name ;
truncate table users;
insert into users select * from tmp_users;
drop table tmp_users ;
以上两种方法的区别就是 create table like 和 create table select 的区别 ，create table like 复制的表结构包含索引而 create table select 不包含索引，没有索引对业务影响很大，这个要特别留意的。还有就是create table like 和 create table select 复制的表没有把表的权限给copy过来。要事后从新设置下。数据量大的时候应该选择 create table select ，先倾倒数据事后再为表建立索引。
至于用中间表的数据更新旧表的策略，要么用drop旧表再rename中间表。要么清空旧表数据再导入中间表数据。数据量大的时候前面方法效率较高。
2. 用一条sql语句来实现
1）找到要删除的数据然后删除这些数据。具体实现如下，
delete users as a from users as a,(
select min(id) , name from users group by name having count(name) > 1
) as b
where a.name = b.name and a.id <> b.id;
加上 having count(name) > 1 可以避免扫描没有重复的记录，提高效率
2）找到要保留的数据 然后用not in 来删除不再这些数据中的记录。大家很容易就想到如下的sql语句：
delete from users where id not in ( select min(id) from users group by name ); 但是mysql删 除动作不能带有本表的查询动作，意思是你删除users表的东西不能以users表的信息为条件所以这个语句会报错，执行不了。只要通过创建临时表作为查询条件。具体实现如下：
delete from users where id not in ( select * from ( select min(id) from users group by name ) );
在实际应用中，很可能会碰到一些需要删除某些字段的重复记录，我现在把我能想到的写下来，望高手们补充。
1、
具体实现如下：
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内容：
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
rminated 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)
14 rows in set (0.00 sec)
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
mysql> create temporary table tmp_wrap select * from users_groups group by uid having count(1) > 1 union all
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、还有一个很精简的办法。
查找重复的，并且除掉最小的那个。
delete users_groups as a from users_groups as a,
(
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、现在来看一下这两个办法的效率。
运行一下以下SQL 语句
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 a,
(
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 很明显的第二个比第一个扫描的函数要少。
