Created

Embed URL

HTTPS clone URL

SSH clone URL

You can clone with HTTPS or SSH.

Download Gist

Bulk MySQL insertion for Rails

View insert.rb
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.