【Ruby】MySQL2 gemで複数SQL文を一度に実行する(Mysql2::Client::MULTI_STATEMENTS)

複数のSQL文を許可するMysql2::Client::MULTI_STATEMENTSオプション

queryメソッドで複数文を許可するにはMysql2::Client.newメソッドのflagsオプションにMysql2::Client::MULTI_STATEMENTSを指定する。

require 'mysql2'

client = Mysql2::Client.new(
  host: 'localhost',
  username: 'user',
  password: 'xxxxxxxx',
  database: 'testdb',
  flags: Mysql2::Client::MULTI_STATEMENTS
)
sql = 'INSERT INTO users (name) VALUES ("Alice"); INSERT INTO users (name) VALUES ("Bob");'
client.query(sql)

実行するとエラー無く正常に動作し、２つのSQL文が実行されている。

mysql> select * from users;
+----+-------+---------------------+
| id | name  | created_at          |
+----+-------+---------------------+
|  1 | Alice | 2023-10-27 21:22:36 |
|  2 | Bob   | 2023-10-27 21:22:36 |
+----+-------+---------------------+
2 rows in set (0.00 sec)

とはいえ、上記はINSERT文に渡す値はハードコーディングしているが実際には外からあたえられる値を使う場合が多いはず。
その場合はprepared statementを使うほうが安全。その方法は後述する。

Mysql2::Client::MULTI_STATEMENTSフラグなしだとエラーになる

Mysql2::Client::MULTI_STATEMENTSフラグを設定しないMySQL2のデフォルト動作では複数SQL文を一度のクエリで実行できない。

require 'mysql2'

client = Mysql2::Client.new(
  host: 'localhost',
  username: 'user',
  password: 'xxxxxxxx',
  database: 'testdb'
)
sql = 'INSERT INTO users (name) VALUES ("Alice"); INSERT INTO users (name) VALUES ("Bob");'
client.query(sql)

上記のように一度のqueryメソッドでセミコロン区切りの複数SQL文を実行しようとするとエラーになる。

$ bundle exec ruby app.rb
/Users/sue/.rbenv/versions/3.1.3/lib/ruby/gems/3.1.0/gems/mysql2-0.5.5/lib/mysql2/client.rb:151:in `_query': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO users (name) VALUES ("Bob")' at line 1 (Mysql2::Error)
        from /Users/sue/.rbenv/versions/3.1.3/lib/ruby/gems/3.1.0/gems/mysql2-0.5.5/lib/mysql2/client.rb:151:in `block in query'
        from /Users/sue/.rbenv/versions/3.1.3/lib/ruby/gems/3.1.0/gems/mysql2-0.5.5/lib/mysql2/client.rb:150:in `handle_interrupt'
        from /Users/sue/.rbenv/versions/3.1.3/lib/ruby/gems/3.1.0/gems/mysql2-0.5.5/lib/mysql2/client.rb:150:in `query'
        from app.rb:6:in `<main>'

構文エラーとして判断されている。

prepared statementで使いたい(mysql2-cs-bindの利用)

prepare/executeではエラー

複数文を実行できて嬉しいのはだいたい更新系のクエリなのでqueryメソッドよりparepared statementのprepareメソッドを使うはず。
prepareメソッドでも同様に複数SQL文を実行できる。

require 'mysql2'

client = Mysql2::Client.new(
  host: 'localhost',
  username: 'user',
  password: 'xxxxxxxx',
  database: 'testdb',
  flags: Mysql2::Client::MULTI_STATEMENTS
)
sql = 'INSERT INTO users (name) VALUES (?); INSERT INTO users (name) VALUES (?);'
statement = client.prepare(sql)
statement.execute('Alice', 'Bob')

上記のようにprepared statementを使ったコードでは以下のようにSQL上の構文エラーとなる。

app.rb:6:in `prepare': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO users (name) VALUES (?)' at line 1 (Mysql2::Error)
        from app.rb:6:in `<main>'

mysql2-cs-bindでクライアント側でprepared statementを処理する

クライアント側でprepared statementを処理してSQLを実行してくれるmysql2-cs-bindを使えば解決する。 (Mysql2::Client::MULTI_STATEMENTSフラグも指定する)

source 'https://rubygems.org'

gem 'mysql2'
gem 'mysql2-cs-bind'

コードは以下のように書き換える。

require 'mysql2'
require 'mysql2-cs-bind'

client = Mysql2::Client.new(host: 'localhost', username: 'root', database: 'testdb', flags: Mysql2::Client::MULTI_STATEMENTS)
sql = 'INSERT INTO users (name) VALUES (?); INSERT INTO users (name) VALUES (?);'
client.xquery(sql, 'Alice', 'Bob')

可変の数のSQLを実行する

SQLの数が可変の場合でもprepared statementのSQLをSQLの数文だけセミコロンで結合した文字列を渡せば良い。

require 'mysql2'
require 'mysql2-cs-bind'

# 例えば以下のような配列があるとする
users = [
  ['Alice', 25],
  ['Bob', 20],
  ['Carol', 30]
]
client = Mysql2::Client.new(
  host: 'localhost',
  username: 'root',
  database: 'testdb',
  flags: Mysql2::Client::MULTI_STATEMENTS
)
# INSERT INTO users (name, age) VALUES (?, ?);INSERT INTO users (name, age) VALUES (?, ?);... という文字列を作る
sql = ('INSERT INTO users (name, age) VALUES (?, ?)' * users.size).join
# ? に合う形にflattenする
params = users.flatten
client.xquery(sql, *params)