mysql 使用ruby on rails活动记录插入多条记录

dnph8jn4  于 2023-05-21  发布在  Mysql
关注(0)|答案(5)|浏览(178)

有没有办法插入多条记录而不是一次插入一条记录？
我有一个非常非常丑陋的耙子任务，它正在做以下事情……

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]))

这肯定是非常低效的，一定有更好的方法。。

mysql

5条答案

按热度按时间
xzv2uavs

xzv2uavs1#

create方法也接受一个数组作为参数。

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查询，而不是单个SQL查询。它更有效，因为它只需要在引擎盖下创建一个单独的activerecord对象。
如果要同时从同一客户端插入多行，请使用带有多个VALUES列表的INSERT语句，以便一次插入多行。这比使用单独的单行INSERT语句快得多（在某些情况下快很多倍）。如果要将数据添加到非空表中，则可以优化bulk_insert_buffer_size变量，使数据插入速度更快。请参见第5.1.3节“服务器系统变量”。
From the mysql page（但我猜它应该是相同的其他dbs）

赞(0）分享  回复(0）举报  2023-05-21
7kqas0il

7kqas0il2#

不幸的是，这在Rails中是不可能的。
但是，activerecord-import是Rails3.x的一个很好的gem，它向模型类添加了一个import方法，并完全按照您希望的方式执行单个SQL插入语句。

赞(0）分享  回复(0）举报  2023-05-21
dvtswwa3

dvtswwa33#

Rails 6现在支持开箱即用：
https://web.archive.org/web/20191216050322/https://blog.bigbinary.com/2019/04/15/bulk-insert-support-in-rails-6.html
可以使用新添加的方法执行批量插入：insert_all，insert_all！和upsert_all。

赞(0）分享  回复(0）举报  2023-05-21
mtb9vblg

mtb9vblg4#

从Ruby on Rails 6.0开始，insert_all方法可用，只需一个查询就可以将多条记录保存到数据库中，这对于常数时间复杂度O（1）很好。

VoteRecord.insert_all([
    { :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]) }
    ...
  ])
赞(0）分享  回复(0）举报  2023-05-21
ct3nt3jp

ct3nt3jp5#

您可以将ActiveRecord语句 Package 在ActiveRecord::Base.transaction中：

ActiveRecord::Base.transaction do 
  1000.times { Post.create(options) } 
end

有关其他技术，请参见this post。

赞(0）分享  回复(0）举报  2023-05-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com