如何在Active Record中检索用于批量插入的已创建ID列表？

Question

我有三个模型：

class Coupon < ActiveRecord::Base
  belongs_to :event
  has_many :coupon_events, :dependent => :destroy
  has_many :events, :through => :coupon_events
end 

class Event < ActiveRecord::Base
  belongs_to :event
  has_many :coupon_events, :dependent => :destroy
  has_many :coupons, :through => :coupon_events
end

class CouponEvent < ActiveRecord::Base
  belongs_to :coupon
  belongs_to :event
end

我阅读了一个CSV文件，用于创建优惠券和优惠券事件。这种方法非常低效，因为记录是逐个创建的，并且每个记录都会导致多个查询，每个查询都包括两个插入语句。

我想使用单个插入查询，如下所示：

coupon_string = " ('abc','AAA'), ('123','BBB')"
Coupon.connection.insert("INSERT INTO coupons (code, name) VALUES"+coupon_string)

接下来我需要为CouponEvent模型创建第二个插入查询，但我需要一个返回的coupon_ids列表。是否有内置方法可以在插入时检索ID？

- Dawn Green

---

如果你只需要第一个查询返回的ID，那么只需将返回的ID存储在哈希表中，在插入时使用即可。 - My God

Answer 1

目前，最好的（但并不理想的）解决方案是使用"activerecord-import"进行批量导入。不幸的是，该gem不会返回已插入的id，因此您需要查询以获取这些id。也就是说，您会批量插入Events模型，查询数据库将它们全部取回到内存中。现在您拥有了Event id，因此可以创建优惠券并批量插入它们。对于CouponEvents，重复上述步骤。

与每个Event、Coupon和CouponEvent一次往返相比——对于包含数千行数据的文件可能需要执行数千次往返——您只需为每个模型执行2次往返——一次用于插入Event，一次用于获取带有id的Events，同样适用于Coupon和CouponEvent——总共6次往返。

- Rob

---

我创建了一个 activerecord-import 的版本，至少对于 postgres，它会返回插入的 id，使用了 @MBO 回答中的技术。它在这里：https://github.com/GoodMeasuresLLC/activerecord-import，并向官方版本提交了推送请求。 - Rob

Active Record Import 使用一次往返来发送查询吗？ - Henley

另外，你确定你的 gem 在多线程环境下返回正确的 ID 吗？比如 10 个不同的线程同时将 activerecord-import 插入到同一张表中？ - Henley

是的，只需要一次往返 - 这就是重点。对于多线程的事情，它依赖于数据库来创建ID，因此它们不必是连续的。数据库中的自增特性在多线程中并没有问题。 - Rob

@Rob，如果其他用户也在写作的情况下，有可能会得到与我保存的不同的ID吗？ - Qasim

Answer 2

实际上我不确定这是否可行（如果它创建了一个插入查询），但你可以尝试使用带有参数数组的#create方法：

new_coupons = Coupon.create([
  { :code => "abc", :name => "AAA" },
  { :code => "123", :name => "BBB" }
])

CouponEvent.create([
  { :enevt_id => ..., coupon_id: ...},
  ...
])

为了创建CouponEvent的参数列表，您需要将new_coupons返回的集合映射到id，并根据优惠券代码/名称（取决于它在CVS文件中的存储方式）添加event_id。

更新：

我已经自己检查过了。如果第一个解决方案不起作用（我的代码中没有唯一性约束的模型，所以我没有检查），并且您使用的是PostgreSQL，您可以始终执行以下操作：

res = Coupon.connection.execute(<<-EOSQL)
  INSERT INTO coupons (code, name)
  VALUES #{values}
  RETURNING id, code
EOSQL

你需要最后的“返回”子句，这样你就可以获取插入行的代码和插入的id。然后你需要映射结果集：

res.map {|row|
  { :coupon_id => row["id"],
    :event_id => events.find { |e| e.coupon_code == row["code"] }
  }
}

在SQL中，没有标准的方法返回插入行的列。只有在PostgreSQL中，“RETURNING”子句才能起作用。因此，如果您使用不同的数据库，您需要查看文档或逐个插入行。

同时，您也不能使用“connection.insert”。在ActiveRecord中，它仅返回一个插入行的ID，而不是所有行的ID。

- MBO

---

唉，我正在使用MySQL，但我刚刚发现我可能能够获得last_insert_id。请参阅http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html。 - Dawn Green

使用 Coupon.create 的版本并不是很有用 - 它会为每个优惠券创建和执行一个 SQL 查询。 - Rob

但是使用Coupon.connection.execute()的第二个版本非常棒，正是我所需要的。 - Rob

Answer 3

这样做的方法是通过插入具有唯一import_id值的记录来实现。步骤如下：

1. 向表中添加一个import_id列。根据您生成随机ID的方式，可以是INT或VARCHAR。

2. 在第一个INSERT之前，生成一个随机ID。

3. 进行第一个多值INSERT，对于每行使用相同的import_id。

4. SELECT id FROM first_table WHERE import_id=<the random import ID>

5. 使用返回的ID生成第二个多值INSERT。

- Animism

Answer 4

connection.insert 只返回一个 id，使用常规表达式对我有用

    insert_sql = <<-SQL
      WITH inserted_ids AS (
        INSERT INTO clients (email, name) VALUES #{array.join(', ')}
        RETURNING id
      )
      SELECT * FROM inserted_ids
    SQL
    result = ActiveRecord::Base.connection.execute(insert_sql)

- user1883793

---

MySQL不支持RETURNING操作？ - Kazuki

Answer 5

如果您正在使用mysql，并且在另一个脚本/进程中没有插入更多的行，则可以使用last_insert_id()获取第一行插入的id。

    first_id = ActiveRecord::Base.connection.execute("select last_insert_id()").first[0]

然后其他记录的ID是按顺序生成的。

即：

    data = %w(one two three)
    to_insert = "('" + data.join("'), ('") + "')"
    Model.connection.insert("INSERT INTO models(name) VALUES #{to_insert}")
    first_id = ActiveRecord::Base.connection.execute("select last_insert_id()").first[0].to_i
    hash = {}
    data.each_with_index {|d, i| hash[first_id + i] = d}

- rorra

---

你的 first_id 值实际上是从 MySQL AR 适配器的 #insert 语句返回的。至少我是这么认为的，但我不使用 MySQL，所以无法确认。 - MBO

这是一个不好的通用解决方案，因为它依赖于数据库生成连续的id。虽然数据库经常这样做，但如果有其他用户插入到同一张表中，它们就不会这样做。锁定表只会降低性能，我假设你一开始使用批量导入来提高性能。此外，数据库可能不生成连续的id还有其他原因，例如id分区（按范围划分，每个数据库分配一定范围的id，不一定相邻或递增，也可以按偶数/奇数划分）。 - Rob

实际上，在我回答的第一行中评论的条件下，这是一个很好的解决方案：“如果您正在使用mysql，并且没有在另一个脚本/进程中插入更多行，则可以通过使用last_insert_id（）获取插入的第一行的id”，最重要的是，它回答了问题：“如何检索Active Record中批量插入的已创建ID列表？”你假设太多了。请随意编写一个带有真实代码和真实解决方案的答案。 - rorra

@rorra 实际上这通常是一个不好的解决方案。我认为如果他像这样做了，OP本人可能会遇到难以调试的问题。我会问OP：你需要IDS做什么？如果是为了手动激活模型的回调函数，请考虑有一个单独的线程来查询表格，看看哪些ID是新插入的，并以这种方式处理这些IDS。 - Henley

不，根据我所说的条件，这是一个很好的解决方案，它快速而实用。请随意证明，在我为所描述的问题编写的条件下，所编写的解决方案将成为一个难以调试的错误。请随意在新答案中编写更好的解决方案，其中包括没有人要求的回调或线程。只是为了明确：“如果您正在使用mysql，并且您没有在另一个脚本/进程中插入更多行，则可以使用last_insert_id()获取第一行插入的id”。 - rorra