Bulk……なんか呪文みたい。それは「バルス」か……。
bulkとは、英語で”大量”を意味する単語だそうです。
余談はさておき、MySQLを用いたデータの更新方法についてサクッと紹介していきます。
以下のようなuserテーブルがあると考え、address(住所)を変更するケースを想定しましょう。
mysql> SELECT * FROM users;
+----+--------+---------+-----+
| id | name | address | age |
+----+--------+---------+-----+
| 1 | 山田 | 東京 | 20 |
| 2 | 岩本 | 埼玉 | 52 |
| 3 | 鈴木 | 東京 | 46 |
| 4 | 佐藤 | 東京 | 31 |
| 5 | 金城 | 沖縄 | 22 |
| 6 | 田中 | 神奈川 | 35 |
+----+--------+---------+-----+
通常のUPDATE
UPDATE テーブル名 SET 変更したいカラム = ’変更後の値’ WHERE 条件;
例) usersテーブルのidを指定して、addressの値を変更
UPDATE users SET address = '大阪' WHERE id = 2;
UPDATE users SET address = '愛知' WHERE id = 5;
UPDATE users SET address = '北海道' WHERE id = 6;
例では岩本さんの住所が埼玉から大阪に、金城さんは愛知に、田中さんは北海道に変更されます。
1レコードのみ、または数レコードの変更であればこの方法でも良いかもしれません。
しかしこの方法における欠点は1レコード更新する度に1クエリ走ってしまうことです。
更新するレコードが増えるのと比例して、通信が発生するため、
1の方法で数万行更新となれば処理時間がかなりかかることになります。
そこで複数レコードを更新する場合にオススメなのが、まさにBulk Updateです。
Bulk Update（バルクアップデート）
UPDATE テーブル名
SET 変更したいカラム = ELT (FIELD (str, str1, str2, str3), ‘変更後の値1’, ‘値2’, ‘値3’)
WHERE 条件で指定するカラム IN (条件で指定する値1, 値2, 値3);
UPDATE users
SET address = ELT(FIELD(id,2,5,6), '大阪', '愛知', '北海道')
WHERE id IN (2,5,6);
1のUPDATE文をBulk Updateで表現するとこのようになります。
ここで使用されているELT関数とFIELD関数は以下で解説します。
ELT/FIELD関数ともに文字列関数です。
他にも文字列関数は多数存在するので興味あればMySQLのドキュメントを確認してみてください。
a. ELT関数
ELT (n, str1, str2, str3….)
文字列リストstr1, str2, str3,….のn番目の要素を返す関数です。
※バルクアップデートの場合は、nの箇所にFIELD関数が使用されている
SELECT ELT(1, '青', '赤', '紫', '白', '黒')
-> '青'
SELECT ELT(3, '青', '赤', '紫', '白', '黒')
-> '紫'
b. FIELD関数
FIELD (str, str1, str2, str3….)
文字列リストstr1, str2, str3,….からstrを探し、インデックス(位置)を返す関数です。
※strがリスト内に存在しない場合は、0を返す
SELECT ELT('紫', '青', '赤', '紫', '白', '黒')
-> 3
SELECT ELT('黒', '青', '赤', '紫', '白', '黒')
-> 5
ELT関数とFIELD関数はなんとなくわかったけど、それでもバルクアップデート文を見るとどこに何を入れたら良いか困惑する人もいるかもしれません。
そんな時はクエリの中身がどの順番で処理されているのか整理してみましょう。
UPDATE users
SET address = ELT(FIELD(id,2,5,6), '大阪', '愛知', '北海道')
WHERE id IN (2,5,6);【再掲】上記で示した例と全く同じクエリです
【処理の順番】
1. where句
id=2のユーザーを条件として設定
2. FIELD関数内
id=2をリストから検索し、1を返す（id=2はリスト内で1番目の位置）
3. ELT関数内
リストから1番目の値を検索し、”大阪”を返す
よって、UPDATE users SET address = ‘大阪’ where id =2 と同一とわかります。
これがid=5,6と1回のクエリで複数レコードが処理されるので、処理速度がはやいんですね～。
（参考）更新後のテーブルはこうなります。
mysql> SELECT * FROM users;
+----+--------+---------+-----+
| id | name | address | age |
+----+--------+---------+-----+
| 1 | 山田 | 東京 | 20 |
| 2 | 岩本 | 大阪 | 52 |
| 3 | 鈴木 | 東京 | 46 |
| 4 | 佐藤 | 東京 | 31 |
| 5 | 金城 | 愛知 | 22 |
| 6 | 田中 | 北海道 | 35 |
+----+--------+---------+-----+
