猫窝私语 — Makumo's Blog

玛酷猫的温馨小窝,记录生活点点滴滴。

@玛酷猫2年前

02/24
21:01
数据库

MySQL同一表中重复数据处理

PS:工作中的随手记录,并非最佳处理方式,仅作为一种思路。

筹备新系统的过程中,遇到这样一个情况,数据库中一个表已经添加了300条信息,这时候突然从外部直接导入了1000条信息,其中有一部分重复信息,在重复的信息里面,新加入的数据有一个字段数据是原来数据里面没有的。现在需要将重复数据中原来没有的那个字段数据更新到原始数据中,并且删除重复数据。假设数据表结构简约如下:

表A
id name phone
1 张三
2 李四
3 王五
-----------------------------
1000 李四 1234567890
1001 赵六 0987654321

其中虚线以上部分是原有数据,以下部分是批量导入数据,phone字段仅新插入数据有内容。

由于在一个表中,先查下有多少重复数据,SQL如下

select name,count(name) as num from 表A group by name having num>1

有一点要注意就是在group by的语句中where和having的区别,两者处理数据顺序不同,where是在group by之前处理筛选数据,having则是group by之后在处理筛选数据,其中重复的数据大概有接近200条。

下一步就是把重复的这近200条数据中,新加入数据中那一个特殊字段的数据更新到老数据中。思路为先从上面重复数据里面分离出后添加的数据,将后添加数据特殊字段内容更新到前面老数据中,假设后添加数据起始id为1000。

update 表A as aa 
inner join (select a.id,a.name,a.phone from 表A as a,(select name,count(name) as num from 表A group by name having num>1) as b where a.name = b.name and a.id >= 1000) as bb
on aa.name = bb.name
set aa.phone = bb.phone
where aa.id < 1000

这里不知道是不是mysql(5.6)版本问题还是其他原因,之前使用update……set……from……select……where语句来更新数据的时候报错,后来换成上述语句更新。

更新完了后就是删除重复数据了,主要还是删除后面添加进来的数据。

delete from 表A 
where id in (
  select aa.id from (
    select a.id from 表A as a,
      (select name,count(name) as num from 表A group by name having num>1) as b 
      where a.name = b.name and a.id >= 1000
    ) aa
  )

这里其实多套了一层select,由于mysql中不允许在同一个表中先select出数据在update这个表,会报类似这样的错误 You can’t specify target table for update in FROM clause,中间多加了一层select来规避这个错误的出现。

至此问题处理完毕。其实在一个表中做这样的处理是非常不好的方法,在数据导入的时候先导入一个临时表中,在进行两个表之间的数据处理,从逻辑上和语句上面都简单很多,出现意外错误的几率也会少很多,误操作影响原有数据几率也小很多。

MySQL同一表中重复数据处理