如何使用Rails在一个查询中执行多个语句？ [英] How can execute multiple statements in one query with Rails?

问题描述

我正在将Ruby on Rails与ActiveRecord和PostgreSQL一起使用。

I am using Ruby on Rails with ActiveRecord and PostgreSQL.

如何执行多个SQL查询？

How can i execute multiple sql queries?

我需要它来运行自定义迁移脚本，例如：

I need it for running a custom migration script, eg:

Foo.connection.execute <<-SQL.split(';').map(&:strip).join
 delete from metadata where record_type = 'Foo';
 TRUNCATE table1 RESTART IDENTITY;
 TRUNCATE table2 RESTART IDENTITY;
 delete from schema_migrations where version > '20120806120823';
SQL

我不接受用户的数据，因此我不必担心sql注入。

I am not accepting data from a user, so I'm not worried about sql-injection.

类似于 CLIENT_MULTI_STATEMENTS 可能在MySQL中？

Something like CLIENT_MULTI_STATEMENTS in MySQL maybe ?

来自MySQL / PHP文档：

From the MySQL/PHP docs:

CLIENT_MULTI_STATEMENTS ：告诉服务器客户端可以在单个字符串中发送
多个语句（以;）。如果未设置此
标志，则禁用多语句执行。有关此标志的更多信息，请参见此表后面的
注释。

CLIENT_MULTI_STATEMENTS: Tell the server that the client may send multiple statements in a single string (separated by ";"). If this flag is not set, multiple-statement execution is disabled. See the note following this table for more information about this flag.

推荐答案

它应该与PostgreSQL开箱即用，并通过pg gem和rails 3.2进行检查：

It should work out of the box with PostgreSQL, checked with pg gem and rails 3.2:

class Multitest < ActiveRecord::Migration
  def up
    execute <<-SQL
      create table x(id serial primary key);
      create table y(id serial primary key, i integer);
    SQL
  end

  def down
  end
end

另一方面，直接操作 schema_migrations 看起来很奇怪。

On a side note, manipulating schema_migrations directly looks strange.

这篇关于如何使用Rails在一个查询中执行多个语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！