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)
+----+------------------------+ | 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 ( |
执行时,请多执行几次,因为每次删除时,只删除掉重复的一次记录,如果你一条记录重复五次,那你就要执行五次的了。所以多执行几次,直到没有可删除的记录了,这样你直接一条语句删除掉重复的mysql记录功能就实现了

热AI工具

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

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

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

记事本++7.3.1
好用且免费的代码编辑器

SublimeText3汉化版
中文版,非常好用

禅工作室 13.0.1
功能强大的PHP集成开发环境

Dreamweaver CS6
视觉化网页开发工具

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)

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

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

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

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

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

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

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

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