背景
最近、データ更新クエリを書く機会があり、MySQLならではことなどを学びました。かれこれ6年ほどSQLを書いて居ますが、これからはじめて更新クエリを書く人たちに一読してもらいたい内容をまとめてみました。
環境
MySQL 5.7
デモデータとして次のようなテーブルを想定しています。
Classテーブル
id, name
1, A
2, B
Studentテーブル
id, name, pointa_a, point_b
1, Ken, 10, 15
2, Ryu, 20, 25
3, Darusimu, 30, 35
PowerUpテーブル
id, up_point,
1, 100
2, 200
3, 300
更新クエリの書き方
1. 定数または同じテーブルの列の値を更新する
一番シンプルな形です。
UPDATE [テーブル名]
SET [列名] = [更新したい値]
UPDATE Student
SET point_a = 40
-- -> 全行40ポイントに更新されます
-- 一度に複数の列を更新することもできます
UPDATE Student
SET point_a = 40, point_b = 90
-- 特定の行に対して更新したい場合はWHERE句を使います
UPDATE Student
SET point_a = 99
WHERE id = 1
-- -> id = 1のみ99ポイントに更新されます
同じテーブルの列を指定することもできます。
※ 更新元、更新先の列の型には注意が必要です
UPDATE Student
SET point_a = ponint_b
-- ->
Studentテーブル
id, name, pointa_a, point_b
1, Ken, 15, 15
2, Ryu, 25, 25
3, Darusimu, 35, 35
2. 違うテーブルの値を更新する（JOIN）
他のテーブルの値を使って更新したい際はJOINを使います。
「,」はMySQLでは「INNER JOIN」と同じになります。ただしON句を指定してないので全行同士がJOINした状態になります。
-- WHERE句でidが同じものを指定して更新します
UPDATE Student, PowerUp
SET point_a = up_point
WHERE Student.id = PowerUp.id
-- 同じ結果になります
UPDATE Student INNER JOIN PowerUp ON Student.id = PowerUp.id
SET point_a = up_point
3. 集計結果などを更新する（サブクエリ）
テーブルの値を集計結果した結果などを更新したい場合はサブクエリが使えます。サブクエリにはエイリアス（AS）を付ける必要があります。
-- point_aの平均値を全行更新する
UPDATE Student, (SELECT AVG(point_a) avg_point FROM Student) AS SubTable
SET point_a = avg_point
-- -> 全行平均値の25に更新されます
これらを組み合わせることで、だいたいのデータ更新は行えると思います。注意する点としては、JOINを使用して複数のテーブルを扱う際にはON句またはWHERE句で更新の対象、条件を満たしている行をちゃんと絞り込むことです。絞り込みが足りなければ、意図しない値での更新、対象外の行への更新が行われる可能性があるので気をつけます。
組織で開発する際に気をつけていること
組織で開発している際に、お作法的な手順を踏むことで事故を防いだり、他の人からも利用しやすくしておくために気をつけていることです。
1. begin~rollbackで挟む
データ更新のクエリはまっさらの状態から、まず「begin;」と「rollback;」を書くようにしています。更新クエリを書いている途中で誤って実行しても大丈夫ですし、最終確認が終わってから「rollback」を「commit」に書き換えること手順を踏むことで、更新結果の確認漏れなどが無いようにする目的です。
begin;
-- 更新するクエリを書くところ
rollback;
2. 結果確認クエリをBeforeAfterで挟んで書く
begin~rollbackの中に更新前後の確認をするクエリを書いておき、更新が正確に行われたか確認します。同じ確認クエリにしておくことで、データ更新がの影響だけでデータが変更されたのか確認しやすくなります。
begin;
-- 更新前の値を確認する
select * from piyo where id IN (...);
-- 更新するクエリを書くところ
-- 更新後の値を確認する
select * from piyo where id IN (...);
rollback;
3. 対象行数を把握する
更新の対象となる行を把握しましょう。IDで指定できればIDで指定するのが良いですし、時間経過やINSERTなどによって更新の対象行数が増減するような条件にしておくのは、正確に更新が行われたのかわからないのであまり良くないです。たとえ1行でも想定していた行数と違う場合は考慮漏れなどがあるはずです、何が原因か突き止めましょう。
4. ステートメントごとにコメントで何をしているのかわかりやすく書く
実行したクエリを今後残すものであれば、今後クエリを見る人がわかりやすいようにコメントを書いておきます。
ヘッダ部には簡単なタイトル、なんの開発の際に発生したものなのか（作成者や作成日時をいれておくと問い合わせなどするときに便利です）
各ステートメントにはその簡単な目的と結果セットで確認すべき点などを書いておきます。
-- piyoテーブルidが100より小さいレコードのsizeを一律「L」に更新するQuery
-- 〜〜〜の仕様変更、piyoたちが小さくなってきたので、これまでのpiyoたちをすべてLとして扱う
begin;
-- 更新前のsizeを確認する（バラバラな状態）
-- 対象は90行（9行は欠番）
select size from piyo where id < 100;
-- id が100より小さいレコードを’L’で更新する
-- 対象は90行（9行は欠番）
UPDATE piyo SET size = 'L' where id < 100;
-- 更新前のsizeを確認する（すべてLになっている）
-- 対象は90行（9行は欠番）
select * from piyo where id < 100;
-- 確認できたらcommitにする
rollback;
-- commit;
ひとこと
組織で開発していくときには、これら当たり前のことがなされていると事故を防げたり、連携がスムーズに行くかと思います。なにか発見があれば幸いです。