public
Last active

Bulk MySQL insertion for Rails

  • Download Gist
insert.rb
Ruby
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
module InsertOnDuplicateKeyUpdate
def self.included(model)
model.class_attribute :insert_max_rows
model.insert_max_rows = 1_000
model.extend ClassMethods
end
module ClassMethods
def insert_on_duplicate_key_update(rows, on_duplicate = nil)
return insert_on_duplicate_key_update([rows], on_duplicate) unless rows.kind_of?(Array)
return if rows.empty?
provided_columns = rows.first.stringify_keys.keys
# unless rows.all? { |row| row.keys - provided_columns == [] && row.size == provided_columns.size }
# raise "All rows must have same columns (#{ provided_columns.join(",") })"
# end
timestamp_columns = column_names & %w(created_at created_on updated_at updated_on)
insert_columns = provided_columns | timestamp_columns
insert_columns.push(inheritance_column) if column_names.include?(inheritance_column)
column_list = insert_columns.map { |c| connection.quote_column_name(c) }.join(",")
time = Time.now
default_values = timestamp_columns.inject({}) { |hsh,col| hsh.update col => time }
default_values[inheritance_column] = self.to_s if column_names.include?(inheritance_column)
prefix = "INSERT INTO #{quoted_table_name} (#{column_list}) VALUES "
suffix = on_duplicate ? " ON DUPLICATE KEY UPDATE #{on_duplicate}" : ''
rows.each_slice(insert_max_rows) do |slice|
query = prefix + slice.map { |row|
values = default_values.stringify_keys.merge(row.stringify_keys)
"(" << values.values_at(*insert_columns).map { |v| quote_value(v) }.join(",") << ")"
}.join(",") + suffix
connection.insert(query)
end
end
end
end

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.