はじめに

MySQL でユニーク制約と論理削除を同時に実現する方法。

TL;DR

  • Generated Column を使う
    • MySQL 5.7 以降であれば利用可能
  • 別カラムを参照して値を切替可能
    • status = 1 なら 'hoge', status = 2 なら 'fuga' みたいにできる
  • Generated Column に対してユニーク制約を設定
この記事が参考になった方
ここかここからチャージや購入してくれると嬉しいです(ブログ主へのプレゼントではなく、ご自身へのチャージ)
欲しいもの / Wish list

目次

  1. はじめに
  2. TL;DR
  3. 環境・条件
  4. 詳細
    1. 新規テーブル作成
    2. カラム追加
    3. 検証結果
  5. まとめ
  6. 参考文献

環境・条件

1
2
3
4
mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using  EditLine wrapper
Server version:		5.7.33 MySQL Community Server (GPL)

詳細

参考:

MySQL 5.7 以降で使用可能な Generated Column を使う。

Generated Column を使うと status = 1 なら 'hoge', status = 2 なら 'fuga' みたいに、他カラムを参照して動的に値を設定することが可能。かつ、Generated Column に対してユニーク制約を設定することもできる。

新規テーブル作成

  • email: メールアドレス
  • discarded_at: 論理削除日時
  • active_email: 有効なメールアドレス(Generated Column)
    • ユニーク制約
1
2
3
4
5
6
7
8
9
10
mysql> CREATE TABLE `my_users` (
  `id` integer NOT NULL AUTO_INCREMENT,
  `email` varchar(255),
  `active_email` varchar(255) GENERATED ALWAYS AS (
    CASE WHEN `discarded_at` IS NULL THEN `email` ELSE NULL END
  ) VIRTUAL,
  `discarded_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_on_my_users_on_active_email` (`active_email`)
);

カラム追加

email はすでに存在する前提で、discarded_at と active_email, およびユニーク制約を追加する。

1
2
3
4
5
6
7
mysql> ALTER TABLE `my_users`
  ADD `active_email` varchar(255) GENERATED ALWAYS AS (
    CASE WHEN `discarded_at` IS NULL THEN `email` ELSE NULL END
  ) VIRTUAL AFTER `email`,
  ADD `discarded_at` datetime DEFAULT NULL AFTER `active_email`;

mysql> ALTER TABLE `my_users` ADD UNIQUE KEY `index_on_my_users_on_active_email` (`active_email`);

検証結果

hoge@fuga.com のユーザーを登録。論理削除されてないので active_email にそのまま反映される。

1
2
3
4
5
6
7
8
9
mysql> INSERT INTO `my_users` (`email`) VALUES ('hoge@fuga.com');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM `my_users`;
+----+---------------+---------------+--------------+
| id | email         | active_email  | discarded_at |
+----+---------------+---------------+--------------+
|  1 | hoge@fuga.com | hoge@fuga.com | NULL         |
+----+---------------+---------------+--------------+

もう一度 hoge@fuga.com のユーザーを登録しようとするとユニーク制約でエラー。

1
2
mysql> INSERT INTO `my_users` (`email`) VALUES ('hoge@fuga.com');
ERROR 1062 (23000): Duplicate entry 'hoge@fuga.com' for key 'index_on_my_users_on_active_email'

論理削除(discarded_at を設定)すると、active_email が NULL になる。

1
2
3
4
5
6
7
8
9
10
11
mysql> UPDATE `my_users` SET `discarded_at` = NOW() WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `my_users`;
+----+---------------+--------------+---------------------+
| id | email         | active_email | discarded_at        |
+----+---------------+--------------+---------------------+
|  1 | hoge@fuga.com | NULL         | 2021-03-21 19:44:37 |
+----+---------------+--------------+---------------------+
1 row in set (0.00 sec)

この状態であれば hoge@fuga.com のユーザーを登録できる。

1
2
3
4
5
6
7
8
9
10
11
mysql> INSERT INTO `my_users` (`email`) VALUES ('hoge@fuga.com');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `my_users`;
+----+---------------+---------------+---------------------+
| id | email         | active_email  | discarded_at        |
+----+---------------+---------------+---------------------+
|  1 | hoge@fuga.com | NULL          | 2021-03-21 19:44:37 |
|  3 | hoge@fuga.com | hoge@fuga.com | NULL                |
+----+---------------+---------------+---------------------+
2 rows in set (0.01 sec)

まとめ

  • Generated Column を使う
    • MySQL 5.7 以降であれば利用可能
  • 別カラムを参照して値を切替可能
    • status = 1 なら 'hoge', status = 2 なら 'fuga' みたいにできる
  • Generated Column に対してユニーク制約を設定

参考文献

関連記事

この記事が参考になった方
ここかここからチャージや購入してくれると嬉しいです(ブログ主へのプレゼントではなく、ご自身へのチャージ)
欲しいもの / Wish list