分享
为什么问答平台  ›  专栏  ›  技术社区  ›  keruilin

删除由多个属性唯一标识的重复记录/对象 - Remove duplicate records/objects uniquely identified by multiple attributes

  •  1
  • keruilin  · 技术社区  · 2 月前

    我有一个名为herostatus的模型,具有以下属性:

    • 身份证件
    • 用户标识
    • 记录类型
    • 英雄类型(可以为空!)
    • 记录数据库
    • 创造在

    有超过100个英雄身份,一个用户可以有多个英雄身份,但不能有一次以上相同的英雄身份。

    用户的英雄身份是由可记录的英雄类型+英雄类型+可记录的ID组合唯一标识的。我想说的是,对于特定的用户,不能有重复的英雄身份。

    不幸的是,我没有进行有效性验证来保证这一点,所以在我做了一些代码更改之后,我为用户获得了一些重复的英雄状态。例如:

    user_id = 18
    recordable_type = 'Evil'
    hero_type = 'Halitosis'
    recordable_id = 1
    created_at = '2010-05-03 18:30:30'
    
    user_id = 18
    recordable_type = 'Evil'
    hero_type = 'Halitosis'
    recordable_id = 1
    created_at = '2009-03-03 15:30:00'
    
    user_id = 18
    recordable_type = 'Good'
    hero_type = 'Hugs'
    recordable_id = 1
    created_at = '2009-02-03 12:30:00'
    
    user_id = 18
    recordable_type = 'Good'
    hero_type = NULL
    recordable_id = 2
    created_at = '2009-012-03 08:30:00'
    

    (最后两个显然不是傻瓜。前两个是。)所以我想做的是去掉重复的英雄身份。哪一个?最近约会的那个。

    我有三个问题:

    1. 如何使用仅SQL方法删除重复项?

    2. 如何使用纯Ruby解决方案删除重复项?类似的事情: Removing "duplicate objects" .

    3. 如何进行验证以防止将来出现重复条目?

    2 回复  |  直到 9 年前
        1
  •  1
  •   mdma    9 年前

    对于只使用SQL的方法,我将使用这个查询(我假设ID是唯一的)。

    DELETE FROM HeroStatus WHERE id IN
    (SELECT id FROM 
       (SELECT user_id, recordable_type, hero_type, recordable_id, MAX(created_at)
         GROUP BY del.user_id, recordable_type, hero_type, recordable_id
         HAVING Count(id)>1) AS del 
          INNER JOIN HeroStatus AS hs ON
          hs.user_id=del.user_id AND hs.recordable_type=del.recordable_type 
           AND hs.hero_type=del.hero_type AND hs.recordable_id=del.recordable_id 
           AND hs.created_at = del.created_at)
    

    有点怪!查询使用自然键(用户ID、可记录类型、英雄类型)查找所有重复项,并选择最大的一个 created_at 值(最近创建的)。然后它找到这些行的ID(通过连接回主表)并删除具有该ID的行。

    (请先在表的副本上尝试此操作,并验证您获得了所需的结果!-)

    为防止将来发生这种情况,请在列上添加唯一索引或约束user_id、recordable_type、hero_type、recordable_id。例如

    ALTER TABLE HeroStatus 
    ADD UNIQUE (user_id, recordable_type, hero_type, recordable_id)
    

    编辑:

    在如下迁移中添加(和删除)此索引:

    add_index(:HeroStatus, [:user_id, :recordable_type, :hero_type, :recordable_id], :unique => true)
    remove_index(:HeroStatus, :column => [:user_id, :recordable_type, :hero_type, :recordable_id], :unique => true)
    

    或者,如果您想明确地命名它:

    add_index(:HeroStatus, [:user_id, :recordable_type, :hero_type, :recordable_id], :unique => true, :name => :my_unique_index)
    remove_index(:HeroStatus, :name => :my_unique_index)
    
        2
  •  0
  •   Jaykul    9 年前

    有时候你需要卷起袖子,做一些严肃的SQL来消灭所有你不想要的。这是很容易的,如果这是一个一次性的事情,而不是太难滚到一个耙任务,你可以按需开火。

    例如,要选择所有不同的状态记录,可以使用如下内容:

    SELECT id FROM hero_statuses GROUP BY user_id, hero_type, recordable_id
    

    考虑到这些是集合中足够唯一的记录,您可以删除不需要的所有记录:

    DELETE FROM hero_statuses WHERE id NOT IN (SELECT id FROM hero_statuses GROUP BY user_id, hero_type, recordable_id)
    

    与任何涉及从中删除的操作一样,我希望您不要在没有备份操作的常规预防措施的情况下,在生产数据上触发此操作。

    关于如何防止将来出现这种情况,如果这些是唯一的约束,请在它们上创建唯一的索引:

    add_index :hero_statuses, [ :user_id, :hero_type, :recordable_id ], :unique => true
    

    当您尝试引入重复记录时,这将生成ActiveRecord异常。唯一索引的一个好处是可以使用“insert ignore into…”或“insert…”在重复键上…“从可能的重复中恢复的功能。