MySQLで論理削除と一意性制約の両立

追記@2015-03-25

素直にMySQLのバックアップを取っていれば(さらに消すときにログを残しておけば)、特に論理削除せず普通に削除すればいい気もしてきました。いろいろと見通しがよくなりますし。とは言えTwitterみたいにアカウント削除しても30日は復活できる、みたいなことをしたい場合必要だと思います。要件次第ですね。


論理削除にはいろいろなメリットがあります。行削除のように関連する他テーブルへ削除が波及しないこと、エントリ復活ができること、障害時にデータ変更の経緯を追いやすくなることなどなど。

ところが論理削除の方針でDBを組んでいて困ったことはありませんか?
「 メールアドレスは一意性(UNIQUE)制約をかけたいのに、それだと削除済みのユーザと同じメールアドレスが使えないことになる 」

論理削除と一意性制約、両立はできないのか?
できないと思っている方、多いと思います。実はちゃんとできます。DB製品によって実現方法がちょっと違ってくるだけで。

論理削除と一意性制約を両立させる方法・DB製品別


↑を読んでなるほどと思ったのですが、MySQLでの実現方法が載っていなかったので、書いてみました。MySQLではCHECK制約が無視されますのでどうしようかなと思ったのですが、要はstatusの値も含めて一意かどうか判別すれば良さそうです。

今回行ったことは、

– 二つのカラム(usernameとemailを想定します)に対する一意性制約と論理削除の両立@MySQL

です。

ともかくやってみます。MySQLのコンソールに入って作業することを想定しています。「#」はコメントです。

# 以下の様なテーブルを作成します
> CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `username` varchar(19) NOT NULL,
  `state` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_state` (`email`,`state`),
  UNIQUE KEY `username_state` (`username`,`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 3つほど値をいれてみます
 > insert into users (email, username) values ("foo@example.com", "foo"),("bar@example.com", "bar"),("fizz@example.com", "fizz");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

# 状態を確認してみます
> select * from users;
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  1 | foo@example.com  | foo      |     0 |
|  2 | bar@example.com  | bar      |     0 |
|  3 | fizz@example.com | fizz     |     0 |
+----+------------------+----------+-------+
3 rows in set (0.00 sec)

# emailとusernameの一意性が保たれているか確かめてみます
# emailが同じだったりusernameが同じだったりするものを入れてみます

# 同じものをいれてみる→一意性制約によりエラー
> insert into users (email, username) values ("foo@example.com", "foo");
ERROR 1062 (23000): Duplicate entry 'foo@example.com-0' for key 'email_state'

# emailが異なり、usernameが同じものを入れてみる→一意性制約によりエラー 
> insert into users (email, username) values ("foo1@example.com", "foo");
ERROR 1062 (23000): Duplicate entry 'foo-0' for key 'username_state'

# emailは同じで、usernameが異なるものを入れてみる→一意性制約によりエラー
> insert into users (email, username) values ("foo@example.com", "foo1");
ERROR 1062 (23000): Duplicate entry 'foo@example.com-0' for key 'email_state'

# emailは同じで、usernameを大文字にしてみる→一意性制約によりエラー
# (これはいわゆるケースセンシティブでない例です。Collation: utf8_general_ciと
# 設定しておく必要があると思います)
> insert into users (email, username) values ("foo@example.com", "FOO");
ERROR 1062 (23000): Duplicate entry 'foo@example.com-0' for key 'email_state'

# ↑上記のように、一意性制約がきちんと働いていることが分かりました。

# 次に、論理削除をしてみます。id:3を論理削除します。
> update users set state = -1 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# ↓のようにstateが-1となっています
> select * from users;
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  1 | foo@example.com  | foo      |     0 |
|  2 | bar@example.com  | bar      |     0 |
|  3 | fizz@example.com | fizz     |    -1 |
+----+------------------+----------+-------+
3 rows in set (0.00 sec)

# さて、この状態において、emailのfizz@example.comや、usernameのfizzは、
# 論理削除されているので、同じものが追加されても受け付けるはずです。

# まったく同じものを挿入してみました。無事、挿入できました。
> insert into users (email, username) values ("fizz@example.com", "fizz");
Query OK, 1 row affected (0.00 sec)

# 状態を確認すると↓のようになっています。
# 一件、fizz@example.comとfizzが二つあっておかしな感じですが、
# あくまで一意性制約はemail+state, username+stateに掛かっているので、
# 正常です。
# 蛇足ですが、新しいレコードのidが8となっているのは、
# MySQLのトランザクションとロールバックの関係のようです。
# 参考: http://stackoverflow.com/questions/2787910/why-does-mysql-autoincrement-increase-on-failed-inserts
 > select * from users;
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  1 | foo@example.com  | foo      |     0 |
|  2 | bar@example.com  | bar      |     0 |
|  3 | fizz@example.com | fizz     |    -1 |
|  8 | fizz@example.com | fizz     |     0 |
+----+------------------+----------+-------+
4 rows in set (0.00 sec)

# 検索するときは、stateの条件も付与する必要があります

# OKな例↓
select * from users where username like "fizz" and state = 0;
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  8 | fizz@example.com | fizz     |     0 |
+----+------------------+----------+-------+
1 row in set (0.02 sec)

# だめな例↓(2つのレコードがでてくる)
> select * from users where username like "fizz";
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  3 | fizz@example.com | fizz     |    -1 |
|  8 | fizz@example.com | fizz     |     0 |
+----+------------------+----------+-------+
2 rows in set (0.00 sec)

というわけで無事、MySQLでもできました。

Bookmark the permalink.

Comments are closed.