複合キーのauto increment実験

前回に引き続き、mysqlのauto incrementをさわってみる。 今回は複合キーのauto incrementについて。 複合キーは複数のカラムで1つのキーとみなす。つまり同じカラムでは重複を許すけれども、複数のカラムを合わせてみると他のレコードと重複していないようなキー。 それにauto incrementをつけてみる。

まずcreate table。MyISAMのみ許される。InnoDBの場合は怒られる。

mysql> create table test ( id1 int(2) , id2 int(3) auto_increment , primary key (id1,id2) ) engine=MyISAM;
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql>
mysql> create table test2 ( id1 int(2) , id2 int(3) auto_increment , primary key (id1,id2) ) engine=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

こんな状態のときにinsertするといい感じでauto incrementしてくれる。

mysql> select * from test;
+-----+-----+
| id1 | id2 |
+-----+-----+
|   1 |   1 |
|   1 |   3 |
+-----+-----+
2 rows in set (0.00 sec)

mysql> insert into test(id1) values (1);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from test;
+-----+-----+
| id1 | id2 |
+-----+-----+
|   1 |   1 |
|   1 |   3 |
|   1 |   4 |
+-----+-----+
3 rows in set (0.00 sec)

追加でid1に別の数字を入れた場合もいい感じにしてくれる。

ysql> insert into test(id1) values (2);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from test;
+-----+-----+
| id1 | id2 |
+-----+-----+
|   1 |   1 |
|   1 |   3 |
|   1 |   4 |
|   2 |   1 |
+-----+-----+
4 rows in set (0.00 sec)

このときのauto_incrementの次の値をみてみると1のまま。毎回計算していれてくれているのかな？

mysql> select auto_increment from information_schema.tables where table_name='test';
+----------------+
| auto_increment |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

なおid1を指定せずにid2だけ指定してinsertした場合、id1にはdefaultの0がinsertされた

mysql> insert into test(id2) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+-----+-----+
| id1 | id2 |
+-----+-----+
|   0 |   1 |
|   1 |   1 |
|   1 |   3 |
|   1 |   4 |
|   1 |   5 |
|   2 |   1 |
+-----+-----+
6 rows in set (0.00 sec)

mysql> show create table `test`;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id1` int(2) NOT NULL DEFAULT '0',
  `id2` int(3) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id1`,`id2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

調子にのって再度id2だけ指定してinsertしてみると、怒られる。当たり前だけど、こちらはauto incrementしてくれない。

mysql> insert into test(id2) values (1);
ERROR 1062 (23000): Duplicate entry '0-1' for key 'PRIMARY'