MySQLのUNIQUE KEY制約とNULL値

UNIQUE KEY制約において、NULL値は同一だと見なさないらしい。UNIQUEキーで「同一」と判断するために、"="演算を行っているのだろうか？

$ mysql --version
mysql5  Ver 14.12 Distrib 5.0.67, for apple-darwin9.4.0 (i686) using  EditLine wrapper

mysql@localhost> USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql@localhost> CREATE TABLE unique_key_test
    -> (
    ->   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   uniq VARCHAR(255) UNIQUE,
    ->   value VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql@localhost> INSERT INTO unique_key_test (uniq, value) VALUES ('xxx', '123');
Query OK, 1 row affected (0.18 sec)

mysql@localhost> INSERT INTO unique_key_test (uniq, value) VALUES ('xxx', '123');
ERROR 1062 (23000): Duplicate entry 'xxx' for key 2

mysql@localhost> SELECT * FROM unique_key_test;
+----+------+-------+
| id | uniq | value |
+----+------+-------+
|  1 | xxx  | 123   |
+----+------+-------+
1 row in set (0.00 sec)

mysql@localhost> INSERT INTO unique_key_test (uniq, value) VALUES (NULL, '123');
Query OK, 1 row affected (0.00 sec)

mysql@localhost> INSERT INTO unique_key_test (uniq, value) VALUES (NULL, '123');
Query OK, 1 row affected (0.00 sec)

mysql@localhost> SELECT * FROM unique_key_test;
+----+------+-------+
| id | uniq | value |
+----+------+-------+
|  1 | xxx  | 123   |
|  2 | NULL | 123   |
|  3 | NULL | 123   |
+----+------+-------+
3 rows in set (0.00 sec)

mysql@localhost> INSERT INTO unique_key_test (uniq, value) VALUES (NULL, '123') ON DUPLICATE KEY UPDATE value='456';
Query OK, 1 row affected (0.00 sec)

mysql@localhost> SELECT * FROM unique_key_test;
+----+------+-------+
| id | uniq | value |
+----+------+-------+
|  1 | xxx  | 123   |
|  2 | NULL | 123   |
|  3 | NULL | 123   |
|  4 | NULL | 123   |
+----+------+-------+
4 rows in set (0.00 sec)

mysql@localhost> SHOW TABLE STATUS LIKE 'uniq%' \G
*************************** 1. row ***************************
           Name: unique_key_test
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 20
    Data_length: 80
Max_data_length: 281474976710655
   Index_length: 6144
      Data_free: 0
 Auto_increment: 5
    Create_time: 2008-08-26 23:49:05
    Update_time: 2008-08-26 23:52:14
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ストレージエンジンによって挙動が違うとかいう話も見かけたので、次はInnoDB。

mysql@localhost> CREATE TABLE unique_key_test_innodb
    -> (
    ->   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   uniq VARCHAR(255) UNIQUE,
    ->   value VARCHAR(255)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql@localhost> INSERT INTO unique_key_test_innodb (uniq, value) VALUES ('xxx', '123');
Query OK, 1 row affected (0.01 sec)

mysql@localhost> INSERT INTO unique_key_test_innodb (uniq, value) VALUES ('xxx', '123');
ERROR 1062 (23000): Duplicate entry 'xxx' for key 2
mysql@localhost> INSERT INTO unique_key_test_innodb (uniq, value) VALUES (NULL, '123');
Query OK, 1 row affected (0.00 sec)

mysql@localhost> INSERT INTO unique_key_test_innodb (uniq, value) VALUES (NULL, '123');
Query OK, 1 row affected (0.01 sec)

mysql@localhost> SELECT * FROM unique_key_test_innodb;
+----+------+-------+
| id | uniq | value |
+----+------+-------+
|  1 | xxx  | 123   |
|  2 | NULL | 123   |
|  3 | NULL | 123   |
+----+------+-------+
3 rows in set (0.00 sec)

mysql@localhost> INSERT INTO unique_key_test_innodb (uniq, value) VALUES (NULL, '123') ON DUPLICATE KEY UPDATE value='456';
Query OK, 1 row affected (0.00 sec)

mysql@localhost> SELECT * FROM unique_key_test_innodb;
+----+------+-------+
| id | uniq | value |
+----+------+-------+
|  1 | xxx  | 123   |
|  2 | NULL | 123   |
|  3 | NULL | 123   |
|  4 | NULL | 123   |
+----+------+-------+
4 rows in set (0.00 sec)

UNIQUE KEY制約とNULL値の関係が、ドキュメントのどこに書かれているのか、探せてません。

値の指定を省略(NULL)したら強制INSERT、指定ありなら重複を考慮してINSERT or UPDATE、という事をやるのにちょうど良いと思っているわけですが、仕様としてどうなのかがわからないので不安です。

MySQLは触るたびに発見があって、自分にがっかりしますね。知っとけよ、と。まあ、ボチボチ覚えていきます。