ユニークインデックスについて

別段MySQLに限った事では無いのだが。

単純な例

CREATE TABLE demo (
  id INT UNSIGNED auto_increment PRIMARY KEY,
  label VARCHAR(255) UNIQUE
) ENGINE=InnoDB;

とやっておいてlabelにa, b, c, aといれる

mysql> insert into demo values(null, 'a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into demo values(null, 'b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into demo values(null, 'c');
Query OK, 1 row affected (0.01 sec)

mysql> insert into demo values(null, 'a');
ERROR 1062 (23000): Duplicate entry 'a' for key 'label'

このように、labelはテーブルの中でユニークな事が保証されているのでDuplicateとなって挿入できない。

mysql> select * from demo;
+----+-------+
| id | label |
+----+-------+
|  1 | a     |
|  2 | b     |
|  3 | c     |
+----+-------+
3 rows in set (0.00 sec)

これは一般的に値を制限するというレストリクション的な用途で使う事が多いのだが、適切にユニークにしておく事で「インデックス」を用いた検索の高速化にも繋がる。すなわち、この中からlabelが「b」のidは何かを調べる場合、インデックスが無ければ全てのカラム、idでいうところの1, 2, 3と舐めていかなければならないが、ユニークインデックスが貼られている場合はその限りでは無い。

これはselectの前にexplainを付けると確認できる

mysql> explain select * from demo where label='b' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: demo
         type: const
possible_keys: label
          key: label
      key_len: 258
          ref: const
         rows: 1
        Extra: Using index
1 row in set (0.00 sec)

ちなみにインデックスが無い場合はこんな具合。

           id: 1
  select_type: SIMPLE
        table: demo
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

複合ユニークキー

たとえばuserというカラムがあって、user Aさんはlabel a, b, c、 user Bさんもlabel a, b, cと持てるみたいなユニークキー。説明がわかり辛いのでとにかく作ろう。

CREATE TABLE demo (
  id INT UNSIGNED auto_increment PRIMARY KEY,
  user  VARCHAR(255) ,
  label VARCHAR(255) ,
  UNIQUE(user, label)
) ENGINE=InnoDB;

いれてみよう。

mysql> insert into demo values(null, 'A', 'a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into demo values(null, 'A', 'b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into demo values(null, 'A', 'c');
Query OK, 1 row affected (0.00 sec)

mysql> insert into demo values(null, 'B', 'a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into demo values(null, 'B', 'b');
Query OK, 1 row affected (0.01 sec)

mysql> insert into demo values(null, 'B', 'c');
Query OK, 1 row affected (0.00 sec)

mysql> select * from demo;
+----+------+-------+
| id | user | label |
+----+------+-------+
|  1 | A    | a     |
|  2 | A    | b     |
|  3 | A    | c     |
|  4 | B    | a     |
|  5 | B    | b     |
|  6 | B    | c     |
+----+------+-------+
6 rows in set (0.00 sec)

ここまでは普通に入る、が

mysql> insert into demo values(null, 'A', 'a');
ERROR 1062 (23000): Duplicate entry 'A-a' for key 'user'
mysql> insert into demo values(null, 'A', 'b');
ERROR 1062 (23000): Duplicate entry 'A-b' for key 'user'
mysql> insert into demo values(null, 'A', 'c');
ERROR 1062 (23000): Duplicate entry 'A-c' for key 'user'
mysql> insert into demo values(null, 'B', 'a');
ERROR 1062 (23000): Duplicate entry 'B-a' for key 'user'
mysql> insert into demo values(null, 'B', 'b');
ERROR 1062 (23000): Duplicate entry 'B-b' for key 'user'
mysql> insert into demo values(null, 'B', 'c');
ERROR 1062 (23000): Duplicate entry 'B-c' for key 'user'

このような組み合わせはDuplicateだぞという事で弾かれる。しかしここでnullを投入しまくると…

mysql> insert into demo values(null, 'A', null);
Query OK, 1 row affected (0.01 sec)

mysql> insert into demo values(null, 'A', null);
Query OK, 1 row affected (0.01 sec)
...
mysql> select * from demo;
+----+------+-------+
| id | user | label |
+----+------+-------+
| 16 | A    | NULL  |
| 17 | A    | NULL  |
| 18 | A    | NULL  |
| 19 | A    | NULL  |
| 20 | A    | NULL  |
| 21 | A    | NULL  |
| 22 | A    | NULL  |
|  1 | A    | a     |
|  2 | A    | b     |
|  3 | A    | c     |
| 13 | B    | NULL  |
| 14 | B    | NULL  |
| 15 | B    | NULL  |
|  4 | B    | a     |
|  5 | B    | b     |
|  6 | B    | c     |
+----+------+-------+
16 rows in set (0.00 sec)

このように入ってしまったりするので注意が必要。こういった場合、ほとんどのケースがNULLであって欲しくはないだろうから、NOT NULLとセットにしておいてもいいかもしれない。

インデックスを見る

繰替えしになるが、UNIQUE制限はインデックスによって表現される。従ってその中身を見る事も可能である。具体的にはshow index from テーブル名。興味があったら覗いてみるべし

mysql/unique.txt · 最終更新: 2012/04/07 10:32 (外部編集)
www.chimeric.de Creative Commons License Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0