如何在轨动态绑定执行原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屋！