Create a gist now

Instantly share code, notes, and snippets.

Bulk MySQL insertion for Rails
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