我们直接从一个简单的案例来入手,直观地看一下实践结果,那就开路吧!
- 创建表以及添加基础数据
为体验效果,添加基础数据的时候我们会有意添加两条重复的。
/* 建表语句 */
CREATE TABLE `user_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '删除重复数据测试表',
`user_uuid` varchar(255) DEFAULT NULL COMMENT '用户编号',
`user_name` varchar(255) DEFAULT NULL COMMENT '用户名',
`password` varchar(255) DEFAULT NULL COMMENT '密码',
`sex` int(255) DEFAULT NULL COMMENT '0:女;1:男',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
`modify_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
/* 新增测试数据 */
insert into user_test (user_uuid, user_name, password, sex) values ('1234567890', 'bearPotMan', '123456', '1');
insert into user_test (user_uuid, user_name, password, sex) values ('1234567890', 'bearPotMan', '123456', '1');
insert into user_test (user_uuid, user_name, password, sex) values ('2345678901', 'bearPot', '123456', '0');
insert into user_test (user_uuid, user_name, password, sex) values ('3456789012', 'bear', '123456', '1');
insert into user_test (user_uuid, user_name, password, sex) values ('4567890123', 'potMan', '123456', '1');
insert into user_test (user_uuid, user_name, password, sex) values ('2345678901', 'bearPot', '123456', '0');
![](https://img.haomeiwen.com/i8429109/3b4247b1b5d35443.png)
- 按以下步骤执行sql语句
- 查询重复数据以及重复的次数
select *,count(id) duplicate_count from user_test
group by user_uuid
having count(id)>1
order by id
![](https://img.haomeiwen.com/i8429109/33f8d24a5a0a193d.png)
- 根据user_uuid分组,查询所有数据的id
根据哪个字段分组依具体的业务而定,此处我们使用理论上唯一的user_uuid.
select min(id) from user_test
group by user_uuid
![](https://img.haomeiwen.com/i8429109/52888d50c9691779.png)
- 删除重复数据
delete from user_test
where id not in
(select a.id from ((select min(id) id from user_test group by user_uuid) a))
- 查看最终结果
去重最终结果.png
结束语:通过本次实践,看到最终的结果,我们达到了目的。
我是bearPotMan,一个经验不足的十八线演(码)员(农)。
Know everything,control everything!
网友评论