如何使用Rails在一个查询中执行多个语句？

I am using Ruby on Rails with ActiveRecord and PostgreSQL.

我正在使用带有ActiveRecord和PostgreSQL的Ruby on Rails。

How can i execute multiple sql queries?

我如何执行多个SQL查询?

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

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

我不接受用户的数据,所以我不担心sql注入。

Something like CLIENT_MULTI_STATEMENTS in MySQL maybe ?

可能是MySQL中的CLIENT_MULTI_STATEMENTS之类的东西?

From the MySQL/PHP docs:

来自MySQL / PHP文档:

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.

CLIENT_MULTI_STATEMENTS:告诉服务器客户端可以在一个字符串中发送多个语句(以“;”分隔)。如果未设置此标志,则禁用多语句执行。有关此标志的更多信息,请参阅此表后面的注释。

3 个解决方案

#1

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

它应该与PostgreSQL开箱即用,用pg gem和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

On a side note, manipulating schema_migrations directly looks strange.

另外,操作schema_migrations直接看起来很奇怪。

#2

For mysql

querys = File.read("/where/is/myquerys.sql")
# or
querys = <<-SQL
 TRUNCATE table1 RESTART IDENTITY;
 TRUNCATE table2 RESTART IDENTITY;
 delete from schema_migrations where version > '20120806120823';
SQL

querys.split(';').map(&:strip).each do |query| 
  execute(query)
end

You may want see this question too: Invoking a large set of SQL from a Rails 4 application

您可能也想看到这个问题:从Rails 4应用程序调用大量SQL

#3

-1

Yes, you need CLIENT_MULTI_STATEMENTS:

是的,您需要CLIENT_MULTI_STATEMENTS:

In database.yml:

development:
  adapter: mysql2
  database: project_development
  flags:
    - MULTI_STATEMENTS

Then in your code:

然后在你的代码中:

connection.execute(multistatement_query)
# Hack for mysql2 adapter to be able query again after executing multistatement_query
connection.raw_connection.store_result while connection.raw_connection.next_result

See https://*.com/a/11246837/338859 for details

有关详细信息,请参阅https://*.com/a/11246837/338859

#1