class ActiveRecord::Base
Public Class Methods
bulk_write_mysql(fields, rows, upsert = nil)
click to toggle source
fields = %w(name email city created_at updated_at) rows = [
["smile", '2268571581@qq.com', 'shanghai', '2016-01-01', '2016-01-01'], ['malzahar', 'm8023zsm@live.com', 'beijing', '2016-01-01', '2016-01-01'],
]
# File lib/db_upsert.rb, line 49 def self.bulk_write_mysql(fields, rows, upsert = nil) return 0 if rows.empty? rows = rows.map do |row| values = row.map.with_index do |val| connection.quote(val) end.join ', ' "(#{ values })" end field_list = fields.map {|e| %Q("#{e}")}.join ', ' sql = "INSERT INTO #{ table_name } (#{ field_list }) VALUES #{ rows.join ', ' }" if upsert if !upsert[:update] update = fields.map(&:to_s) - upsert[:conflict].map(&:to_s) else update = upsert[:update] end update = update.map {|field| "#{ field } = VALUES#{ field }"} sql += " ON DUPLICATE KEY UPDATE #{ update.join ', ' }" if upsert[:where] sql += " WHERE #{ upsert[:where] }" end end res = connection.execute(sql) res.cmd_tuples end
bulk_write_pg(fields, rows, upsert = nil)
click to toggle source
fields = %w(name email city created_at updated_at) rows = [
["smile", '2268571581@qq.com', 'shanghai', '2016-01-01', '2016-01-01'], ['malzahar', 'm8023zsm@live.com', 'beijing', '2016-01-01', '2016-01-01'],
] BulkUpsert.bulk_write(fields, rows, :conflict => [:name]) conflict: uniq index columns update: if conflict update columns
# File lib/db_upsert.rb, line 14 def self.bulk_write_pg(fields, rows, upsert = nil) return 0 if rows.empty? rows = rows.map do |row| values = row.map.with_index do |val| connection.quote(val) end.join ', ' "(#{ values })" end field_list = fields.map {|e| %Q("#{e}")}.join ', ' sql = "INSERT INTO #{ table_name } (#{ field_list }) VALUES #{ rows.join ', ' }" if upsert if !upsert[:update] update = fields.map(&:to_s) - upsert[:conflict].map(&:to_s) else update = upsert[:update] end update = update.map {|field| "#{ field } = EXCLUDED.#{ field }"} sql += " ON CONFLICT (#{ upsert[:conflict].join ', ' }) DO UPDATE SET #{ update.join ', ' }" if upsert[:where] sql += " WHERE #{ upsert[:where] }" end end res = connection.execute(sql) res.cmd_tuples end