如何在轨动态绑定执行原UPDATE 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.
可能有人给我任何帮助吗?
Could someone give me any help on it?
推荐答案
它看起来并不像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的$ C $下一个正常的更新,看看它是从实际的查询做了一边。
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.
使用prepared查询,可以为您节省少量的时间在数据库中,但除非你正在做这个一百万次在一排,你可能会更好只是建立在更新与正常的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(更新表设置为f1 =#{的ActiveRecord :: Base.sanitize(F1)})
或使用类似的ActiveRecord评论者说。
or using ActiveRecord like the commenters said.
这篇关于如何在轨动态绑定执行原UPDATE SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!