首页 > 数据库开发 > 删除一个表中的重复数据同时保留第一次插入那一条以及sql优化

删除一个表中的重复数据同时保留第一次插入那一条以及sql优化

业务:一个表中有很多数据(id为自增主键),在这些数据中有个别数据出现了重复的数据。

目标:需要把这些重复数据删除同时保留第一次插入的那一条数据,还要保持其它的数据不受影响。

解题过程:

第一步:查出所有要保留的下来的数据的id(save_id)

 

[sql][/sql] view plaincopy

  1. SELECT id as save_id
  2.   FROM yujing.alarm_event_info_snapshot aeis
  3.  where aeis.event_id in
  4.        (SELECT ae.id
  5.           FROM yujing.alarm_event ae
  6.          where ae.event_uuid like 'yuanwtj_%')
  7.  group by (aeis.event_id)

 

优化后:

 

[sql][/sql] view plaincopy

  1. SELECT aeis.id as save_id
  2.   FROM yujing.alarm_event ae
  3.  right join yujing.alarm_event_info_snapshot aeis
  4.     on aeis.event_id = ae.id
  5.  where ae.event_uuid like 'yuanwtj_%'
  6.  group by (aeis.event_id)

 

第二步:获取所有相关数据的id(all_id)

 

[sql][/sql] view plaincopy

  1. SELECT aeis.id as all_id
  2.   FROM yujing.alarm_event_info_snapshot aeis
  3.  where aeis.event_id in
  4.        (SELECT ae.id
  5.           FROM yujing.alarm_event ae
  6.          where ae.event_uuid like 'yuanwtj_%')
  7.  order by aeis.event_id

 

优化后:

 

[sql][/sql] view plaincopy

  1. SELECT aeis.id as all_id
  2.   FROM yujing.alarm_event ae
  3.  right join yujing.alarm_event_info_snapshot aeis
  4.     on aeis.event_id = ae.id
  5.  where ae.event_uuid like 'yuanwtj_%'

第三步:获取要删除的数据的id(del_id)

[sql][/sql] view plaincopy

  1. select ad.all_id as del_id
  2.   from (SELECT aeis.id as all_id
  3.           FROM yujing.alarm_event_info_snapshot aeis
  4.          where aeis.event_id in
  5.                (SELECT ae.id
  6.                   FROM yujing.alarm_event ae
  7.                  where ae.event_uuid like 'yuanwtj_%')) as ad
  8.  where ad.all_id not in (SELECT id as save_id
  9.                            FROM yujing.alarm_event_info_snapshot aeis
  10.                           where aeis.event_id in
  11.                                 (SELECT ae.id
  12.                                    FROM yujing.alarm_event ae
  13.                                   where ae.event_uuid like 'yuanwtj_%')
  14.                           group by (aeis.event_id))

优化后:

[sql][/sql] view plaincopy

  1. select ad.all_id as del_id
  2.   from (SELECT aeis.id as all_id
  3.           FROM yujing.alarm_event ae
  4.          right join yujing.alarm_event_info_snapshot aeis
  5.             on aeis.event_id = ae.id
  6.          where ae.event_uuid like 'yuanwtj_%') as ad
  7.   left join (SELECT aeis.id as save_id
  8.                FROM yujing.alarm_event ae
  9.               right join yujing.alarm_event_info_snapshot aeis
  10.                  on aeis.event_id = ae.id
  11.               where ae.event_uuid like 'yuanwtj_%'
  12.               group by (aeis.event_id)) as sd
  13.     on ad.all_id = sd.save_id
  14.  where sd.save_id is null

 

第四步:根据id删除所有节点,注意mysql中如果有大量数据时需要批量删除,我最后使用了ETL工具进行的批量删除

总结:在mysql数据库中,sql语句中最好不要在in或not in关键字的查询里动态获取匹配的值,数据量大的情况下使用它们效率很低,可以使用左右连接来代替in操作,这样效率会提高很多倍,大数据量下尤为明显。


本文固定链接: http://www.devba.com/index.php/archives/2331.html | 开发吧

报歉!评论已关闭.