MySQL 如何查找删除重复行?

如何查找重复行

第一步是定义什么样的行才是重复行。多数情况下很简单:它们某一列具有相同的值。本文采用这一定义,或许你对“重复”的定义比这复杂,你需要对sql做些修改。本文要用到的数据样本:

create table test(id int not null primary key, day date not null);

insert into test(id, day) values(1, '2006-10-08');
insert into test(id, day) values(2, '2006-10-08');
insert into test(id, day) values(3, '2006-10-09');

select * from test;
+----+------------+
| id | day        |
+----+------------+
|  1 | 2006-10-08 |
|  2 | 2006-10-08 |
|  3 | 2006-10-09 |
+----+------------+

前面两行在day字段具有相同的值,因此如何我将他们当做重复行,这里有一查询语句可以查找。查询语句使用GROUP BY子句把具有相同字段值的行归为一组,然后计算组的大小。

select day, count(*) from test GROUP BY day;
+------------+----------+
| day        | count(*) |
+------------+----------+
| 2006-10-08 |        2 |
| 2006-10-09 |        1 |
+------------+----------+

重复行的组大小大于1。如何希望只显示重复行,必须使用HAVING子句,比如

select day, count(*) from test group by day HAVING count(*) > 1;
+------------+----------+
| day        | count(*) |
+------------+----------+
| 2006-10-08 |        2 |
+------------+----------+

这是基本的技巧:根据具有相同值的字段分组,然后知显示大小大于1的组。

为什么不能使用WHERE子句?因为WHERE子句过滤的是分组之前的行,HAVING子句过滤的是分组之后的行。

如何删除重复行

一个相关的问题是如何删除重复行。一个常见的任务是,重复行只保留一行,其他删除,然后你可以创建适当的索引,防止以后再有重复的行写入数据库。

同样,首先是弄清楚重复行的定义。你要保留的是哪一行呢?第一行,或者某个字段具有最大值的行?本文中,假设要保留的是第一行——id字段具有最小值的行,意味着你要删除其他的行。

也许最简单的方法是通过临时表。尤其对于MYSQL,有些限制是不能在一个查询语句中select的同时update一个表。简单起见,这里只用到了临时表的方法。

我们的任务是:删除所有重复行,除了分组中id字段具有最小值的行。因此,需要找出大小大于1的分组,以及希望保留的行。你可以使用MIN()函数。这里的语句是创建临时表,以及查找需要用DELETE删除的行。

create temporary table to_delete (day date not null, min_id int not null);

insert into to_delete(day, min_id)
  select day, MIN(id) from test group by day having count(*) > 1;

select * from to_delete;
+------------+--------+
| day     | min_id |
+------------+--------+
| 2006-10-08 |    1 |
+------------+--------+

有了这些数据,你可以开始删除“脏数据”行了。可以有几种方法,各有优劣,只是说明在支持查询子句的关系数据库中,使用的标准方法。

delete from test
  where exists(
     select * from to_delete
     where to_delete.day = test.day and to_delete.min_id <> test.id
  )

如何查找多列上的重复行

有人最近问到这样的问题:我的一个表上有两个字段b和c,分别关联到其他两个表的b和c字段。我想要找出在b字段或者c字段上具有重复值的行。

咋看很难明白,通过对话后我理解了:他想要对b和c分别创建unique索引。如上所述,查找在某一字段上具有重复值的行很简单,只要用group分组,然后计算组的大小。并且查找全部字段重复的行也很简单,只要把所有字段放到group子句。但如果是判断b字段重复或者c字段重复,问题困难得多。这里提问者用到的样本数据

create table a_b_c(
  a int not null primary key auto_increment,
  b int,
  c int
);

insert into a_b_c(b,c) values (1, 1);
insert into a_b_c(b,c) values (1, 2);
insert into a_b_c(b,c) values (1, 3);
insert into a_b_c(b,c) values (2, 1);
insert into a_b_c(b,c) values (2, 2);
insert into a_b_c(b,c) values (2, 3);
insert into a_b_c(b,c) values (3, 1);
insert into a_b_c(b,c) values (3, 2);
insert into a_b_c(b,c) values (3, 3);

现在,你可以轻易看到表里面有一些重复的行,但找不到两行具有相同的二元组{b, c}。这就是为什么问题会变得困难了。

错误的查询语句

如果把两列放在一起分组,你会得到不同的结果,具体看如何分组和计算大小。提问者恰恰是困在了这里。有时候查询语句找到一些重复行却漏了其他的。这是他用到了查询

select b, c, count(*) from a_b_c
group by b, c
having count(distinct b > 1)
  or count(distinct c > 1);

结果返回所有的行,因为CONT(*)总是1.为什么?因为 >1 写在COUNT()里面。这个错误很容易被忽略,事实上等效于

select b, c, count(*) from a_b_c
group by b, c
having count(1)
  or count(1);

为什么?因为(b > 1)是一个布尔值,根本不是你想要的结果。你要的是

select b, c, count(*) from a_b_c
group by b, c
having count(distinct b) > 1
  or count(distinct c) > 1;

返回空结果。很显然,因为没有重复的{b,c}。这人试了很多其他的OR和AND的组合,用来分组的是一个字段,计算大小的是另一个字段,像这样

select b, count(*) from a_b_c group by b having count(distinct c) > 1;
+------+----------+
| b    | count(*) |
+------+----------+
|    1 |        3 |
|    2 |        3 |
|    3 |        3 |
+------+----------+

没有一个能够找出全部的重复行。而且最令人沮丧的是,对于某些情况,这种语句是有效的,如果错误地以为就是这么写法,然而对于另外的情况,很可能得到错误结果。

事实上,单纯用GROUP BY 是不可行的。为什么?因为当你对某一字段使用group by时,就会把另一字段的值分散到不同的分组里。对这些字段排序可以看到这些效果,正如分组做的那样。首先,对b字段排序,看看它是如何分组的

MySQL 如何查找删除重复行?

当你对b字段排序(分组),相同值的c被分到不同的组,因此不能用COUNT(DISTINCT c)来计算大小。COUNT()之类的内部函数只作用于同一个分组,对于不同分组的行就无能为力了。类似,如果排序的是c字段,相同值的b也会分到不同的组,无论如何是不能达到我们的目的的。

几种正确的方法

也许最简单的方法是分别对某个字段查找重复行,然后用UNION拼在一起,像这样:

select b as value, count(*) as cnt, 'b' as what_col
from a_b_c group by b having count(*) > 1
union
select c as value, count(*) as cnt, 'c' as what_col
from a_b_c group by c having count(*) > 1;
+-------+-----+----------+
| value | cnt | what_col |
+-------+-----+----------+
|     1 |   3 | b        |
|     2 |   3 | b        |
|     3 |   3 | b        |
|     1 |   3 | c        |
|     2 |   3 | c        |
|     3 |   3 | c        |
+-------+-----+----------+

输出what_col字段为了提示重复的是哪个字段。另一个办法是使用嵌套查询:

select a, b, c from a_b_c
where b in (select b from a_b_c group by b having count(*) > 1)
   or c in (select c from a_b_c group by c having count(*) > 1);
+----+------+------+
| a  | b    | c    |
+----+------+------+
| 7  |    1 |    1 |
| 8  |    1 |    2 |
| 9  |    1 |    3 |
| 10 |    2 |    1 |
| 11 |    2 |    2 |
| 12 |    2 |    3 |
| 13 |    3 |    1 |
| 14 |    3 |    2 |
| 15 |    3 |    3 |
+----+------+------+

这种方法的效率要比使用UNION低许多,并且显示每一重复的行,而不是重复的字段值。还有一种方法,将自己跟group的嵌套查询结果联表查询。写法比较复杂,但对于复杂的数据或者对效率有较高要求的情况,是很有必要的。

select a, a_b_c.b, a_b_c.c
from a_b_c
  left outer join (
     select b from a_b_c group by b having count(*) > 1
  ) as b on a_b_c.b = b.b
  left outer join (
     select c from a_b_c group by c having count(*) > 1
  ) as c on a_b_c.c = c.c
where b.b is not null or c.c is not null

文章来源:https://www.cnaaa.net,转载请注明出处:https://www.cnaaa.net/archives/8079

(0)
杰斯的头像杰斯
上一篇 2023年4月25日 下午4:20
下一篇 2023年4月26日 下午3:55

相关推荐

  • 数据库压力测试

    一、前言 在前面的压力测试过程中,主要关注的是对接口以及服务器硬件性能进行压力测试,评估请求接口和硬件性能对服务的影响。但是对于多数Web应用来说,整个系统的瓶颈在于数据库。 原因很简单:Web应用中的其他因素,例如网络带宽、负载均衡节点、应用服务器(包括CPU、内存、硬盘、连接数等)、缓存,都很容易通过水平的扩展(俗称加机器)来实现性能的提高。而对于MyS…

    2023年3月21日
    26300
  • Redis 缓存雪崩、缓存穿透、缓存击穿、缓存预热

    缓存雪崩 定义 缓存雪崩是指在短时间内,有大量缓存同时过期,导致大量的请求直接查询数据库,从而对数据库造成了巨大的压力,严重情况下可能会导致数据库宕机的情况叫做缓存雪崩。 正常情况下执行过程: 缓存雪崩下执行过程: 可以看到,当缓存失效时,大量请求直接绕过 Redis 去请求数据库,导致会对数据库造成很大压力。 解决 加锁排队 加锁排队可以起到缓冲的作用,防…

    2022年11月17日
    35800
  • count(1)、count(*)与count(列名)的执行区别

    在工作中遇到count(*)、count(1)、count(col) ,可能会让你分不清楚,都是计数,干嘛这么搞这么多东西。 count 作用 COUNT(expression):返回查询的记录总数,expression 参数是一个字段或者 * 号。 测试 MySQL版本:5.7.29 创建一张用户表,并插入一百万条数据,其中gender字段有五十万行是为n…

    2023年1月12日
    40700
  • windows server 2012上安装sql server 2008图文详解

    1.下载sql server 2008镜像文件 2.关闭防火墙 sql server 2008安装过程,检测安装进程会有报错,避免安装失败,这里先关闭防火墙 3.运行并安装程序 选择管理员身份运行 选择运行程序二不获取帮助 进入安装中心后选择 安装,再选择 全新安装 继续选择运行程序而不获取帮助 选择后如图,系统会自动检测系统,没警告说明可以定确定 输入密钥…

    2022年8月4日
    2.2K00
  • 如何快速实现 Redis 持久化

    RDB快照(Redis DataBase) RDB是一种快照存储持久化方式,具体就是将Redis某一时刻的内存数据保存到硬盘的文件当中,默认保存的文件名为dump.rdb,而在Redis服务器启动时,会重新加载dump.rdb文件的数据到内存当中恢复数据。 开启RDB持久化方式 开启RDB持久化方式很简单,客户端可以通过向Redis服务器发送save或bgs…

    2023年6月29日
    22000

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

在线咨询: QQ交谈

邮件:712342017@qq.com

工作时间:周一至周五,8:30-17:30,节假日休息

关注微信