如何在rails中执行任意参数化的SQL [英] How to execute arbitrary parameterized SQL in rails

问题描述

出于性能原因，我需要在我的 Rails 模型中编写一个执行任意 SQL 的新方法:

For performance reasons, I need to write a new method in my Rails model that executes some arbitrary SQL:

UPDATE table
   SET col1 = ? AND col2 = ?
   WHERE id = ?

我知道我可以使用 ActiveRecord::Base.connection.execute 或 ActiveRecord::Base.connection.update 和一串 SQL 来获得我需要的结果，但是用实际参数值替换参数占位符 (?) 的正确程序是什么?是否有将参数插入 SQL 语句的 Rails 方法，还是应该通过手动插入来完成?后者似乎不安全...

I understand I can use ActiveRecord::Base.connection.execute or ActiveRecord::Base.connection.update with a string of SQL to get the results I need, but what is the proper procedure for substituting the parameter placeholders (?) with the actual parameter values? Is there a Rails method for interpolating parameters into a SQL statement, or should it just be done by manual interpolation? The latter seems unsafe...

推荐答案

您也可以这样做:

updates = ActiveRecord::Base.send(:sanitize_sql_array, ["name = ? and category = ?", name, category])
ActiveRecord::Base.connection.execute("update table set #{updates} where id = #{id.to_s.to_i}")

to_s 在 to_i 之前的 id 被调用，以防它为零.

to_s is being called on id before to_i in case it's nil.

这篇关于如何在rails中执行任意参数化的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！