Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last 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

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

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

gyarasu
persol
PERSOL(パーソル)グループは、人材派遣、人材紹介、求人広告をはじめとし、ITアウトソーシング、設計開発にいたるまで、国内外70社を超える幅広い企業群で構成されています。
https://www.persol-group.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away