Ruby-on-rails 如何在 rails 中使用动态绑定执行原始更新 sql

How to execute a raw update sql with dynamic binding in rails

提问by ywenbo

I want to execute one update raw sql like below:

我想执行一个更新原始 sql，如下所示：

update table set f1=? where f2=? and f3=?

This SQL will be executed by ActiveRecord::Base.connection.execute, but I don't know how to pass the dynamic parameter values into the method.

这条SQL将由 执行ActiveRecord::Base.connection.execute，但我不知道如何将动态参数值传递到方法中。

Could someone give me any help on it?

有人可以给我任何帮助吗？

回答by Brian Deterling

It doesn't look like the Rails API exposes methods to do this generically. You could try accessing the underlying connection and using it's methods, e.g. for MySQL:

看起来 Rails API 并没有公开通用方法来执行此操作。您可以尝试访问底层连接并使用它的方法，例如对于 MySQL：

st = ActiveRecord::Base.connection.raw_connection.prepare("update table set f1=? where f2=? and f3=?")
st.execute(f1, f2, f3)
st.close

I'm not sure if there are other ramifications to doing this (connections left open, etc). I would trace the Rails code for a normal update to see what it's doing aside from the actual query.

我不确定这样做是否还有其他后果（连接保持打开状态等）。我会跟踪 Rails 代码以进行正常更新，以查看除了实际查询之外它在做什么。

Using prepared queries can save you a small amount of time in the database, but unless you're doing this a million times in a row, you'd probably be better off just building the update with normal Ruby substitution, e.g.

使用准备好的查询可以在数据库中为您节省少量时间，但是除非您连续执行一百万次，否则您最好只使用普通的 Ruby 替换来构建更新，例如

ActiveRecord::Base.connection.execute("update table set f1=#{ActiveRecord::Base.sanitize(f1)}")

or using ActiveRecord like the commenters said.

或者像评论者所说的那样使用 ActiveRecord。

回答by Paul A Jungwirth

ActiveRecord::Base.connectionhas a quotemethod that takes a string value (and optionally the column object). So you can say this:

ActiveRecord::Base.connection有一个quote方法接受一个字符串值（和可选的列对象）。所以你可以这样说：

ActiveRecord::Base.connection.execute(<<-EOQ)
  UPDATE  foo
  SET     bar = #{ActiveRecord::Base.connection.quote(baz)}
EOQ

Note if you're in a Rails migration or an ActiveRecord object you can shorten that to:

请注意，如果您在 Rails 迁移或 ActiveRecord 对象中，您可以将其缩短为：

connection.execute(<<-EOQ)
  UPDATE  foo
  SET     bar = #{connection.quote(baz)}
EOQ

UPDATE:As @kolen points out, you should use exec_updateinstead. This will handle the quoting for you and also avoid leaking memory. The signature works a bit differently though:

更新：正如@kolen 指出的那样，您应该exec_update改用。这将为您处理引用并避免内存泄漏。签名的工作方式有点不同：

connection.exec_update(<<-EOQ, "SQL", [[nil, baz]])
  UPDATE  foo
  SET     bar = 
EOQ

Here the last param is a array of tuples representing bind parameters. In each tuple, the first entry is the column type and the second is the value. You can give nilfor the column type and Rails will usually do the right thing though.

这里的最后一个参数是一个表示绑定参数的元组数组。在每个元组中，第一个条目是列类型，第二个条目是值。您可以指定nil列类型，但 Rails 通常会做正确的事情。

There are also exec_query, exec_insert, and exec_delete, depending on what you need.

还有exec_query, exec_insert, 和exec_delete，取决于您的需要。

回答by leandroico

You should just use something like:

你应该只使用类似的东西：

YourModel.update_all(
  ActiveRecord::Base.send(:sanitize_sql_for_assignment, {:value => "'wow'"})
)

That would do the trick. Using the ActiveRecord::Base#sendmethod to invoke the sanitize_sql_for_assignmentmakes the Ruby (at least the 1.8.7 version) skip the fact that the sanitize_sql_for_assignmentis actually a protected method.

这样就行了。使用ActiveRecord::Base#send方法调用sanitize_sql_for_assignment使得 Ruby（至少 1.8.7 版本）跳过sanitize_sql_for_assignment实际上是一个受保护的方法这一事实。

回答by mikowiec

Sometime would be better use name of parent class instead name of table:

有时最好使用父类的名称而不是表的名称：

# Refers to the current class
self.class.unscoped.where(self.class.primary_key => id).update_all(created _at: timestamp)

For example "Person" base class, subclasses (and database tables) "Client" and "Seller" Instead using:

例如“Person”基类、子类（和数据库表）“Client”和“Seller”而是使用：

Client.where(self.class.primary_key => id).update_all(created _at: timestamp)
Seller.where(self.class.primary_key => id).update_all(created _at: timestamp)

You can use object of base class by this way:

您可以通过这种方式使用基类的对象：

person.class.unscoped.where(self.class.primary_key => id).update_all(created _at: timestamp)

回答by ToTenMilan

Why use raw SQL for this?

为什么要为此使用原始 SQL？

If you have a model for ituse where:

如果您有模型，请使用where：

f1 = 'foo'
f2 = 'bar'
f3 = 'buzz'
YourModel.where('f1 = ? and f2 = ?', f1, f2).each do |ym|
  # or where(f1: f1, f2: f2).each do (...)
  ym.update(f3: f3) 
end

If you don't have a model for it(just the table), you can create a file and model that will inherit from ActiveRecord::Base

如果您没有它的模型（只有表），您可以创建一个文件和模型，该文件和模型将从ActiveRecord::Base

class YourTable < ActiveRecord::Base
  self.table_name = 'your_table' # specify explicitly if needed
end

and again use wherethe same as above:

并再次使用where与上述相同的内容：

回答by spume

Here's a trick I recently worked out for executing raw sql with binds:

这是我最近使用绑定执行原始 sql 的一个技巧：

binds = SomeRecord.bind(a_string_field: value1, a_date_field: value2) +
        SomeOtherRecord.bind(a_numeric_field: value3)
SomeRecord.connection.exec_query <<~SQL, nil, binds
  SELECT *
  FROM some_records
  JOIN some_other_records ON some_other_records.record_id = some_records.id
  WHERE some_records.a_string_field = 
    AND some_records.a_date_field < 
    AND some_other_records.a_numeric_field > 
SQL

where ApplicationRecorddefines this:

其中ApplicationRecord定义了这个：

# Convenient way of building custom sql binds
def self.bind(column_values)
  column_values.map do |column_name, value|
    [column_for_attribute(column_name), value]
  end
end

and that is similar to how AR binds its own queries.

这类似于 AR 绑定自己的查询的方式。

回答by donvnielsen

I needed to use raw sql because I failed at getting composite_primary_keys to function with activerecord 2.3.8. So in order to access the sqlserver 2000 table with a composite primary key, raw sql was required.

我需要使用原始 sql，因为我无法让 Composite_primary_keys 与 activerecord 2.3.8 一起运行。因此，为了使用复合主键访问 sqlserver 2000 表，需要原始 sql。

sql = "update [db].[dbo].[#{Contacts.table_name}] " +
      "set [COLUMN] = 0 " +
      "where [CLIENT_ID] = '#{contact.CLIENT_ID}' and CONTACT_ID = '#{contact.CONTACT_ID}'"
st = ActiveRecord::Base.connection.raw_connection.prepare(sql)
st.execute

If a better solution is available, please share.

如果有更好的解决方案，请分享。

回答by activars

In Rails 3.1, you should use the query interface:

在 Rails 3.1 中，你应该使用查询接口：

• new(attributes)
• create(attributes)
• create!(attributes)
• find(id_or_array)
• destroy(id_or_array)
• destroy_all
• delete(id_or_array)
• delete_all
• update(ids, updates)
• update_all(updates)
• exists?

• 新（属性）
• 创建（属性）
• 创建！（属性）
• 查找（id_or_array）
• 销毁（id_or_array）
• 全部销毁
• 删除（id_or_array）
• 删除所有
• 更新（ID，更新）
• update_all（更新）
• 存在吗？

update and update_all are the operation you need.

update 和 update_all 是您需要的操作。

See details here: http://m.onkey.org/active-record-query-interface

在此处查看详细信息：http: //m.onkey.org/active-record-query-interface