首页 数据库 mysql教程 MySQL 数据库中删除重复记录方法总结

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

Jun 07, 2016 pm 05:52 PM
mysql 数据库 重复记录

本文章总结了关于在mysql数据库中各种删除重复记录的sql语句,下面我们用实例介绍了操作方法,大家可参考一下。

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.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> 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.hzhuti.com |

| 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

-> 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.hzhuti.com |

| 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.hzhuti.com |

| 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.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://安卓主题_www.hzhuti.com |

| 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.hzhuti.com |

| 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记录功能就实现了

本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
2 周前 By 尊渡假赌尊渡假赌尊渡假赌
仓库:如何复兴队友
4 周前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险:如何获得巨型种子
4 周前 By 尊渡假赌尊渡假赌尊渡假赌

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

如何优化 PHP 中的 MySQL 查询性能? 如何优化 PHP 中的 MySQL 查询性能? Jun 03, 2024 pm 08:11 PM

可以通过以下方式优化MySQL查询性能:建立索引,将查找时间从线性复杂度降至对数复杂度。使用PreparedStatements,防止SQL注入并提高查询性能。限制查询结果,减少服务器处理的数据量。优化连接查询,包括使用适当的连接类型、创建索引和考虑使用子查询。分析查询,识别瓶颈;使用缓存,减少数据库负载;优化PHP代码,尽量减少开销。

如何在 PHP 中使用 MySQL 备份和还原? 如何在 PHP 中使用 MySQL 备份和还原? Jun 03, 2024 pm 12:19 PM

在PHP中备份和还原MySQL数据库可通过以下步骤实现:备份数据库:使用mysqldump命令转储数据库为SQL文件。还原数据库:使用mysql命令从SQL文件还原数据库。

如何使用 PHP 插入数据到 MySQL 表中? 如何使用 PHP 插入数据到 MySQL 表中? Jun 02, 2024 pm 02:26 PM

如何将数据插入MySQL表中?连接到数据库:使用mysqli建立与数据库的连接。准备SQL查询:编写一个INSERT语句以指定要插入的列和值。执行查询:使用query()方法执行插入查询,如果成功,将输出一条确认消息。

如何修复 MySQL 8.4 上的 mysql_native_password 未加载错误 如何修复 MySQL 8.4 上的 mysql_native_password 未加载错误 Dec 09, 2024 am 11:42 AM

MySQL 8.4(截至 2024 年的最新 LTS 版本)中引入的主要变化之一是默认情况下不再启用“MySQL 本机密码”插件。此外,MySQL 9.0完全删除了这个插件。 此更改会影响 PHP 和其他应用程序

如何在 PHP 中使用 MySQL 存储过程? 如何在 PHP 中使用 MySQL 存储过程? Jun 02, 2024 pm 02:13 PM

要在PHP中使用MySQL存储过程:使用PDO或MySQLi扩展连接到MySQL数据库。准备调用存储过程的语句。执行存储过程。处理结果集(如果存储过程返回结果)。关闭数据库连接。

如何使用 PHP 创建 MySQL 表? 如何使用 PHP 创建 MySQL 表? Jun 04, 2024 pm 01:57 PM

使用PHP创建MySQL表需要以下步骤:连接到数据库。创建数据库(如果不存在)。选择数据库。创建表。执行查询。关闭连接。

在PHP中使用MySQLi建立数据库连接的详尽教程 在PHP中使用MySQLi建立数据库连接的详尽教程 Jun 04, 2024 pm 01:42 PM

如何在PHP中使用MySQLi建立数据库连接:包含MySQLi扩展(require_once)创建连接函数(functionconnect_to_db)调用连接函数($conn=connect_to_db())执行查询($result=$conn->query())关闭连接($conn->close())

iOS 18 新增'已恢复”相册功能 可找回丢失或损坏的照片 iOS 18 新增'已恢复”相册功能 可找回丢失或损坏的照片 Jul 18, 2024 am 05:48 AM

苹果公司最新发布的iOS18、iPadOS18以及macOSSequoia系统为Photos应用增添了一项重要功能,旨在帮助用户轻松恢复因各种原因丢失或损坏的照片和视频。这项新功能在Photos应用的"工具"部分引入了一个名为"已恢复"的相册,当用户设备中存在未纳入其照片库的图片或视频时,该相册将自动显示。"已恢复"相册的出现为因数据库损坏、相机应用未正确保存至照片库或第三方应用管理照片库时照片和视频丢失提供了解决方案。用户只需简单几步

See all articles