Edited at

MySQLでBulk Update(一括更新)する方法

意外と知らなかった良い方法があったので記録。


INSERT ... ON DUPLICATE KEY UPDATE 構文:thinking:

https://dev.mysql.com/doc/refman/5.6/ja/insert-on-duplicate.html

これはよく知られてる方法だと思います。

かんたんに言えば「INSERTを試みて、primary keyの重複やunique制約で引っかかった場合にはUPDATEする」構文です。


一括UPDATEする際の問題点


  • 更新対象のテーブルにNOT NULLかつDEFAULT VALUEが設定されていないカラムがあった場合に、それらをすべて指定してあげる必要がある。

  • UPDATEとして扱われるSQLについても、内部的な動作はDELETEしてからINSERTしているので、初期値が必要なカラムは値をしていして挙げないといけない

  • WHERE句使えない(多分)


回避方法


  • 特に困ることがなければNOT NULLカラムにはDEFAULT VALUEを定義してあげて、できるだけSQL側で色々指定しなくて良いようにする

  • 一回SELECTして現状のレコードを引っ張ってきた後に、それを使って新しくINSERTするためのレコードを生成してSQLに指定する

とはいえUPDATE前提の用途だとやっぱ合わない気がする。あくまでもINSERTするユースケースが前提になる。


UPDATE・ELT・FIELDの組み合わせ:relaxed:

INSERT ... ON DUPLICATE KEY UPDATEで一括更新やろうとしててなんかイケてないなーと思い調べてみて最近始めて知った方法。


テーブルの例

例えばユーザを管理するテーブルを考える。

id
username
group_id

234
hoge
100

235
fuga
100

234
foo
200

235
bar
200

238
username
100


  • id: ユーザのid

  • username: ユーザ名

  • group_id: 所属組織のid

  • idとgroup_idの組み合わせがユニーク


やりたいこと

group_id = 100の組織に属する2人のユーザ名を下記のように変更したい。

- hoge→hogehoge

- fuga→fugafuga


SQL文

こんな感じでシンプルな形で書ける。

 UPDATE users

SET name = ELT(FIELD(id, 234, 235), 'hogehoge', 'fugafuga')
WHERE id IN (1000, 1001) AND group_id = 100

とあるグループの中で一意のIDなどで絞り込みを加えた上で一括UPDATEする場合はとても便利。


ELT

https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_elt


ELT() は、文字列リストの N 番目の要素を返します。N = 1 の場合は str1、N = 2 の場合は str2 のように返します。N が 1 よりも小さいか、引数の数よりも大きい場合は、NULL を返します。ELT() は FIELD() の補数です。



FIELD

https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_field


str1、str2、str3、... リスト内で str のインデックス (位置) を返します。str が見つからない場合は、0 を返します。

FIELD() へのすべての引数が文字列の場合は、すべての引数が文字列として比較されます。すべての引数が数値の場合は、数字として比較されます。それ以外の場合は、引数が倍精度として比較されます。

NULL ではどの値との等価比較にも失敗するため、str が NULL である場合は、戻り値が 0 になります。FIELD() は ELT() の補数です。



ELT(FIELD(id, 234, 235), 'hogehoge', 'fugafuga')

まず、FIELD(id, 234, 235)では、id = 234のときは1が返ってくるので

ELT(1, 'hogehoge', 'fugafuga) となります。

ELT(1, 'hogehoge', 'fugafuga)hogehogeと展開されます。

よって、SQL全体としてはid=234に対しては

 UPDATE users

SET name = 'hogehoge'
WHERE id = 1000 AND group_id = 100

このように展開されていると解釈できます。

今後も使う場面多そうです。