Rails3：使用 .where() 等哈希替换执行 SQL

Question

使用这样的简单模型，

class Model < ActiveRecord::Base
   # ...
end

我们可以执行这样的查询，

Model.where(["name = :name and updated_at >= :D", \
  { :D => (Date.today - 1.day).to_datetime, :name => "O'Connor" }])

其中哈希中的值将被替换到最终的 SQL 语句中，并根据底层数据库引擎进行适当的转义。

我想知道 SQL 执行的类似功能，例如：（

ActiveRecord::Base.connection.execute( \
    ["update models set name = :name, hired_at = :D where id = :id;"], \
    { :id => 73465, :D => DateTime.now, :name => "O'My God" }] \
  ) # THIS CODE IS A FANTASY. NOT WORKING.

请不要通过加载模型对象、修改然后保存来解决该示例！该示例只是我想拥有/了解的功能的说明。集中精力主题！）

最初的问题是我想将大量（数千行）数据插入数据库。我想使用 ActiveRecord 框架的 SQL 抽象的一些功能，但我不想使用基于 ActiveRecord::Base 的模型对象，因为它们太慢了！ （对于我当前的问题，每秒 8 个查询。）

Answer 1

query = ActiveRecord::Base.connection.raw_connection.prepare("INSERT INTO users (name) VALUES(:name)")
query.execute(:name => 'test_name')
query.close

Answer 2

使用批量插入的具体代码示例扩展@peufeu解决方案：

users_places = []
users_values = []
timestamp = Time.now.strftime('%Y-%m-%d %H:%M:%S')
params[:users].each do |user|
    users_places << "(?,?,?,?)"
    users_values << user[:name] << user[:punch_line] << timestamp << timestamp
end

bulk_insert_users_sql_arr = ["INSERT INTO users (name, punch_line, created_at, updated_at) VALUES #{users_places.join(", ")}"] + users_values
begin
    sql = ActiveRecord::Base.send(:sanitize_sql_array, bulk_insert_users_sql_arr)
    ActiveRecord::Base.connection.execute(sql)
rescue
    "something went wrong with the bulk insert sql query"
end

这是 参考ActiveRecord::Base中的sanitize_sql_array方法，它通过转义字符串中的单引号来生成正确的查询字符串。例如，punch_line“不要让他们让你失望”将变成“不要让他们让你失望”。

Answer 3

是的，您可以执行原始 SQL，但请查看有助于批量插入的 ar-extensions gem：

https://github .com/zdennis/ar-extensions

这里有一篇关于它的帖子，以及各种其他技术：

http://www.coffeepowered.net/2009/01/23/mass-inserting-data-in-rails-without-killing-your-performance/" Coffeepowered.net/2009/01/23/mass-inserting-data-in-rails-without-killing-your-performance/

Answer 4

对于 INSERT，使用长 VALUES 子句（如 Simon 的链接所示）对它们进行批处理是最快的方法（除非您想生成文本文件并使用 MySQL 的 LOAD DATA INFILE 将其加载到数据库中）。但是您必须非常小心转义文本值（示例中没有这样做）。

我问“你使用什么数据库”，因为它对于大规模更新确实很重要。

例如，您可以在 postgres 上执行此操作（我相信 SQL Server 将“columnX”更改为“colX”）：

UPDATE foo 
JOIN (VALUES (1,2),(3,4),... long list) v ON (foo.id=v.column1)
SET foo.bar = v.column2

并且您可以使用单个语句非常快地更新行负载。

如果您不需要 Ruby 对数据执行一些特定于 Ruby 的魔法，将数据从一个数据库传输到另一个数据库的最快方法是将其导出为文本文件（CSV 或制表符分隔），然后将其加载到另一个数据库上DB（MySQL 上的 LOAD DATA INFILE），可能在临时表中，并使用 SQL 进行批量处理。

编辑：这是我在Python中执行此操作的方法：

sql = [ "INSERT INTO foo (column list) VALUES " ]
values = []

for tuple in tuple_list:
     append "(?,?,?,?)" to sql
     extend values list with tuple

然后将sql连接到字符串中，您将得到“INSERT INTO foo（列列表）VALUES（？，？，？，？），（？，？，？，？），（？ ,?,?,?)" 和 "(?,?,?,?)" 重复次数与要插入的行数相同。

然后“values”包含 (a1,b1,c1,d1,a2,b2,c2,d2,a3,b3,c3,d3) 的列表，其中 an,bn,cn,dn 是您要为行插入的元组名词每一个都对应于 sql 字符串中的一个占位符。

然后将其传递给通常的“使用参数执行查询”函数，该函数将照常处理引用和转义。

Answer 5

最近，我在使用 mysql2 gem 将 100K+ 条记录插入到 Rails 4 应用程序的 MySQL 数据库中时遇到了类似的问题。数据包含在插入之前必须进行清理的字符。

我最终采用的解决方案是 https://www.coffeepowered.net/2009/01/23/mass-inserting-data-in-rails-without-killing-your-performance/

以下是上述链接中的相关代码块：

TIMES = 10000
inserts = []
TIMES.times do
  inserts.push "(3.0, '2009-01-23 20:21:13', 2, 1)"
end
sql = "INSERT INTO user_node_scores (`score`, `updated_at`, `node_id`, `user_id`) VALUES #{inserts.join(", ")}"

我所做的修改是对需要它的值使用公共方法 ActiveRecord::Base.sanitize() 。

inserts = []
created = Time.now.strftime "%Y-%m-%d %H:%M:%S"
params[:audits].each do |audit|
  inserts.push "(#{audit.user_id), #{created}," + ActiveRecord::Base.sanitize(audit.comment) + ", #{audit.status})"
end
sql = "INSERT INTO user_audits (`user_id`, `created_at`, `comment`, `status`) VALUES #{inserts.join(", ")}"