Ruby-on-rails 批量插入导轨 3
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15784305/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Batch insertion in rails 3
提问by phoenixwizard
I want to do a batch insert of few thousand records into the database (POSTGRES in my case) from within my Rails App.
我想从我的 Rails 应用程序中批量插入几千条记录到数据库（在我的例子中是 POSTGRES）。
What would be the "Rails way" of doing it? Something which is fast and also correct way of doing it.
这样做的“Rails方式”是什么？一些快速且正确的方法。
I know I can create the SQL query by string concatenation of the attributes but I want a better approach.
我知道我可以通过属性的字符串连接来创建 SQL 查询，但我想要一个更好的方法。
回答by Simone Carletti
ActiveRecord .createmethod supports bulk creation. The method emulates the feature if the DB doesn't support it and uses the underlying DB engine if the feature is supported.
ActiveRecord.create方法支持批量创建。如果数据库不支持该功能，则该方法模拟该功能，如果支持该功能，则使用底层数据库引擎。
Just pass an array of options.
只需传递一系列选项。
# Create an Array of new objects
User.create([{ :first_name => 'Jamie' }, { :first_name => 'Jeremy' }])
Block is supported and it's the common way for shared attributes.
支持块，这是共享属性的常用方式。
# Creating an Array of new objects using a block, where the block is executed for each object:
User.create([{ :first_name => 'Jamie' }, { :first_name => 'Jeremy' }]) do |u|
u.is_admin = false
end
回答by phoenixwizard
I finally reached a solution after the two answers of @Simone Carletti and @Sumit Munot.
在@Simone Carletti 和@Sumit Munot 的两个答案之后，我终于找到了解决方案。
Until the postgres driver supports the ActiveRecord .create method's bulk insertion, I would like to go with activerecord-import gem. It does bulk insert and that too in a single insert statement.
在 postgres 驱动程序支持 ActiveRecord .create 方法的批量插入之前，我想使用activerecord-import gem。它在单个插入语句中进行批量插入。
books = []
10.times do |i|
books << Book.new(:name => "book #{i}")
end
Book.import books
In POSTGRES it lead to a single insert statemnt.
在 POSTGRES 中，它导致单个插入语句。
Once the postgres driver supports the ActiveRecord .create method's bulk insertion in a single insert statement, then @Simone Carletti 's solution makes more sense :)
一旦 postgres 驱动程序支持 ActiveRecord .create 方法在单个插入语句中的批量插入，那么@Simone Carletti 的解决方案就更有意义:)
回答by Sumit Munot
You can create a script in your rails model, write your queries to insert in that script In rails you can run the script using
您可以在 Rails 模型中创建脚本，编写查询以插入该脚本在 rails 中，您可以使用以下命令运行脚本
rails runner MyModelName.my_method_name
Is the best way that i used in my project.
是我在项目中使用的最佳方式。
Update:
更新：
I use following in my project but it is not proper for sql injection. if you are not using user input in this query it may work for you
我在我的项目中使用了以下内容，但它不适用于 sql 注入。如果您没有在此查询中使用用户输入，它可能对您有用
user_string = " ('[email protected]','a'), ('[email protected]','b')"
User.connection.insert("INSERT INTO users (email, name) VALUES"+user_string)
For Multiple records:
对于多条记录：
new_records = [
{:column => 'value', :column2 => 'value'},
{:column => 'value', :column2 => 'value'}
]
MyModel.create(new_records)
回答by Chris Aitchison
You can do it the fast way or the Rails way ;) The best way in my experience to import bulk data to Postgres is via CSV. What will take several minutes the Rails way will take several seconds using Postgres' native CSV import capability.
您可以通过快速方式或 Rails 方式进行 ;) 根据我的经验，将批量数据导入 Postgres 的最佳方式是通过 CSV。使用 Postgres 的本机 CSV 导入功能，Rails 方式需要几分钟的时间将需要几秒钟。
http://www.postgresql.org/docs/9.2/static/sql-copy.html
http://www.postgresql.org/docs/9.2/static/sql-copy.html
It even triggers database triggers and respects database constraints.
它甚至会触发数据库触发器并遵守数据库约束。
Edit (after your comment): Gotcha. In that case you have correctly described your two options. I have been in the same situation before, implemented it using the Rails 1000 save! strategy because it was the simplest thing that worked, and then optimized it to the 'append a huge query string' strategy because it was an order of magnitude better performing.
编辑（在您发表评论后）：陷阱。在这种情况下，您已正确描述了您的两个选项。我之前也遇到过同样的情况，使用 Rails 1000 save 实现了它！策略，因为它是最简单的工作，然后将其优化为“附加一个巨大的查询字符串”策略，因为它的性能要好一个数量级。
Of course, premature optimization is the root of all evil, so perhaps do it the simple slow Rails way, and know that building a big query string is a perfectly legit technique for optimization at the expense of maintainabilty. I feel your real question is 'is there a Railsy way that doesn't involve 1000's of queries?' - unfortunately the answer to that is no.
当然，过早的优化是万恶之源，所以也许可以用简单的、缓慢的 Rails 方式来做，并且要知道构建大查询字符串是一种以牺牲可维护性为代价的完全合法的优化技术。我觉得你真正的问题是“是否有一种不涉及 1000 次查询的 Railsy 方式？” - 不幸的是，答案是否定的。
