51Testing软件测试论坛

 找回密码
 (注-册)加入51Testing

QQ登录

只需一步,快速开始

微信登录,快人一步

查看: 2102|回复: 1

MySQL 大批量插入,如何过滤掉重复数据?

[复制链接]
  • TA的每日心情
    无聊
    6 小时前
  • 签到天数: 919 天

    连续签到: 1 天

    [LV.10]测试总司令

    发表于 2021-9-16 10:51:43 | 显示全部楼层 |阅读模式
    加班原因是上线,解决线上数据库存在重复数据的问题,发现了程序的bug,很好解决,有点问题的是,修正线上的重复数据。
      线上库有6个表存在重复数据,其中2个表比较大,一个96万+、一个30万+,因为之前处理过相同的问题,就直接拿来了上次的Python去重脚本,脚本很简单,就是连接数据库,查出来重复数据,循环删除。
      emmmm,但是这个效率嘛,实在是太低了,1秒一条,重复数据大约2万+,预估时间大约在8个小时左右。
      盲目依靠前人的东西,而不去自己思考是有问题的!总去想之前怎么可以,现在怎么不行了,这也是有问题的!我发现,最近确实状态不太对,失去了探索和求知的欲望,今天算是一个警醒,颇有迷途知返的感觉。
      言归正传,下面详细介绍去重步骤。
    1. CREATE TABLE `animal` (  
    2.     `id` int(11) NOT NULL AUTO_INCREMENT,  
    3.     `name` varchar(20) DEFAULT NULL,  
    4.     `age` int(11) DEFAULT NULL,  
    5.     PRIMARY KEY (`id`)  
    6.   ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;  
    7.   INSERT INTO `pilipa_dds`.`student` (`id`, `name`, `age`) VALUES ('1', 'cat', '12');  
    8.   INSERT INTO `pilipa_dds`.`student` (`id`, `name`, `age`) VALUES ('2', 'dog', '13');  
    9.   INSERT INTO `pilipa_dds`.`student` (`id`, `name`, `age`) VALUES ('3', 'camel', '25');  
    10.   INSERT INTO `pilipa_dds`.`student` (`id`, `name`, `age`) VALUES ('4', 'cat', '32');  
    11.   INSERT INTO `pilipa_dds`.`student` (`id`, `name`, `age`) VALUES ('5', 'dog', '42');
    复制代码
    目标:我们要去掉name相同的数据。
      先看看哪些数据重复了:
    1. SELECT name,count( 1 )   
    2.   FROM  
    3.    student   
    4.   GROUP BY  
    5.   NAME   
    6.   HAVING  
    7.    count( 1 ) > 1;
    复制代码
    输出:

    1. name count(1) cat 2 dog 2
    复制代码
    name为cat和dog的数据重复了,每个重复的数据有两条;

    1.  Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(1)>1)
    复制代码
    删除全部重复数据,一条不留
      直接删除会报错:
    1. DELETE   
    2.   FROM  
    3.    student   
    4.   WHERE  
    5.    NAME IN (  
    6.    SELECT NAME   
    7.    FROM  
    8.     student   
    9.    GROUP BY  
    10.    NAME   
    11.   HAVING  
    12.    count( 1 ) > 1)
    复制代码
    报错:
    1. 1093 - You can't specify target table 'student' for update in FROM clause, Time: 0.016000s
    复制代码
    原因是:更新这个表的同时又查询了这个表,查询这个表的同时又去更新了这个表,可以理解为死锁。mysql不支持这种更新查询同一张表的操作。
      解决办法:把要更新的几列数据查询出来做为一个第三方表,然后筛选更新。
    1.  DELETE   
    2.   FROM  
    3.    student   
    4.   WHERE  
    5.    NAME IN (  
    6.    SELECT  
    7.     t.NAME   
    8.   FROM  
    9.    ( SELECT NAME FROM student GROUP BY NAME HAVING count( 1 ) > 1 ) t)
    复制代码
    删除表中删除重复数据,仅保留一条
      在删除之前,我们可以先查一下,我们要删除的重复数据是啥样的。
    1.  SELECT  
    2.    *   
    3.   FROM  
    4.    student   
    5.   WHERE  
    6.    id NOT IN (  
    7.    SELECT  
    8.     t.id   
    9.    FROM  
    10.    ( SELECT MIN( id ) AS id FROM student GROUP BY `name` ) t   
    11.    )
    复制代码
    啥意思呢,就是先通过name分组,查出id最小的数据,这些数据就是我们要留下的火种,那么再查询出id不在这里面的,就是我们要删除的重复数据。
      开始删除重复数据,仅留一条
      很简单,刚才的select换成delete即可。
    1.  DELETE   
    2.   FROM  
    3.    student   
    4.   WHERE  
    5.    id NOT IN (  
    6.    SELECT  
    7.     t.id   
    8.    FROM  
    9.    ( SELECT MIN( id ) AS id FROM student GROUP BY `name` ) t   
    10.    )
    复制代码
    90万+的表执行起来超级快。





    回复

    使用道具 举报

  • TA的每日心情
    开心
    2021-6-9 14:08
  • 签到天数: 1 天

    连续签到: 1 天

    [LV.1]测试小兵

    发表于 2021-9-18 15:15:53 | 显示全部楼层
    玩这东西很有意思
    回复 支持 反对

    使用道具 举报

    本版积分规则

    关闭

    站长推荐上一条 /1 下一条

    小黑屋|手机版|Archiver|51Testing软件测试网 ( 沪ICP备05003035号 关于我们

    GMT+8, 2024-3-29 15:57 , Processed in 0.066100 second(s), 23 queries .

    Powered by Discuz! X3.2

    © 2001-2024 Comsenz Inc.

    快速回复 返回顶部 返回列表