LoginSignup
57
37

More than 5 years have passed since last update.

MySQL CasualAdvent Calendar 2018Day 15
@taruhachi(taruhachi nakagawa)in株式会社ゆめみ

MySQL5.7以降で条件付きのユニーク制約をかけるためのベストプラクティス

Last updated at Posted at 2018-11-05

解決したい問題

PostgreSQLなどでは部分Indexが使えることでテーブルの一部分に対するユニーク制約などをDBで実現できたのに、MySQLではそれができないのでつらすぎる問題を解決したい。

今回は、以下のようなユーザーアカウントテーブルに対する操作を例として記載します。

CREATE TABLE IF NOT EXISTS account (
 id                BIGINT NOT NULL AUTO_INCREMENT,
 email             VARCHAR(255) NULL,
 create_timestamp  TIMESTAMP NULL,
 update_timestamp  TIMESTAMP NULL,
 del_flg           BOOL      NOT NULL,  -- 0: 有効な会員 1:削除済み
 PRIMARY KEY(id)
)

このときに、del_flg=0の有効な会員に対してのみユニークキー制約をかけたい。
ということはよくありますよね。
上記、PostgreSQLなど、部分Indexを利用できるRDBでは簡単に実装できたのですが、MySQLではこの意味での部分Indexが利用できなかったためこれまでは非常に面倒または気持ちの悪い実装をしなければなりませんでした。

結論

結論から言うと、MySQL5.7.6 以降、PostgreSQLのような部分Indexに相当するものを作成することができ、そちらでユニーク制約をかけることができるようになりました。
MySQLでも(エセ)部分Indexを使うぞ！！

PostgreSQLなどのRDBにおける部分Indexとは？

部分インデックスとは、テーブルの部分集合に構築されるインデックスです。 部分集合は、(部分インデックスの述語と呼ばれる)条件式で定義されます。 部分インデックスには、その述語を満たすテーブル行のみに対するエントリが含まれます。

つまり、巨大なテーブル中でも、よく使うデータ範囲だけにIndexを作成することにより、Indexサイズを小さくしたりするときなどに有効です。

ただし、それ以上に有効なのが、テーブル内の特定の条件に対してのみユニーク制約をかけたいといったパターンです。

部分Indexを使ってユニーク制約をかける例

先程のアカウントの例で言えばこちらのIndex追加で一発です。

CREATE UNIQUE INDEX uidx_email ON account(email) WHERE del_flg = 0;

こうすることでdel_flg=0の有効な会員にのみindexが作成されユニーク制約を発動させることができます。
便利ですね。

[参考] MySQLにおける部分Indexとは？

MySQLにも部分Indexがあると言われますが、MySQLにおける部分Indexは、textカラムなどの長いカラムの前方数文字分だけをindex化するというもので他のRDBにおける部分Indexとは性格が異なります。
そのため、上記のようにテーブルの一部に対してユニーク制約を追加する事ができませんでした。

過去のプラクティス（推奨しません）

MySQLでユニーク制約をかける方法その1

del_flg を使う代わりに、statusを使う、さらに、削除済み状態をnullで表現することにより、ユニーク制約を追加する。
参考： https://qiita.com/yuba/items/70165875cfe02b03513d

テーブル追加

CREATE TABLE IF NOT EXISTS account (
 id                BIGINT NOT NULL AUTO_INCREMENT,
 email             VARCHAR(255) NULL,
 create_timestamp  TIMESTAMP NULL,
 update_timestamp  TIMESTAMP NULL,
 status            INT       NULL,  -- 1: 有効な会員 null:削除済み
 PRIMARY KEY(id)
)

MySQLではnullはユニーク制約にかかりませんので、これに対して以下のindexを追加する事ができます。

index追加

CREATE UNIQUE INDEX uidx_alive_email ON account(email, status);

こうすることで、status=1の会員はemailがユニーク、status=nullの会員はemailの重複を許可するという制約となります。
ただし、statusをnullで表現するという方法ですので、正直気持ち悪さは残りますし、今後別のstatusに対してもユニークチェックから外したいといった要件が出たときに応えることができません。

MySQLでユニーク制約をかける方法その2

del_flgをフラグでなく、解約時に解約日付を入れるカラムとします。

テーブル定義

DROP TABLE account;
CREATE TABLE IF NOT EXISTS account (
 id                BIGINT NOT NULL AUTO_INCREMENT,
 email             VARCHAR(255) NULL,
 create_timestamp  TIMESTAMP NULL,
 update_timestamp  TIMESTAMP NULL,
 deleted_timestamp TIMESTAMP NOT NULL, --解約時が解約走査の日時をここに入れる、そのときは無効ユーザーでemailの重複が許される
 PRIMARY KEY(id)
);

index追加

CREATE UNIQUE INDEX uidx_alive_email ON account(email, deleted_timestamp);

こうすることにより、deleted_timestampさえ異なれば同じemailを扱う事ができるようになります。
しかし、有効な会員に対してこのdeleted_timestampをnullで設定すると、ユニーク制約のチェックができなくなるため、このカラムはnullに設定できません。
しかも有効な日付チェックなどを考えると'0000-00-00 00:00:00'の文字列なども入れることができずに、むしろこのカラムはvarchar()型にしてしまったほうがマシかも知れません。
また、このカラムに解約時の主キーのidを入れて管理しているシステムもあるかもしれません。
いずれにせよ解約状態をこういったカラムで管理することになりキモいです。

MySQL5.7.6 以降のベストプラクティス！！

MySQLでも(エセ)部分Indexを使う

MySQL5.7以降、MySQLでGenerated columnを使えるようになりました。
これは、SQLによる演算結果をカラムとして見せる手法です。
まが、このGenerated columnにはindexを追加することができます。
つまり、組み合わせる事により、関数Indexを利用する事ができるようになりました。

元テーブル

CREATE TABLE IF NOT EXISTS account (
 id                BIGINT NOT NULL AUTO_INCREMENT,
 email             VARCHAR(255) NULL,
 create_timestamp  TIMESTAMP NULL,
 update_timestamp  TIMESTAMP NULL,
 del_flg           BOOL      NOT NULL,  -- 0: 有効な会員 1:削除済み
 PRIMARY KEY(id);
)

今回、元のテーブルはPostgreSQLのときと同じく、del_flgだけで構いません。ここに以下のGenerated columnを追加します。

ALTER TABLE account ADD `active_email` varchar(255) GENERATED ALWAYS AS (
    CASE WHEN del_flg = 0 THEN email
    ELSE  null END
) VIRTUAL;

ここではCASE文を利用して、有効な会員の場合は、email、無効な会員の場合にはnullの入るカラムが入るviewを追加したのと同じような状態になります。
viewとの違いはこのカラムにindexを追加する事ができることです。

CREATE UNIQUE INDEX uidx_active_email ON account(active_email);

null項目にはユニーク制約が働きませんので、有効な会員のemailだけをactive_emailとしてチェックする部分Index的な事を実装する事ができます。

上記、一度でやると以下のDDLと等価です。

CREATE TABLE IF NOT EXISTS account (
  id               bigint(20)   NOT NULL     AUTO_INCREMENT,
  email            varchar(255) DEFAULT NULL,
  create_timestamp timestamp    NULL DEFAULT NULL,
  update_timestamp timestamp    NULL DEFAULT NULL,
  del_flg          bool         NOT NULL,
  active_email     varchar(255) GENERATED ALWAYS AS ((case when (del_flg = 0) then email else NULL end)) VIRTUAL,
  PRIMARY KEY (id),
  UNIQUE KEY uidx_active_email (active_email)
);

実際にやってみた

テーブル追加

mysql> CREATE TABLE IF NOT EXISTS account (
    ->   id               bigint(20)   NOT NULL     AUTO_INCREMENT,
    ->   email            varchar(255) DEFAULT NULL,
    ->   create_timestamp timestamp    NULL DEFAULT NULL,
    ->   update_timestamp timestamp    NULL DEFAULT NULL,
    ->   del_flg          bool         NOT NULL,
    ->   active_email     varchar(255) GENERATED ALWAYS AS ((case when (del_flg = 0) then email else NULL end)) VIRTUAL,
    ->   PRIMARY KEY (id),
    ->   UNIQUE KEY uidx_active_email (active_email)
    -> );
Query OK, 0 rows affected (0.04 sec)

emailの重複しない会員データ投入

mysql> INSERT INTO account SET
    ->   email            = 'email_00001',
    ->   create_timestamp = now(),
    ->   update_timestamp = now(),
    ->   del_flg          = 0;

Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO account SET
    ->   email            = 'email_00002',
    ->   create_timestamp = now(),
    ->   update_timestamp = now(),
    ->   del_flg          = 0;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM account;
+----+-------------+---------------------+---------------------+---------+--------------+
| id | email       | create_timestamp    | update_timestamp    | del_flg | active_email |
+----+-------------+---------------------+---------------------+---------+--------------+
|  1 | email_00001 | 2018-11-05 08:11:02 | 2018-11-05 08:11:02 |       0 | email_00001  |
|  2 | email_00002 | 2018-11-05 08:11:02 | 2018-11-05 08:11:02 |       0 | email_00002  |
+----+-------------+---------------------+---------------------+---------+--------------+
2 rows in set (0.00 sec)

active_emailのところに、emailがそれぞれ格納され、正常にデータ登録ができました。

emailの重複する会員データを投入

では、先程投入したのと同じemail_00001のアカウントを追加投入してみましょう。

mysql> INSERT INTO account SET
    ->   email            = 'email_00001',
    ->   create_timestamp = now(),
    ->   update_timestamp = now(),
    ->   del_flg          = 0;
ERROR 1062 (23000): Duplicate entry 'email_00001' for key 'uidx_active_email'
mysql> SELECT * FROM account;  
+----+-------------+---------------------+---------------------+---------+--------------+
| id | email       | create_timestamp    | update_timestamp    | del_flg | active_email |
+----+-------------+---------------------+---------------------+---------+--------------+
|  1 | email_00001 | 2018-11-05 08:11:02 | 2018-11-05 08:11:02 |       0 | email_00001  |
|  2 | email_00002 | 2018-11-05 08:11:02 | 2018-11-05 08:11:02 |       0 | email_00002  |
+----+-------------+---------------------+---------------------+---------+--------------+
2 rows in set (0.00 sec)

予想どおり、ユニークキー制約に引っかかり、データの投入をする事ができませんでした。

emailの重複する会員データを解約済みアカウントとして投入

今度は、同じemail_00001のアカウントをdel_flg=1として連続投入してみます。

mysql> INSERT INTO account SET
    ->   email            = 'email_00001',
    ->   create_timestamp = now(),
    ->   update_timestamp = now(),
    ->   del_flg          = 1;
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO account SET
    ->   email            = 'email_00001',
    ->   create_timestamp = now(),
    ->   update_timestamp = now(),
    ->   del_flg          = 1;
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO account SET
    ->   email            = 'email_00001',
    ->   create_timestamp = now(),
    ->   update_timestamp = now(),
    ->   del_flg          = 1;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM account; 
+----+-------------+---------------------+---------------------+---------+--------------+
| id | email       | create_timestamp    | update_timestamp    | del_flg | active_email |
+----+-------------+---------------------+---------------------+---------+--------------+
|  1 | email_00001 | 2018-11-05 08:11:02 | 2018-11-05 08:11:02 |       0 | email_00001  |
|  2 | email_00002 | 2018-11-05 08:11:02 | 2018-11-05 08:11:02 |       0 | email_00002  |
|  4 | email_00001 | 2018-11-05 08:14:37 | 2018-11-05 08:14:37 |       1 | NULL         |
|  5 | email_00001 | 2018-11-05 08:14:42 | 2018-11-05 08:14:42 |       1 | NULL         |
|  6 | email_00001 | 2018-11-05 08:14:42 | 2018-11-05 08:14:42 |       1 | NULL         |
+----+-------------+---------------------+---------------------+---------+--------------+
5 rows in set (0.02 sec)

今度は、active_emailの欄がNULLとなっているため、emailのユニーク制約にかからずに、何件でも投入できました。

最後に

実は上記のIndexはNULL項目を含むため、厳密な意味での部分Indexではないのですが、MySQLでも簡単にテーブルに対する部分制約を追加する事ができました。
MySQL5.7.6 以降で追加されたGenerated columnは使い方次第で非常に強力な機能ですので、他にも色んな所で利用できます。是非利用を検討してみてください。

他の利用例：Jsonカラムの展開、通常の複合indexでは解決できないIndexの追加など、、、。

57
37
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
57
37