MySQLのGenerated Columns初めて知った。

はじめに

こんばんは。

Generated Columns知ってますか？

僕は最近お仕事で初めて知りました。

dev.mysql.com

特定のロジックに従って算出された値をカラムのように扱えるようになるみたいです。

本題

例えば 論理削除が採用されている会員テーブルで、emailカラムにユニーク制約をつけたいという場合、論理削除されたレコードに登録されている emailデータは、新たに登録できるような作りにしたいと思います。

ただ、ユニーク制約をつけている場合、制約に引っかかって登録できないというよくある失敗が発生したりします(よくあるのか...?)

僕が今まで体験してきた現場だと論理削除時に emailデータを *****@gmail.com__削除データ__YYYYMMDDHHIISS みたいな感じでUPDATEしたりしてました。

Generated Columnsを使えば この上記のupdateをせずとも ユニーク制約の恩恵を受けることができるみたいです。

CREATE TABLE `users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'メールアドレス',
  `enabled` tinyint(1) GENERATED ALWAYS AS ((case when isnull(`deleted_at`) then 1 else NULL end)) STORED COMMENT '有効である',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_unique01` (`email`, `enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ユーザーテーブル';

enabled カラムの定義が重要で、 deleted_at がNULL(非論理削除状態) であれば 1 で そうでなければ NULL になります。

そして email と enabled のユニーク制約をつけておけば、論理削除状態と非論理削除状態どちらでも 同じemail値が存在できることができます。

終わりに

結構前からMySQL使ってますが全然存在を知りませんでした...

マニュアル読めよって話ですね。すいません。

現場からは以上です。