MySQL查看数据库表中的重复记录并删除

共饮一杯无

发布于 2022-11-28 08:31:55

11.5K00

代码可运行

文章被收录于专栏：Java升级打怪进阶之路Java升级打怪进阶之路

运行总次数：0

代码可运行

表数据如下

查看用户名相同的记录

select * from user where username in (select username from user group by username HAVING count(*) >1);

查看用户名和手机号都相同的重复记录

select * from user where (username,phone)
in (select username,phone from user group by username,phone HAVING count(*) >1);

注意：where条件中(username,phone)的括号不能少不然会报错。

删除用户名和手机号都相同的重复记录

DELETE from user where (username,phone) -- 注意：此处一定要加括号，当成联合字段来处理
		IN (
        -- 查找用户名和手机号均重复的用户信息
        SELECT username,phone FROM user GROUP BY username,phone HAVING COUNT(1) > 1
    ) AND id NOT IN (
        -- 查询最小id的记录
        SELECT MIN(id) FROM user GROUP BY username,phone HAVING COUNT(1) > 1
		);

上述语句看着是不是应该正常能执行删除掉用户名和手机号都相同的重复记录只保留id最小的那一条。实际执行会报如下错误：

1093 - You can’t specify target table ‘user’ for update in FROM clause 含义：不能在同一表中查询的数据作为同一表的更新数据。

正确写法如下：

DELETE from user where id in(
	select * from (
		select id from user where (username,phone) -- 注意：此处一定要加括号，当成联合字段来处理 
		IN (
				-- 查找用户名和手机号均重复的用户信息
				SELECT username,phone FROM user GROUP BY username,phone HAVING COUNT(1) > 1
		) AND id NOT IN (
				-- 查询最小id的记录
				SELECT MIN(id) FROM user GROUP BY username,phone HAVING COUNT(1) > 1
		) 
	)as u

);

将select出的结果再通过中间表select一遍，这样就规避了错误。

相关初始化数据

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `phone` varchar(20) DEFAULT NULL COMMENT '注册手机号',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='用户表';


INSERT INTO `user`(`id`, `username`, `phone`, `age`) VALUES (1, '我是主数据库的zjq', '13666666666', 18);
INSERT INTO `user`(`id`, `username`, `phone`, `age`) VALUES (2, 'zjq', '15656455662', 18);
INSERT INTO `user`(`id`, `username`, `phone`, `age`) VALUES (3, 'zjq666', '15656455663', 18);
INSERT INTO `user`(`id`, `username`, `phone`, `age`) VALUES (4, 'zjq', '15656455664', 18);
INSERT INTO `user`(`id`, `username`, `phone`, `age`) VALUES (5, 'zjq666', '15656455662', 18);
INSERT INTO `user`(`id`, `username`, `phone`, `age`) VALUES (6, 'zjq', '15656455666', 18);
INSERT INTO `user`(`id`, `username`, `phone`, `age`) VALUES (7, 'zjq888', '15656455667', 18);
INSERT INTO `user`(`id`, `username`, `phone`, `age`) VALUES (8, 'zjq', '15656455668', 18);
INSERT INTO `user`(`id`, `username`, `phone`, `age`) VALUES (9, 'zjq323', '15658855662', 18);
INSERT INTO `user`(`id`, `username`, `phone`, `age`) VALUES (10, 'zjq', '15656455662', 18);
INSERT INTO `user`(`id`, `username`, `phone`, `age`) VALUES (11, 'zjq', '16666666662', 99);