💻
MySQL Generated Columns を活用したユニークキー制約
今回は、MySQL での Generated Column の活用についての紹介です。
Generated Column とは？
カラム定義時にロジックを組んでおくことで、演算や条件分岐ロジックの結果を値として、仮想的に参照可能にするもしくは記憶領域に格納することが出来る機能です。
ドキュメントには以下のような直角三角形の斜辺 を格納するスキーマの例が紹介されています。
以降にカラムの値計算を行う式が定義されています。
ユースケース
よく紹介されている例としては、以下のようなものがあります。
- 複雑な条件結果を先にカラムに定義しておき、クエリ条件を簡略化させる
- を指定して記憶領域に書き込みを行っておき、クエリ参照時には計算コストをかけない
- 型のようなインデックスキーをつけれないカラムに対して、インデックスキー用のカラムを生成する
との組み合わせの例としては、以下のようなものです。
json のキーとして を持つデータに対して、インデックスキーカラムを設定できました。
アプリケーションでのデータ整合性
アプリケーションが読み書きするデータの整合性は、DB側でも外部キー、ユニークキーやCHECK制約などを用いて担保したいものです。
今回紹介したいのは、ユニークキーとして Generated Columns を活用する例です。
例1. 状態やステータスで有効なユニークキーを表現する
レコードの物理削除を行いたくない場合に、 カラムを用いることがあるかと思います。 名称 カラムに対してユニークキーを設定したいとします。
まずは、と（nullableなカラム）の複合ユニークキーを設定した良くない例は下記です。
NULLとなっているカラム値に対しユニークキーは有効でないため、重複名が登録できてしまいます。
このようなケースでは、 が NULL であるかをフラグとして持つ Generated Columns を定義し、ユニークキーとして使うと有効です。
例2. ユニークキーの条件がレコードにより異なる
更にこみいったビジネスロジックにおけるデータ整合性を担保する場合を考えてみます。
条件
- 各店舗の商品を持つテーブル
- 全店舗で商品名 は必ず持つが、商品コード は店舗により未設定の場合がある
- 商品コード をもつ場合、 がユニークキーとなる
- 商品コード をもたない場合、商品名 がユニークキーとなる
これをスキーマとして持つテーブルは以下のように定義できます。
商品コードを設定していない店舗１で、重複した商品名は以下のようにユニークキー制約でエラーになります。
商品コードを設定している店舗２では、重複した商品名も登録可能になります！
おわりに
Generated Columns の活用例をいくつか紹介してみました。 INSERT/UPDATE 時にはカラムにセットする値をアプリケーション側から無視する必要があったり（もしくは DEFAULT を使う）、考えることはあります。しかし、有効なケースも多々あると思いますので参考になりましたら幸いです！