Typeorm + MySQL(MariaDB) で ON DUPLICATE KEY UPDATE

2020/02/07 16:59:332020/06/07 16:37:46


TypeORM で bulkInsert しつつ ON DUPLICATE KEY UPDATE (upsert) したいときありますよね。
あります。
検索しても様々な情報が出てくる上(閉じられていない Issue, 閉じられているが解決してない Issue)ほとんどダメで、最終的にたどり着いた方法でやっと解決したので書き残しておきたいと思います。

重複する.ts
const data = new User({ id: 1, name: 'a' }) 
 
await getConnection() 
            .createQueryBuilder() 
            .insert() 
            .into(User) 
            .values([data]) 
            .execute()
当然(プライマリ定義によりますが)このコードを2回実行するとデータが重複し、ER_DUP_ENTRY: Duplicate entry '1' for key 'PRIMARY' となります。
今回は一件のみの挿入なので、挿入前に普通に find し、結果によって UPDATE クエリを行えばいいかもしれません。しかしこういったデータを大量に挿入する必要がある場合、途中で被りのデータが発生しても普通にアップデートして続行して欲しい時があります。
検索すると onConflict を使うバージョンが大量に出てきますが、これは Postgres のみのオプションのようで、MySQL 系だと普通に無視されてしまいます
  • d.tsAdds additional ON CONFLICT statement supported in postgres. と書いてあります
そこで、今回は orUpdate を用いる方法を紹介します。

重複しない.ts
const data = new User({ id: 1, name: 'a' }) 
 
await getConnection() 
            .createQueryBuilder() 
            .insert() 
            .orUpdate({ conflict_target: ["id"], overwrite: ["name"] }) 
            .into(User) 
            .values([data]) 
            .execute()
こうすると、conflict_target が重複した時(または無指定でも DUP したとき) overwrite に指定された string[] の中身に従って上書きを行います。
発行されるクエリはこの通り。

orUpdate.sql
INSERT INTO `users`(`id`, `name`) VALUES (?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name)
内部的にはちゃんと ON DUPLICATE KEY UPDATE が実施されていることがわかります。
これだけだと中身スカスカすぎるので orUpdate の他のオプションについても記述します。

orUpdate のオプション
  • conflict_target
  • 重複する可能性のあるカラム?
  • プライマリキー?
  • 無指定でも動くしクエリに影響していないように見える
  • overwrite
  • 上書きするカラム?これがないと orUpdate そのものが無視されてしまう
  • クエリに ON DUPLICATE KEY UPDATE が追加されていない
  • columns
  • わからん
  • 分かったら Twitter で教えてください

自信がないなら記述をやめたほうが良かったかもしれません。適当に使ってみてください
余談2いきます

orIgnore について
d.ts みてた時に気づいたんですけど orIgnore というのもありますね。
  • これは string | boolean しか受け付けてなさそう。
  • DO NOTHING っぽい動きをしそうです

orIgnore.sql
INSERT  IGNORE INTO `users`(`id`, `name`) VALUES (?, ?)
INSERT IGNORE 構文なんてあるんだ…

おわり


著者の画像

ci7lus

@ci7lus

Caramelize - Made withCaramelizeand / Privacy