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

2022-11-28 16:31:55 浏览数 (1)

表数据如下

查看用户名相同的记录

代码语言：javascript复制
select * from user where username in (select username from user group by username HAVING count(*) >1);

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

代码语言：javascript复制
select * from user where (username,phone)
in (select username,phone from user group by username,phone HAVING count(*) >1);

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

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

代码语言：javascript复制
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 含义：不能在同一表中查询的数据作为同一表的更新数据。

正确写法如下：

代码语言：javascript复制
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一遍，这样就规避了错误。

相关初始化数据

代码语言：javascript复制
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);

呜啦啦啦啦 看官喜欢的话点赞收藏或者关注一下吧

select table target

0 人点赞