Generated ColumnのUniqueIndexのSTOREDはNULLはダメみたい

はじめに

今回業務上でユニークなインデックス追加をしてデータを規制したいなと思った際にGenerated Columnという便利なものが存在するので使ってみた時の備忘録

Generated ColumnとかVIRTUALとSTOREDについて

Generated Columnはその名の通り「生成値」、すなわち他のカラム同士とかで条件をつけた結果を格納したり、計算結果を入れたりなどできる。結果に対してindexも貼れちゃう便利なやつ。

そんなGenerated ColumnにはVIRTUALとSTORE があります。
ざっくり説明すると、

• VIRTUAL
  • 名前の通り実態をDBのストレージに保存することはないので、ストレージを圧迫せずに使用可能
  • ただ逆を言えば頻繁にアクセスされるテーブルだと毎回算出されるのであまりパフォーマンスがよくない
  • 何も指定しないとデフォルトでこれらしい
• STORE
  • VIRTUALと違いDBのストレージに実際に保存される
  • 頻繁にアクセスされるならばこっちだと永続化されいているのでパフォーマンスが良い

説明はこんなところで詳細はドキュメントなどをみてください。
https://dev.mysql.com/doc/refman/8.0/ja/create-table-generated-columns.html

UniqueIndexでのSTOREDはNULLはダメみたい

本題ですが、今回Generated Columnを使ってUniqIndexの作成しました。

以下のようなindexを作成しようとしましたがダメでした。

-- これはNG

ADD COLUMN unique_idx VARCHAR(255) GENERATED ALWAYS AS (
    CASE
    WHEN user_id IS NOT NULL AND group_id IS NOT NULL
    THEN CONCAT(user_id, '_', group_id)
    ELSE NULL
    END
) STORED UNIQUE;

やろうとしていることは、user_idとgroup_idをconcatで合体させたものをカラムに保存して、どちらかのカラムがない時はNULLにするという条件で生成、それに対してUniqueIndexを貼ろうという感じです。

なんでエラーになったのかを説明すると、この状態だとNULLがUniqueにならないためSTOREDの中にはどうやらNULLが入れられないらしいです。普通のカラムはそんなことないはずなので注意が必要です。
ちなみにVIRTUALだとこのパターンでもUniqueIndexを貼れるようでした。

-- これはOK

ADD COLUMN unique_idx VARCHAR(255) GENERATED ALWAYS AS (
    CASE
    WHEN user_id IS NOT NULL AND group_id IS NOT NULL
    THEN CONCAT(user_id, '_', group_id)
    ELSE NULL
    END
) VIRTUAL UNIQUE;

auto_incrementを含めてしまうのもダメみたい

次にNULLがダメなら、Uniqueにしてあるしidにしちゃえば被らないなと思い以下にしました。

-- これはNG

ADD COLUMN unique_idx VARCHAR(255) GENERATED ALWAYS AS (
    CASE
    WHEN user_id IS NOT NULL AND group_id IS NOT NULL
    THEN CONCAT(user_id, '_', group_id)
    ELSE id
    END
) STORED UNIQUE;

これもダメなようでした。どうやらGenerated Columnの生成タイミングの問題絡みでauto_incrementのカラムは採用できないらしいです。auto_incrementの値はレコードが挿入される直前に生成されて、Generated Columnの値はそれよりも前に決定される必要があるかららしいですね。

ちなみにSequelizeのモデル定義ではどう記述する？

普段の業務でSequelizeを使用しているので、どう記述するのかも調査しました。
結論、モデル定義ではGenerated Columnは定義できません。
ただし、DB上で作成したGenerated ColumnはSequelizeのモデル定義でカラムとして定義すれば取ってこれるらしいです。
なので、データベースマイグレーションのロールバックや作り直しの時はSequelizeだけで走りきると、DBに存在しているカラムが足りないことになるので注意が必要です（しっかりコメントアウトでメモを残しておくと良いかもしれません）

const User = sequelize.define('User', {
  firstName: {
    type: DataTypes.STRING,
    field: 'first_name'
  },
  lastName: {
    type: DataTypes.STRING,
    field: 'last_name'
  },
  fullName: {
    type: DataTypes.STRING,
    field: 'full_name' // こいつがGenerated Column
  }
}, {});

終わりに

Generated Columnを今回初めて知って触ったのですがかなりできることがありそうですので、indexを作成する際の選択肢として頭の片隅に置いておきたいです。