Mysql数据库中查询重复数据和去重数据 , 删除重复数据的sql及分析

本文介绍如何使用SQL语句处理数据库中的重复数据,包括查询、筛选和删除重复记录的方法,并提供适用于MySQL 5.7及以上版本的具体操作步骤。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

数据库中有重复数据时,用到哪些sql语句?
建表:

CREATE TABLE `user` (
  `id` bigint(255) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '名称',
  `age` int(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

这里有若干数据,并掺杂了重复数据
在这里插入图片描述

1. 查看过滤重复后的数据

思路, group by 分组可以对多个列进行分组, 分组后可以过滤掉重复的数据
这里在mysql5.7以上版本会报错,因为不支持select那些group by和聚合函数之外的字段
sql语句:

SELECT id,`name`,age,count(1)
	FROM user GROUP BY `name`,age

在这里插入图片描述
这里要么把id去掉,要么选择临时方案:

set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set @@SESSION.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

SELECT id,`name`,age,count(1)
	FROM user GROUP BY `name`,age;

2. 查看重复的数据

刚刚的语句已经把每个组对应的count数查询出来了,那么count>1的自然是重复的数据

SELECT id,`name`,age,count(1) as c
	FROM user GROUP BY `name`,age having c > 1

在这里插入图片描述

3. 删除重复的数据留下一条

思路: 刚刚已经把重复的数据查询出来了,包括id, 那么查询出每个重复组中的唯一一个id,也就是x,就可以delete … id not in (x)

上面说虽然5.7以上版本默认不支持查询group by 以外的字段,比如id,但是聚合函数还是可以的
子语句1:

SELECT MIN(id) FROM user
			GROUP BY name,age 

查询出来的id就是我们需要留下的不重复的数据的id
在这里插入图片描述

按理来说只要:
delete from user where id not in 子语句1

DELETE FROM user
	WHERE id NOT IN (
		SELECT MIN(id) FROM user
			GROUP BY name,age 
	)

但是报错了

DELETE FROM user
	WHERE id NOT IN (
		SELECT MIN(id) FROM user
			GROUP BY name,age 
	)
> 1093 - You can't specify target table 'user' for update in FROM clause
> 时间: 0.007s

因为在mysql中,不能在一条Sql语句中,即查询这些数据,同时修改这些数据

解决方法:select的结果再通过一个中间表temp进行select多一次,就可以避免这个错误

DELETE FROM user
	WHERE id NOT IN (
		SELECT temp.min_id FROM (
			SELECT MIN(id) min_id FROM user
				GROUP BY name,age
			)AS temp
	);
select * from user;
	

删除成功:
在这里插入图片描述

### 达梦数据库重复数据的方法 对于达梦数据库中存在大量历史表数据以及频繁的批插操作场景下,处理数据复问题至关要。由于达梦库在批插时一旦遇到单条记录插入失败则会停止整个批次的操作,这使得传统的MySQL `INSERT IGNORE INTO` 或者 MongoDB 的唯一索引机制不再适用。 针对此情况,在不考虑性能影响的前提下,可以利用SQL语句直接删除多余的复项: ```sql DELETE FROM table_name a WHERE rowid NOT IN (SELECT MIN(rowid) FROM table_name b WHERE a.key_columns = b.key_columns GROUP BY key_columns); ``` 上述命令将保留每组键列相同的最小ROWID对应的那一行而移除其他行[^1]。 另一种更为高效的做法是在应用层面控制输入源端口的数据质量,确保传入待写入的历史表前已完成初步清洗工作,避免不必要的冗余信息进入目标存储层。此外,也可以尝试调整业务逻辑设计模式,比如引入临时过渡表暂存新到来的信息并实施必要的验证流程后再正式迁移到最终的目的地表格内。 如果确实需要依赖数据库特性来防止复,则可探索使用触发器(Trigger),每当有新的记录试图加入指定表时自动激活特定条件检查;或是基于视图(Views)+物化视图(Materialized Views),构建辅助结构帮助识别潜在冲突实例。 最后值得注意的一点是关于驱动程序的选择及其配置参数设置也会影响整体行为表现,因此务必确认所使用的JDBC驱动版本同当前运行环境下的DM Kernel完全匹配,并参照官方指南完成相应初始化步骤[^3]。
评论 15
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值