ActiveRecord の insert_all で発行されるSQLに ON DUPLICATE KEY UPDATE がある理由
タイトルに書きたいことが長いとタイトル付けに困りますね。
今日は、「Ruby on Rails の ActiveRecord の insert_all
で MySQL向けに発行されるSQLに ON DUPLICATE KEY UPDATE foo = foo
が含まれている理由」について、知っておこう。(調べました)
TL;DR
- Ruby on Rails の
insert_all
で発行されるSQLにはON DUPLICATE KEY UPDATE foo = foo
が末尾に指定されている-
foo
はどうやら INSERT INTO で登録先として指定している列の1番目 -
upsert_all
にON DUPLICATE KEY UPDATE
が指定されているならまだしも、insert_all
にこの指定があるのは感覚的に気持ち悪い
-
- 該当の実装部分はここ
- 実装時のコミットはこれ
- https://github.com/rails/rails/commit/91ed21b304c468db8ce9fd830312c151432935d0
- 実装を見るとわかるが、
foo
は INSERT INTO で登録先として指定している列の1番目にしてる
- コミットメッセージにもあるように 「重複レコードをスキップ」するため にそうなっている
-
insert_all
最小引数で使用した場合、skip_duplicates?
は true になる -
insert_all!
を使えば重複が発生する場合にエラーにして全てのINSERTを失敗にすることもできる- 複数レコードを一括登録 | Railsドキュメント
insert_all
で MySQL向けに発行されるSQLに ON DUPLICATE KEY UPDATE foo = foo
が含まれている
Ruby on Rails の ActiveRecord の 例えば products
というテーブルがあったとして
class CreateProducts < ActiveRecord::Migration[7.0]
def change
create_table :products do |t|
t.string :name
t.text :description
t.timestamps
end
end
end
こんな感じで insert_all
すると
# Rails7からは自動でタイムスタンプが追加されるため、created_atやupdated_atは本来指定不要
# ここでは生成されるクエリとの対応づけをわかりやすくするためあえて 6.1 までの書き方で書いてます
now = Time.zone.now
products = [
{name: 'foo', description: 'foo_description', created_at: now, updated_at: now},
{name: 'bar', description: 'bar_description', created_at: now, updated_at: now},
{name: 'baz', description: 'baz_description', created_at: now, updated_at: now},
]
Product.insert_all(products)
MySQL へ発行されるSQLはこんな感じになる
-- 改行なしでSQLが発行されますが、ここでは読みやすくするために改行入れてます
INSERT INTO
`products` (`name`, `description`,`created_at`,`updated_at`)
VALUES
('foo', 'foo_description', '2023-07-01 00:00:00.000000', '2023-07-01 00:00:00.000000')
,('bar', 'bar_description', '2023-07-01 00:00:00.000000', '2023-07-01 00:00:00.000000')
,('baz', 'baz_description', '2023-07-01 00:00:00.000000', '2023-07-01 00:00:00.000000')
ON DUPLICATE KEY UPDATE `name`=`name`;
ON DUPLICATE KEY UPDATE ステートメントは 行を挿入した時に UNIQUE INDEX / PRIMARY KEY で値が重複する場合、行の挿入ではなく更新をするようにするSQLです
ON DUPLICATE KEY UPDATE 句を指定し、行を挿入すると、UNIQUE インデックスまたは PRIMARY KEY で値が重複する場合、古い行の UPDATE が発生します
- 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE ステートメント | MySQL 8.0 リファレンスマニュアル
ActiveRecord には upsert_all
というメソッドもあり、これを使うときに ON DUPLICATE KEY UPDATE
ステートメントが指定されるのはわかりますが、なぜ insert_all
の時にもこのステートメントが発行されているのでしょうか……?
ON DUPLICATE KEY UPDATE ステートメント
前述したように、MySQL の ON DUPLICATE KEY UPDATE
ステートメントは
行を挿入した時に UNIQUE INDEX / PRIMARY KEY で値が重複する場合、行の挿入ではなく更新をするようにするSQLです。
(話が脱線しますが、このような UPSERT を実現するためのSQLは各種RDBMSで構文が違ってて大変ですよね……)
通常はこのように使います
-- 例えば、name列に UNIQUE INDEX 制約があるときに
-- 1行の挿入の場合
INSERT INTO
`products` (`name`, `description`,`created_at`,`updated_at`)
VALUES
('foo', 'foo_description', '2023-07-01 00:00:00.000000', '2023-07-01 00:00:00.000000')
ON DUPLICATE KEY UPDATE `description`='foo_description';
-- 複数行を Bulk Insert する場合は VALUES(col_name) 関数が使える
-- 1行挿入の時に VALUES(col_name) 関数を使ってもOK
INSERT INTO
`products` (`name`, `description`,`created_at`,`updated_at`)
VALUES
('foo', 'foo_description', '2023-07-01 00:00:00.000000', '2023-07-01 00:00:00.000000')
,('bar', 'bar_description', '2023-07-01 00:00:00.000000', '2023-07-01 00:00:00.000000')
,('baz', 'baz_description', '2023-07-01 00:00:00.000000', '2023-07-01 00:00:00.000000')
ON DUPLICATE KEY UPDATE `description`=VALUES(`description`);
このように、INSERT INTO
で行を挿入する際に UNIQUE INDEX / PRIMARY KEY で値が重複する場合、行の挿入ではなく更新を行う、いわゆる UPSERT を実現するためのSQLです。
ここで、今回の insert_all
での ON DUPLICATE KEY UPDATE
をもう一度見てみましょう
-- 改行なしでSQLが発行されますが、ここでは読みやすくするために改行入れてます
INSERT INTO
`products` (`name`, `description`,`created_at`,`updated_at`)
VALUES
('foo', 'foo_description', '2023-07-01 00:00:00.000000', '2023-07-01 00:00:00.000000')
,('bar', 'bar_description', '2023-07-01 00:00:00.000000', '2023-07-01 00:00:00.000000')
,('baz', 'baz_description', '2023-07-01 00:00:00.000000', '2023-07-01 00:00:00.000000')
ON DUPLICATE KEY UPDATE `name`=`name`;
該当の部分を抜き出すと
ON DUPLICATE KEY UPDATE `name`=`name`;
です。ON DUPLICATE KEY UPDATE
ステートメントの使い方を踏まえると、
この指定は
「行が重複して挿入できない場合、既存の name カラムの値で name カラムの値を上書きする、
つまり 何も更新しない ということになります。
なぜ、そんな指定をしているのか……?
ActiveRecord の実装を読んでみる
ざっくりリファレンスをあさってみたのですが、それっぽい記述が見つからなかったので
こういう時は実装を読んでみましょう。OSS最高!
ActiveRecord に限らず、 Ruby on Rails は特にリファレンスには出してないメソッドの使い方やオプションがコード内のコメントに色々書く文化で開発している気がします。
調べるにあたって、 ActiveRecord のコードを GitHub上で検索したりして調べればいいんですが
今だと軽く ChatGPT にきいてみて足掛かりを得るのもいいですね。
ちょっと間違ってたりもするんですが、なんとなくの最初の足掛かりとしては助けになります。
で、読んでいたところ、該当の実装箇所を見つけました。
少し抜粋します
def build_insert_sql(insert) # :nodoc:
sql = +"INSERT #{insert.into} #{insert.values_list}"
if insert.skip_duplicates?
no_op_column = quote_column_name(insert.keys.first)
sql << " ON DUPLICATE KEY UPDATE #{no_op_column}=#{no_op_column}"
elsif insert.update_duplicates?
sql << " ON DUPLICATE KEY UPDATE "
if insert.raw_update_sql?
sql << insert.raw_update_sql
else
sql << insert.touch_model_timestamps_unless { |column| "#{column}<=>VALUES(#{column})" }
sql << insert.updatable_columns.map { |column| "#{column}=VALUES(#{column})" }.join(",")
end
end
sql
end
insert.skip_duplicates?
の時に
sql << " ON DUPLICATE KEY UPDATE #{no_op_column}=#{no_op_column}"
してますね!
no_op_column = quote_column_name(insert.keys.first)
なので
INSERT時に指定しているカラムの一番最初のカラムを #{no_op_column}=#{no_op_column}
するようです。
なぜこのような実装をしているのでしょう?
BLAMEするとコミットした時のコミットがわかるのでみてみましょう
Adds a method to ActiveRecord allowing records to be inserted in bulk without instantiating ActiveRecord models. This method supports options for handling uniqueness violations by skipping duplicate records or overwriting them in an UPSERT operation.
ActiveRecord already supports bulk-update and bulk-destroy actions that execute SQL UPDATE and DELETE commands directly. It also supports bulk-read actions through
pluck
. It makes sense for it also to support bulk-creation.
記事作成者訳(DeepL使用):
ActiveRecordモデルをインスタンス化せずにレコードを一括挿入できるメソッドをActiveRecordに追加します。このメソッドは、重複レコードをスキップしたり、UPSERT操作で上書きしたりすることで、一意性違反を処理するオプションをサポートしています。ActiveRecordは、SQLのUPDATEやDELETEコマンドを直接実行するbulk-updateやbulk-destroyアクションをすでにサポートしています。また、
pluck
による一括読み込みアクションもサポートしている。一括作成もサポートすることは理にかなっている。
「一意性違反を処理するオプションをサポートしています」と書いてありますね。
このコミットを含むプルリクエストがこれです
(全然関係ないですけど、DHHさんのガッツリコードレビューが入ってて面白いですね)
このプルリク上でのやり取りでも
I just pushed a commit that:
Extracts a command object
Splits the API into six methods
insert! / insert_all!
insert / insert_all (skip duplicates)
upsert / upsert_all
というような言及がされており、
insert_all
は重複をスキップする、 insert_all!
は重複があったあ場合エラーにする旨が書かれています。
もう少しコードを追いかけてみましょう。
insert.skip_duplicates?
はここで定義されています
module ActiveRecord
class InsertAll # :nodoc:
attr_reader :model, :connection, :inserts, :keys
attr_reader :on_duplicate, :update_only, :returning, :unique_by, :update_sql
def initialize(model, inserts, on_duplicate:, update_only: nil, returning: nil, unique_by: nil, record_timestamps: nil)
@model, @connection, @inserts = model, model.connection, inserts.map(&:stringify_keys)
@on_duplicate, @update_only, @returning, @unique_by = on_duplicate, update_only, returning, unique_by
@record_timestamps = record_timestamps.nil? ? model.record_timestamps : record_timestamps
# (記事作成者 中略)
def skip_duplicates?
on_duplicate == :skip
end
insert_all
メソッドと insert_all!
メソッドの実装はここです。
メソッドの挙動や実行時の動きの例についても多くのコメントが書かれており、読みごたえがありますね。
# (記事作成者 によりコメントとコードの一部を抜粋しています)
# Rows are considered to be unique by every unique index on the table. Any
# duplicate rows are skipped.
def insert_all(attributes, returning: nil, unique_by: nil, record_timestamps: nil)
InsertAll.new(self, attributes, on_duplicate: :skip, returning: returning, unique_by: unique_by, record_timestamps: record_timestamps).execute
end
# Raises ActiveRecord::RecordNotUnique if any rows violate a
# unique index on the table. In that case, no rows are inserted.
def insert_all!(attributes, returning: nil, record_timestamps: nil)
InsertAll.new(self, attributes, on_duplicate: :raise, returning: returning, record_timestamps: record_timestamps).execute
end
今回の調査でわかったこと
今回の記事の主旨である「なぜ insert_all
で ON DUPLICATE KEY UPDATE foo = foo
が発行されるのか?
については、 重複エラーが起こった時に重複行をエラーにせずskipするため にそういう実装になっている、ということですね。
追加でわかったこととしては、insert_all!
を使うことで重複エラーが起こった時に例外を発生することができる、ということです。
Ruby on Rails の ActiveRecord はメソッド名に !
をつけると
エラーがある時に例外を発生させ、!
をつけないときは例外を発生させない、という規約があるので
今回の実装もそれにのっとったものになっているということですね。
(例えば Foo.create!
と Foo.create
など)
わかってくれば、そりゃそうだな、という気持ちになります。
ここまで調べた上で改めてリファレンスをあさってみると、このことは書いてありました。
- 複数レコードを一括登録 | Railsドキュメント
- insert_all | api.rubyonrails.org
- insert_all! | api.rubyonrails.org
今回はここまでです。じゃ!
参考
- 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE ステートメント | MySQL 8.0 リファレンスマニュアル
- rails / rails | GitHub
- 複数レコードを一括登録 | Railsドキュメント
- insert_all | api.rubyonrails.org
- insert_all! | api.rubyonrails.org
- 週刊Railsウォッチ: insert_allやupsert_allのタイムスタンプ自動更新、app/contextsにロジックを置くほか(20211025前編)|TechRacho by BPS株式会社
- MySQLのINSERT ... ON DUPLICATE KEY UPDATEでレコードの挿入/更新を便利に実行 | Qiita
- Rails6〜7で追加された便利メソッド12選 | rince | zenn
Discussion