複合キーによるAUTO_INCREMENTの利用

意外と知られてなさそうな雰囲気なので書いておきます。

mysql> create table t1 (c1 int, c2 int auto_increment, c3 varchar(100), primary key (c1,c2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 (c1,c3) values (1, "aiueo");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 (c1,c3) values (1, "aiueo");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 (c1,c3) values (1, "aiueo");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 (c1,c3) values (1, "aiueo");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 (c1,c3) values (2, "aiueo");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 (c1,c3) values (2, "aiueo");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 (c1,c3) values (2, "aiueo");
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----+----+-------+
| c1 | c2 | c3    |
+----+----+-------+
|  1 |  1 | aiueo |
|  1 |  2 | aiueo |
|  1 |  3 | aiueo |
|  1 |  4 | aiueo |
|  2 |  1 | aiueo |
|  2 |  2 | aiueo |
|  2 |  3 | aiueo |
+----+----+-------+
7 rows in set (0.00 sec)

これが使えるのはMyISAMとBDBのみということみたいですので、InnoDBマニアな方には意味の無い話ですが・・。