まだプログラマーですが何か?

プログラマーネタとアスリートネタ中心。たまに作成したウェブサービス関連の話も http://twitter.com/dotnsf

タグ:auto_increment

MySQL の主キーとかに auto_increment 属性を付けておくと連番の ID を自動生成してくれるので便利です。

例えばテーブル定義時にこんな感じで指定しておくと、
> create table names( id int primary key auto_increment, name varchar(100) );

データ挿入時に id を指定せず(気にせずに)他の値だけを挿入すればよくなって、
> insert into names( name ) values( '鈴木' );
> insert into names( name ) values( '佐藤' );
> insert into names( name ) values( '田中' );
> insert into names( name ) values( '山田' );
  :

ユニークな ID が自動で割り当てられる、ということが実現できます:
> select * from names;
+----+------+
| id | name |
+----+------+
|  1 | 鈴木 |
|  2 | 佐藤 |
|  3 | 田中 |
|  4 | 山田 |
     :
+----+------+

このように便利な auto_increment 属性ですが、便利な反面でコントロールする必要が出た場合に少しコツが必要になります。例えば 10 レコード入った時点で最後のデータに間違いが判明して、削除してしまったとします:
> select * from names;
+----+------+
| id | name |
+----+------+
|  1 | 鈴木 |
|  2 | 佐藤 |
|  3 | 田中 |
|  4 | 山田 |
     :
|  9 | 山本 |
| 10 | 木村 | ←ミスしたので削除↓
+----+------+

> delete from names where id = 10;

> select * from names;
+----+------+
| id | name |
+----+------+
|  1 | 鈴木 |
|  2 | 佐藤 |
|  3 | 田中 |
|  4 | 山田 |
     :
|  9 | 山本 |
+----+------+

この時点でテーブルには 9 件のレコードが入っています。ここで次のデータをインサートすると auto_increment はリセットされずに id = 11 として 10 件目のデータが挿入されます:
> insert into names( name ) values( '武田' );

> select * from names;
+----+------+
| id | name |
+----+------+
|  1 | 鈴木 |
|  2 | 佐藤 |
|  3 | 田中 |
|  4 | 山田 |
     :
|  9 | 山本 |
| 11 | 武田 |
+----+------+

ID をどう考えるかにもよりますが、この挙動を(ユニークである、という点は確保できているので)これはこれで OK と考えるのであれば特に気にする必要はありません。ただ連番ではなくなってしまうので、auto_increment を自分の意図で(上記の例であれば 10 から再スタートするように)リセットしたくなることもあります。

その場合は、以下のコマンドを実行します:
> alter table names auto_increment = 10;

names テーブルの auto_increment 属性値を 10 に(強制的に)変更しました。この状態からインサートすると、次に入るレコードの ID は 10 になります:
> insert into names( name ) values( '武田' );

> select * from names;
+----+------+
| id | name |
+----+------+
|  1 | 鈴木 |
|  2 | 佐藤 |
|  3 | 田中 |
|  4 | 山田 |
     :
|  9 | 山本 |
| 10 | 武田 |
+----+------+

ID は本来内部的に利用するべきものなので抜け番号があったからまずいわけでもなく、また連番でないといけないような制約が入るのは本来あまりいいことではありません。でもまあ気持ち的にスッキリはしますかね。

普段は MySQL 使いの自分が IBM DB2 を使おうとして、意外とハマったのが以下に紹介する2点です。

まず1点目。MySQL ではテーブル列の属性に auto_increment という指定を付与することができます:
mysql> create table langs(
  id int primary key auto_increment,
  name varchar(256)
);

この属性が付いた列はデータ挿入時に値を指定する必要がなく、定義に従ったルールでユニークな値を勝手に挿入してくれる便利な属性です(赤字はコメントです):
mysql> insert into langs( name ) values( 'Java' );  nameだけを指定してinsert
mysql> insert into langs( name ) values( 'PHP' );
mysql> insert into langs( name ) values( 'Ruby' );

mysql> select * from langs;
+----+------+
| id | name |
+----+------+
|  2 | Java | ユニークなid値が勝手に挿入されている
| 12 | PHP  |
| 22 | Ruby |
+----+------+

これと同じことを DB2 利用時にもやりたい! のですが、DB2 では create table 時に auto_increment 属性を認識してくれません。


では DB2 で同じことをするには、create table 時にどのような指定をすればよいのでしょうか?

その答がこちらです。indentity 構文を使いますが、ちと面倒:
db2> create table langs(
  id int primary key generated always as identity (start with 1 increment by 1),
  name varchar(256)
);

これをテーブル定義時に指定しておけば insert 時にはユニークな値を自動生成してくれるので楽ちんです。



2点目。同様に、MySQL では auto_increment 指定のある列に挿入された値を知りたい、と思うことがあります:
mysql> insert into langs( name ) values( 'Python' );
↑今、インサートしたこのレコードの ID 値が何だったのかを知りたい

それは LAST_INSERT_ID() で取得できました。
mysql> select last_insert_id() from langs;

これと同じことを DB2 の identity 指定した列に対してやるにはどうすればいいでしょうか?
その答は IDENTITY_VAL_LOCAL() です:
db2> select IDENTITY_VAL_LOCAL() from langs;

MySQL と DB2 で文法の違いがありますが、とりあえずはどちらも出来るということで。 

自分が迷ったのは今のところこの位ですが、Bluemix を使うユーザーが増えると必然的(?)に SQL Database や dashDB を使う人も増えると思います。DB2 をベースにしたこれらのサービスを使う時に、今回紹介したような点で戸惑う人の助けになれば幸いです。


(参考にしたページ)
http://stackoverflow.com/questions/13466347/how-to-auto-increment-in-db2
http://stackoverflow.com/questions/3087836/db2-how-to-get-the-last-insert-id-from-a-table
 

このページのトップヘ