MySQL 去重

activerecord_utils.rb

# -*- encoding: utf-8 -*-

class ActiveRecord::Base
  class << self
      # 去重
    def divorce_by(field_name, id = :id)
      ids = twin_ids_by(field_name, id)
      self.where(id: ids).delete_all unless ids.empty?
    end
    # 找出重复的 ids
    def twin_ids_by(field_name, id = :id)
      self.select(id).twins_by(field_name).uniq.pluck(id)
    end
    # 找出重复记录
    def twins_by(field_name, limit = 1)
      group(field_name).having('COUNT(?) > ?', field_name, limit)
    end
    # 反选字段
    def unselect(*args)
      self.select(self.column_names - args.map(&:to_s))
    end
  end
end

# 列出所有表名
module Kernel
  def tables
    ActiveRecord::Base.connection.tables
  end
end

order_ids = [
  4272, 4274, 4276, 4279, 4281, 4283, 4285, 4287, 4289, 4291, 4293, 4295, 
  4297, 4299, 4301, 4302, 4304, 4306, 4308, 4305, 4310, 4312, 4314, 4315, 
  4316, 4318, 4319, 4320, 4322, 4323, 4324, 4326, 4327, 4328, 4330, 4331, 
  4333, 4334, 4332, 4336, 4337, 4340, 4341, 4343, 4344, 4345, 4347, 4348, 
  4349, 4351, 4353, 4355, 4357, 4359, 4361, 4363, 4365, 4367, 4369, 4371, 
  4373, 4375, 4379, 4380, 4384, 4385, 4388, 4389, 4392, 4393, 4396, 4397, 
  4400, 4401, 4404, 4405, 4408, 4409, 4412, 4413, 5003
]

line_item_ids = [
  6036, 5238, 5240, 5224, 5273, 5275, 5276, 5230, 5232, 5248, 5244, 5222, 
  5226, 5228, 5242, 5237, 5262, 5263, 5266, 5265, 5267, 5246, 5302, 5304, 
  5310, 5314, 5301, 5298, 5300, 5233, 5316, 5259, 5250, 5252, 5253, 5255, 
  5256, 5257, 5269, 5270, 5271, 5281, 5282, 5283, 5277, 5278, 5279, 5284, 
  5285, 5287, 5288, 5291, 5292, 5294, 5295, 5297, 5306, 5308, 5318, 5312, 
  5320, 5322, 5324, 5351, 5353, 5365, 5366, 5326, 5339, 5340, 5334, 5336, 
  5331, 5330, 5356, 5358, 5347, 5348, 5344, 5343, 5360, 5362
]

billing_ids = [
  6128, 6130, 6132, 6133, 6136, 6140, 6142, 6145, 6146, 6147, 6149, 6151, 
  6153, 6156, 6157, 6160, 6161, 6163, 6165, 6173, 6174, 6178, 6180, 6181,
  6182
]

ids = User.select(:id).group(field_name).having("count(#{field_name}) > 1").pluck(:id)
User.where(id: ids).delete_all unless ids.empty?

感谢 @yangxing-star 效率高很多。