MySQLのLPADで0埋めするUPDATE、再実行されても安全な条件にする

  • MySQL
  • SQL

この記事は、構成と文章の整理にAIを使っています。
あくまで読みやすくするための補助で、内容そのものをAIにすべて任せて書いているわけではありませんが、ところどころ相談しながら整えています。

何が起きたか

id を元に id-0000000042-jp みたいな文字列を作って ref_code カラムに突っ込むUPDATEを書いてた。

UPDATE users SET ref_code = CONCAT(CONCAT('id-', LPAD(id, 10, '0')), '-jp');

動くには動くけど、これは既存レコード全部に付与する想定で書いたもの。つまりWHERE句が無く全行が対象になる。今回はそれが意図通りだったけど、途中で失敗して再実行するケースを考えると、このままでは危ない。

ネストしていた CONCAT(CONCAT(...), ...) も、CONCAT は可変長引数を取れるので1回にまとめられる。

冪等性を持たせる

全行に一括で同じ変換をかけるUPDATEは、途中で失敗して再実行される可能性がある。再実行しても二重に加工されないように、「まだフォーマット済みでない行だけ」を対象にするWHERE句を足した。

UPDATE users
SET ref_code = CONCAT('id-', LPAD(id, 10, '0'), '-jp')
WHERE ref_code NOT REGEXP '^id-[0-9]{10}-jp$';

これで、既に id-0000000042-jp の形式になっている行はWHEREで弾かれる。新規追加されたレコードだけ後から流し込む、という運用もできる。

注意点

  • 元データに id-...-jp に近い形だけど桁数が違うゴミが混ざっていると誤判定する。事前に SELECT COUNT(*) で対象件数を確認してから流した方がいい。
  • ref_code にインデックスが無い場合、正規表現条件を付けてもフルスキャンになる。速度がシビアなら LENGTH(ref_code) <> 16 のような単純な長さチェックの方が軽い（id- 3文字 + 10桁 + -jp 3文字 = 16文字）。
  • LPAD(id, 10, '0') は id が数値型でも暗黙的に文字列変換されるので、桁数指定通りに0埋めされる。ここは特に問題なし。

結論

一括UPDATEを書くときは、全件対象なら「全件対象である」ことを自覚した上で書く。再実行される可能性があるなら、変換済みかどうかを判定する条件をWHEREに入れておくと安全。