Rails 6.0からupsert_allという機能が追加されました。
(普段、MySQLを多用しているので、以降はMySQLを使っている前提で書いています)
upsert_allを使うと『INSERT ... ON DUPLICATE KEY UPDATE ステートメント』を発行してくれます。
ユニーキーが重複するレコードがある場合はUpdate、重複しない場合はInsertしてくれるので一括でデータ登録したいときに便利です。
実際に使ってみる
最初にリンクを張ったドキュメントを見ると下記のパラメーターが指定できることがわかります。
upsert_all(attributes, on_duplicate: :update, returning: nil, unique_by: nil)
- attributes
- hashの配列でUpsertするデータを指定します。
- on_duplicate
- defaultで
:updateが指定されているようです。これにより発行されるSQLにON DUPLICATE KEY UPDATEが指定されます。
- defaultで
- returning, unique_by
- MySQLでは使えないようです。
なお、この記事で動作確認するときは下記のバージョンで実施しています。
- Ruby: 3.0.2
- Rails: 6.1.4
- MySQL: 8.0.23
実行
下記のusersテーブルで実行します。emailをユニークキーにしています。
mysql> desc users;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| email | varchar(255) | NO | UNI | NULL | |
| locale | int | NO | | 0 | |
| created_at | datetime(6) | NO | | NULL | |
| updated_at | datetime(6) | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+
早速実行してみます。
最初はusersテーブルにデータが入っていない状態で実行します。
irb(main):003:0> User.all.count
(1.6ms) SELECT COUNT(*) FROM `users`
=> 0
irb(main):006:0> attributes = [{ name: 'name1', email: 'name1@example.com', created_at: Time.now, updated_at: Time.now }]
=> [{:name=>"name1", :email=>"name1@example.com", :created_at=>2021-07-16 07:50:24.4640805 +0000, :updated_at=>2021-07-16 07:50:24.4640968 +0000}]
irb(main):007:0> User.upsert_all(attributes)
User Upsert (4.7ms) INSERT INTO `users` (`name`,`email`,`created_at`,`updated_at`) VALUES ('name1', 'name1@example.com', '2021-07-16 07:50:24.464080', '2021-07-16 07:50:24.464096') ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`email`=VALUES(`email`),`created_at`=VALUES(`created_at`),`updated_at`=VALUES(`updated_at`)
=> #<ActiveRecord::Result:0x000055c30a4d0ad0 @column_types={}, @columns=[], @hash_rows=nil, @rows=[]>
irb(main):008:0> User.all.count
(2.3ms) SELECT COUNT(*) FROM `users`
=> 1
irb(main):009:0> User.first
User Load (0.8ms) SELECT `users`.* FROM `users` ORDER BY `users`.`id` ASC LIMIT 1
=>
<User:0x000055c3083612c8
id: 1,
name: "name1",
email: "name1@example.com",
locale: "en",
created_at: Fri, 16 Jul 2021 07:50:24.464080000 UTC +00:00,
updated_at: Fri, 16 Jul 2021 07:50:24.464096000 UTC +00:00>
上記の通り、name1@example.comのレコードがInsertされました。
次に、ユニークキーであるemailが重複するデータを含んだ状態で実行します。
irb(main):015:0> attributes = [{ name: 'name11', email: 'name1@example.com', created_at: Time.now, updated_at: Time.now }, { name: 'name2', email: 'name2@example.com', created_at: Time.now, updated_at: Time.now }]
=>
[{:name=>"name11", :email=>"name1@example.com", :created_at=>2021-07-16 08:00:08.7574342 +0000, :updated_at=>2021-07-16 08:00:08.757445 +0000},
...
irb(main):017:0> User.upsert_all(attributes)
User Bulk Upsert (5.5ms) INSERT INTO `users` (`name`,`email`,`created_at`,`updated_at`) VALUES ('name11', 'name1@example.com', '2021-07-16 08:00:08.757434', '2021-07-16 08:00:08.757445'), ('name2', 'name2@example.com', '2021-07-16 08:00:08.757457', '2021-07-16 08:00:08.757468') ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`email`=VALUES(`email`),`created_at`=VALUES(`created_at`),`updated_at`=VALUES(`updated_at`)
=> #<ActiveRecord::Result:0x000055c30a5ebd98 @column_types={}, @columns=[], @hash_rows=nil, @rows=[]>
irb(main):018:0> User.all
User Load (0.7ms) SELECT `users`.* FROM `users`
=>
[#<User:0x000055c309542cf8
id: 1,
name: "name11",
email: "name1@example.com",
locale: "en",
created_at: Fri, 16 Jul 2021 08:00:08.757434000 UTC +00:00,
updated_at: Fri, 16 Jul 2021 08:00:08.757445000 UTC +00:00>,
<User:0x000055c309542500
id: 3,
name: "name2",
email: "name2@example.com",
locale: "en",
created_at: Fri, 16 Jul 2021 08:00:08.757457000 UTC +00:00,
updated_at: Fri, 16 Jul 2021 08:00:08.757468000 UTC +00:00>]
上記の通り、emailが重複しているname1@example.comはUpdateされて、name2@example.comはInsertされます。
問題点
一見すると問題なく動作しているように見えますが、Update時もcreated_atが更新されています。
created_atの場合、プログラム上で使わないからUpdateされても実害はないという話はあるかもしれませんが、できれば更新したくないですよね。
また、今回問題になったのはcreated_atでしたが、更新するカラムを指定したい状況は結構あると思います。
現状、発行されるSQLのON DUPLICATE KEY UPDATEにcreated_atが含まれていのるためUpdate時に更新されてしまいます。
`name`=VALUES(`name`),`email`=VALUES(`email`),`created_at`=VALUES(`created_at`),`updated_at`=VALUES(`updated_at`)`
上記はattributesで指定したhashから自動生成されているようです。
created_atを除くためにattributesから除いてしまうと、Insert時にcreated_atが未指定になりエラーになります。
そこでON DUPLICATE KEY UPDATE以降のカラムを指定できないか調査してみました。
調査結果
調査ではrailsのコードを見てみました。
SQLを作っている場所を探したところ下記で作っているようでした。
コードを抜粋すると下記の通り。
指定したattiributesから、機械的に#{column}=VALUES(#{column})としており、指定するのは難しそうでした。
elsif insert.update_duplicates?
sql << " ON DUPLICATE KEY UPDATE "
sql << insert.touch_model_timestamps_unless { |column| "#{column}<=>VALUES(#{column})" }
sql << insert.updatable_columns.map { |column| "#{column}=VALUES(#{column})" }.join(",")
end
しかし、上記はRails6.1.4のコードでしたが、2021年7月時点のmainブランチを見ると当該コードが下記のようになっていました。
raw_update_sqlという名前的に、生のクエリーを指定できそうだぞ！！！
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
この修正が入ったプルリクを見つけました。
プルリクの内容を読む限りでは、この記事に書いているような用途を想定して実装されたようではないようですが、これが使えるようになれば更新対象のカラムをon_duplicateオプションに指定できるようになりそうです！
執筆時点でmainブランチにマージされているので、次のバージョンでは使えるようになるのではないでしょうか。楽しみです！
おまけ
結局、執筆時点でリリースされているバージョンではupsert_allを使って更新カラムを指定することはできなそうでした。
現時点でこの記事の課題を解決したい場合は下記のgemを使えば可能です。
(私の感覚ですが)上記のgemは、Railsに実装されているinsert_allやupsert_allより使い勝手が良いので、特にこだわりがなければこのgemを使っていれば間違いないと思います。
ActiveRecordのオブジェクトをそのまま渡せるので使いやすいし、created_atやupdated_atを明示的に指定する必要もありません。
ただ、Railsに入っている機能で実現できるなら、余計にgemを追加したくないという気持ちもあるので、初手ではinsert_allやupsert_allを使うようにしています。