Zenn
😳

ActiveRecord の insert_all で発行されるSQLに ON DUPLICATE KEY UPDATE がある理由

2023/07/01に公開
MySQL
Rails
Ruby
Rails
#
ActiveRecord
tech

タイトルに書きたいことが長いとタイトル付けに困りますね。

今日は、「Ruby on Rails の ActiveRecord の insert_all で MySQL向けに発行されるSQLに ON DUPLICATE KEY UPDATE foo = foo が含まれている理由」について、知っておこう。（調べました）

TL;DR

Ruby on Rails の ActiveRecord の insert_all で MySQL向けに発行されるSQLに ON DUPLICATE KEY UPDATE foo = foo が含まれている

例えば　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 が発生します

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 にきいてみて足掛かりを得るのもいいですね。

ちょっと間違ってたりもするんですが、なんとなくの最初の足掛かりとしては助けになります。

で、読んでいたところ、該当の実装箇所を見つけました。

https://github.com/rails/rails/blob/5ed37b35d666b833aeccb14a4cacd2926251232d/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb#L621
少し抜粋します

rails/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
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するとコミットした時のコミットがわかるのでみてみましょう

https://github.com/rails/rails/commit/91ed21b304c468db8ce9fd830312c151432935d0

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さんのガッツリコードレビューが入ってて面白いですね）
https://github.com/rails/rails/pull/35077

このプルリク上でのやり取りでも

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? はここで定義されています

https://github.com/rails/rails/blob/5ed37b35d666b833aeccb14a4cacd2926251232d/activerecord/lib/active_record/insert_all.rb#L61C9-L61C24

rails/activerecord/lib/active_record/insert_all.rb
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! メソッドの実装はここです。
メソッドの挙動や実行時の動きの例についても多くのコメントが書かれており、読みごたえがありますね。

https://github.com/rails/rails/blob/5ed37b35d666b833aeccb14a4cacd2926251232d/activerecord/lib/active_record/persistence.rb#L175

activerecord/lib/active_record/persistence.rb
# (記事作成者 によりコメントとコードの一部を抜粋しています)
# 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 など）
わかってくれば、そりゃそうだな、という気持ちになります。

ここまで調べた上で改めてリファレンスをあさってみると、このことは書いてありました。

今回はここまでです。じゃ！

参考

Discussion