MySQL 数据库中删除重复记录方法总结

MYSQL数据库中，经常会遇到重复记录的情况，那么就需要SQL删除重复记录，下面为您列举了四种删除重复记录的方式，用于不同的情况，希望对您有所帮助。

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

 代码如下 复制代码
select * from people  where peopleId in (select   peopleId from   people group by   peopleId having count(peopleId) > 1)    

 

2、SQL删除重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

 代码如下 复制代码
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、查找表中多余的重复记录（多个字段）

 代码如下 复制代码
select * from vitae a  where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)   

 

 
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

 代码如下 复制代码

delete from vitae a  where (a.peopleId,a.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)

演示数据

表结构：

 

 代码如下 复制代码

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)

数据：

mysql> select * from demo order by id;

+----+------------------------+

| id | site |

+----+------------------------+

| 1 | http://www.111cn.netn |

| 2 | http://安卓主题_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> 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://安卓主题_www.111cn.net |

| 3 | http://www.zhutiy.com |

+----+------------------------+

3 rows in set (0.00 sec)

如果你要删除较新的重复记录，可以使用下面的语句：

 代码如下 复制代码

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://安卓主题_www.111cn.net |

| 4 | http://www.111cn.netn |

| 5 | http://www.zhutiy.com |

+----+------------------------+

3 rows in set (0.00 sec)

你可以用下面的语句先确认将被删除的重复记录：

 

 代码如下 复制代码

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)

如果有创建索引的权限，可以用下面的方法：

在表上创建唯一键索引：

 代码如下 复制代码

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://安卓主题_www.111cn.net |

| 3 | http://www.zhutiy.com |

+----+------------------------+

3 rows in set (0.00 sec)

 

重复记录被删除后，如果需要，可以删除索引：

 代码如下 复制代码

mysql> alter table demo drop index ukey;

Query OK, 3 rows affected (0.37 sec)

Records: 3 Duplicates: 0 Warnings: 0

如果有创建表的权限，可以用下面的方法：

创建一个新表，然后将原表中不重复的数据插入新表：

 代码如下 复制代码

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://安卓主题_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 order by id;

+----+------------------------+

| id | site |

+----+------------------------+

| 1 | http://www.111cn.netn |

| 2 | http://安卓主题_www.111cn.net |

| 3 | http://www.zhutiy.com |

+----+------------------------+

3 rows in set (0.00 sec)

然后将原表备份，将新表重命名为当前表：

 

 代码如下 复制代码

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://安卓主题_www.111cn.net |

| 3 | http://www.zhutiy.com |

+----+------------------------+

3 rows in set (0.00 sec)

注意：使用这种方式创建的表会丢失原表的索引信息！

 代码如下 复制代码

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)

如果要保持和原表信息一致，你可以使用 show create table demo; 来查看原表的创建语句，然后使用原表的创建语句创建新表，接着使用 insert … select 语句插入数据，再重命名表即可。

实例

今天无意导入几个测试数据，发现测试数据中，有很多数据记录是相同的，我现在就想删除掉这些数据，
如何查询数据相同记录呢？这个好说，以下语句就可以查看相同记录的了：

 代码如下 复制代码
SELECT COUNT(*) AS c, key_word FROM search_keywrod GROUP BY key_word HAVING c > 1 

其中 HAVING c >1 代表相同记录数就有相同的了。

查询相同的是比较容易实现的了，但是想要删除这些重复的，估计就比较麻烦的了，因为你是要删除自身表里的记录，有些朋友可能就会使用到临时表，把相同需要删除的记录，导到时这个临时表，然后再通过临时表来删除主表。或者写一个临时程序，删除掉其中的一条重复记录。

以上二个方法删除重复记录，最大的麻烦就是操作繁琐。而我们今天介绍的就是利用MYSQL自身的语句，不创建临时表，不写程序来删除掉自身的重复记录。请看以下SQL语句：

 代码如下 复制代码
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 

这条语句就跟我们第一条语句是一样的功能，但是这条语句好处就是MIN(id)，可以控制是删除大的id重复记录(MAX)，还是删除小的id重复记录（MIN）。

OK，经过改良的语句实现了查询，现在就可以利用DELETE FROM语句来删除了。

 代码如下 复制代码

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) 

执行时，请多执行几次，因为每次删除时，只删除掉重复的一次记录，如果你一条记录重复五次，那你就要执行五次的了。所以多执行几次，直到没有可删除的记录了，这样你直接一条语句删除掉重复的mysql记录功能就实现了

时间： 2024-09-12 14:19:58

MySQL 数据库中删除重复记录方法总结的相关文章

MySQL数据库中删除重复记录的方法总结[推荐]_Mysql

表结构: mysql> desc demo; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL

sql 删除数据库中的重复记录方法

删除数据库中的重复记录(且仅保留一条有效记录)示例-  代码如下 复制代码 create table A ( userID int identity(1,1), userName varchar(20), userPwd varchar(20), userEmail varchar(50) ) insert into A(userName,userpwd) select 'qin','qin' union all select 'qin','qin1' union all select 'qin

mysql中删除重复记录sql语句

删除重复记录方法一: 1. 新建一个临时表  代码如下 复制代码 create table tmp as select * from youtable group by  name(name为不希望有重复的列) 2. 删除原来的表  代码如下 复制代码 drop table youtable 3. 重命名表  代码如下 复制代码 alter table tmp rename youtable 但是这个方法有个问题,由临时表转变过来的最终表,其表结构会和原来的不一致,需要手工更改.这个问题,待解决

在SQL中删除重复记录(多种方法)

重复|重复记录  学习sql有一段时间了,发现在我建了一个用来测试的表(没有建索引)中出现了许多的重复记录.后来总结了一些删除重复记录的方法,在Oracle中,可以通过唯一rowid实现删除重复记录:还可以建临时表来实现...这个只提到其中的几种简单实用的方法,希望可以和大家分享(以表employee为例). SQL> desc employee  Name                                      Null?    Type ------------------

在SQL中删除重复记录的多种方法

学习sql有一段时间了,发现在我建了一个用来测试的表(没有建索引)中出现了许多的重复记录.后来总结了一些删除重复记录的方法,在Oracle中,可以通过唯一rowid实现删除重复记录:还可以建临时表来实现...这个只提到其中的几种简单实用的方法,希望可以和大家分享(以表employee为例). SQL> desc employee Name Null? Type ----------------------------------------- -------- -----------------

教你几种在SQLServer中删除重复数据方法_MsSql

方法一 复制代码 代码如下: declare @max integer,@id integer declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) > 1 open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin select @max = @max -1 set rowcount

教你几种在SQLServer中删除重复数据方法

方法一 复制代码 代码如下: declare @max integer,@id integer declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) > 1 open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin select @max = @max -1 set rowcount

sql查询重复记录、删除重复记录方法大全

查找所有重复标题的记录: ? 1 SELECT * FROM t_info a WHERE ((SELECT COUNT(*) FROM t_info WHERE Title = a.Title) > 1) ORDER BY Title DESC 一.查找重复记录 1.查找全部重复记录 ? 1 Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1) 2.过滤重复记录(只显示一条) ?

mysql sql DISTINCT 删除重复记录

mysql教程 sql  distinct  删除重复记录 如何在mysql中使用distinct关键字过滤在一个select语句的结果列表重复值 如果你在查询数据时想删除重复的数据,可以利用distinct关键字过滤重复值,下面看下distinct用法. select distinct column_name from table_name; 让我们来看看一个简单的例子,我们有一个汽车列表.每辆车都有一个id,品牌,类型和颜色,但现在我们只是简单的列出所有品牌: select brand fr