如何在 rails 中使用动态绑定执行原始更新 sql [英] How to execute a raw update sql with dynamic binding in rails

问题描述

我想执行一个如下的更新原始 sql:

I want to execute one update raw sql like below:

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

这个SQL会被ActiveRecord::Base.connection.execute执行，但是我不知道如何将动态参数值传入方法中.

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.

有人可以帮我吗?

推荐答案

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

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:

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

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

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.

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

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.

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

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

or using ActiveRecord like the commenters said.

这篇关于如何在 rails 中使用动态绑定执行原始更新 sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！