使用Ruby on Rails活动​​记录插入多条记录

本文介绍了使用Ruby on Rails活动​​记录插入多条记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

有没有办法插入多条记录，而不是一次？

Is there a way to INSERT multiple records rather than one at a time?

我有一个非常非常难看rake任务是执行以下操作...

I have a very very ugly rake task that is doing the following ...

VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2000-03-07", :party => row[45], :participate => participated(row[45])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2000-11-07", :party => row[46], :participate => participated(row[46])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "SP", :election => "2000-05-08", :party => row[47], :participate => participated(row[47])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2001-11-06", :party => row[48], :participate => participated(row[48])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2002-05-07", :party => row[49], :participate => participated(row[49])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2002-11-05", :party => row[50], :participate => participated(row[50])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "SP", :election => "2003-05-06", :party => row[51], :participate => participated(row[51])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2003-11-04", :party => row[52], :participate => participated(row[52])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2004-03-02", :party => row[53], :participate => participated(row[53])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2004-11-02", :party => row[54], :participate => participated(row[54])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "SP", :election => "2005-02-08", :party => row[55], :participate => participated(row[55])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2005-05-03", :party => row[56], :participate => participated(row[56])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2005-09-13", :party => row[57], :participate => participated(row[56])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2005-11-08", :party => row[58], :participate => participated(row[58])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "SP", :election => "2006-02-07", :party => row[59], :participate => participated(row[59])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2006-05-02", :party => row[60], :participate => participated(row[60])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2006-11-07", :party => row[61], :participate => participated(row[61])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2007-05-08", :party => row[62], :participate => participated(row[62])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2007-09-11", :party => row[63], :participate => participated(row[63])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2007-11-06", :party => row[64], :participate => participated(row[64])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2007-11-06", :party => row[65], :participate => participated(row[65])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2007-12-11", :party => row[66], :participate => participated(row[66])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2008-03-04", :party => row[67], :participate => participated(row[67])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2008-10-14", :party => row[68], :participate => participated(row[68])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2008-11-04", :party => row[69], :participate => participated(row[69])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2008-11-18", :party => row[70], :participate => participated(row[70])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2009-05-05", :party => row[71], :participate => participated(row[71])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2009-09-08", :party => row[72], :participate => participated(row[72])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2009-09-15", :party => row[73], :participate => participated(row[73])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2009-09-29", :party => row[74], :participate => participated(row[74])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2009-11-03", :party => row[75], :participate => participated(row[75])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2010-05-04", :party => row[76], :participate => participated(row[76])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2010-07-13", :party => row[77], :participate => participated(row[77])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2010-09-07", :party => row[78], :participate => participated(row[78])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2010-11-02", :party => row[79], :participate => participated(row[79])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2011-05-03", :party => row[80], :participate => participated(row[80])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2011-09-13", :party => row[81], :participate => participated(row[81])) VoteRecord.create(:prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2011-11-08", :party => row[82], :participate => participated(row[82]))

这已经是非常低效的，并且必须有一个更好的办法...

This has to be extremely inefficient and there must be a better way...

推荐答案

在创建方法有一个数组作为参数。

The create method takes also an array as parameter.

VoteRecord.create( [ { :prospect_id => prospect.id, :state => "OH", :election_type => "GE", :election => "2011-11-08", :party => row[82], :participate => participated(row[82]) }, { :prospect_id => prospect.id, :state => "OH", :election_type => "PR", :election => "2011-09-13", :party => row[81], :participate => participated(row[81]) } ... ] )

这应该导致一个SQL语句（每记录不是一条语句），这是比较有效的。 （我想这是你换货与inefficent什么？）

This should result in one SQL statement (and not one statement per record) which is more efficent. (I guess that is what you ment with inefficent?)

如果你是在同一时间插入来自同一客户端多行，   使用INSERT语句具有多个值列表，以插入多个   行的时间。这是相当快（在一些快许多倍   例）比使用单独的单行INSERT语句。如果你是   将数据添加到非空表，可以调整   bulk_insert_buffer_size变量，使数据插入更快。   参见5.1.3节，服务器系统变量。

If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster. See Section 5.1.3, "Server System Variables".

从MySQL页面（但我想它应该是相同的其他DBS）

From the mysql page (but I guess it should be the same for other dbs)