MySQLのこういうのっていかがなもんか

良くはない。それはわかってる。でもなんかちょっとくらいなら使っていいかな的な
例えばこのようなテーブル

Create Table: CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `delete_null_flag` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`,`delete_null_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

があって、適当にinsertしてみる

mysql> insert into users (name) values ('shoichimasuhara');
Query OK, 1 row affected (0.01 sec)

まあ普通に入るし

mysql> select * from users;
+----+-----------------+------------------+
| id | name            | delete_null_flag |
+----+-----------------+------------------+
|  1 | shoichimasuhara |                1 |
+----+-----------------+------------------+

もう一回同じのを入れるとunique制約でコケる

mysql> insert into users (name) values ('shoichimasuhara');
ERROR 1062 (23000): Duplicate entry 'shoichimasuhara-1' for key 'name'

ところでdelete_null_flagをNULLにしてみてもう一度insert

mysql> update users set delete_null_flag = NULL where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+----+-----------------+------------------+
| id | name            | delete_null_flag |
+----+-----------------+------------------+
|  1 | shoichimasuhara |             NULL |
+----+-----------------+------------------+
1 row in set (0.00 sec)

mysql> insert into users (name) values ('shoichimasuhara');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+-----------------+------------------+
| id | name            | delete_null_flag |
+----+-----------------+------------------+
|  1 | shoichimasuhara |             NULL |
|  3 | shoichimasuhara |                1 |
+----+-----------------+------------------+
2 rows in set (0.00 sec)

そりゃま成功するんだけど、さらに追加したレコードのdelete_null_flagもNULLにしてみると

mysql> update users set delete_null_flag = NULL where id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+----+-----------------+------------------+
| id | name            | delete_null_flag |
+----+-----------------+------------------+
|  1 | shoichimasuhara |             NULL |
|  3 | shoichimasuhara |             NULL |
+----+-----------------+------------------+
2 rows in set (0.00 sec)

成功。知らなかった(無知ですみませんすみません…)。これは

NULLは各々ユニークとして扱われる

ということらしい(ほんとか？) まあだいたいそんな感じ。調べてない。気になる人調べて。

んで

アクティブなユーザ名はユニークにしたいけど削除されたユーザの情報は残したい。でも削除済みユーザテーブルは作りたくない

とかいうワガママを発揮したい時にdeleteフラグに使えないかなーなんてだめですかそうですか。なんか他にまともな方法無いですか…。

mysql> alter table users add comment text;
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into users (name, comment) values ('shoichimasuhara', '私はたぶん3人目だと思うから。');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users\G
*************************** 1. row ***************************
              id: 1
            name: shoichimasuhara
delete_null_flag: NULL
         comment: NULL
*************************** 2. row ***************************
              id: 3
            name: shoichimasuhara
delete_null_flag: NULL
         comment: NULL
*************************** 3. row ***************************
              id: 4
            name: shoichimasuhara
delete_null_flag: 1
         comment: 私はたぶん3人目だと思うから。
3 rows in set (0.00 sec)