在日常业务场景中，经常会出现一个问题就是解决数据重复的问题，这里用到了一张用户表(s_user)做重复数据操作，分别包含了两个字段，id、name分别用于做唯一标示以及相同姓名的检索。

MySQL查询表内重复记录并删除

表结构以及测试数据

/*
Navicat MySQL Data Transfer

Source Server         : 120.25.170.205
Source Server Version : 50173
Source Host           : 120.25.170.205:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50173
File Encoding         : 65001

Date: 2018-08-13 17:11:24
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for s_user
-- ----------------------------
DROP TABLE IF EXISTS `s_user`;
CREATE TABLE `s_user` (
  `id` varchar(36) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of s_user
-- ----------------------------
INSERT INTO `s_user` VALUES ('a53cb32a-9ed0-11e8-87f3-00163e064c72', '李四');
INSERT INTO `s_user` VALUES ('86badf60-9ea0-11e8-87f3-00163e064c72', '老王');
INSERT INTO `s_user` VALUES ('a50cb058-9ed0-11e8-87f3-00163e064c72', '李四');
INSERT INTO `s_user` VALUES ('8529ce40-9ea0-11e8-87f3-00163e064c72', '老万');
INSERT INTO `s_user` VALUES ('a2d7d40c-9ed0-11e8-87f3-00163e064c72', '张三');
INSERT INTO `s_user` VALUES ('801528a0-9ea0-11e8-87f3-00163e064c72', '李四');
INSERT INTO `s_user` VALUES ('a313915e-9ed0-11e8-87f3-00163e064c72', '张三');
INSERT INTO `s_user` VALUES ('79a27d1a-9ea0-11e8-87f3-00163e064c72', '张三');

小葵花课堂开课了

1、如何通过用户名检索出相同的用户信息

select name,count(s.id) from s_user s GROUP BY s.`name`  HAVING count(s.`name`)>1

MySQL查询表内重复记录并删除

2、通过用户名检索出所有相同的用户信息并删除

delete from s_user
where name in (SELECT * from(select s.name from s_user s group by s.name having count(name) > 1)s)
and id not in (SELECT id from (select min(s.id)id from s_user s group by s.name having count(name)>1)a)

MySQL查询表内重复记录并删除

注意事项：由于子查询限制，如果子查询的 from 子句和更新、删除对象使用同一张表则需要在子查询外添加一个查询，否则会出现以下错误:

[Err] 1093 - You can't specify target table 's_user' for update in FROM clause

错误的查询：

delete from s_user
where name in (select s.name from s_user s group by s.name having count(name) > 1)
and id not in (SELECT id from (select min(s.id)id from s_user s group by s.name having count(name)>1)a)

正确的查询：

delete from s_user
where name in (SELECT * from(select s.name from s_user s group by s.name having count(name) > 1)s)
and id not in (SELECT id from (select min(s.id)id from s_user s group by s.name having count(name)>1)a)

秒客网

MySQL查询表内重复记录并删除

小葵花课堂开课了

相关文章