意外と知らなかった良い方法があったので記録。
INSERT ... ON DUPLICATE KEY UPDATE 構文
これはよく知られてる方法だと思います。
かんたんに言えば「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の組み合わせ
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
ELT() は、文字列リストの N 番目の要素を返します。N = 1 の場合は str1、N = 2 の場合は str2 のように返します。N が 1 よりも小さいか、引数の数よりも大きい場合は、NULL を返します。ELT() は FIELD() の補数です。
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
このように展開されていると解釈できます。
今後も使う場面多そうです。