採番テーブルというのは、例えば同じ DB の違うテーブル（data_1テーブルとdata_2テーブルとか）で id を重複させたくない（つまり、data_1テーブル、data_2テーブルでは auto_increment は付けない）場合などに、ユニークな id を生成するためのテーブルです。こんな感じ。

CREATE TABLE num (
    id bigint(20) unsigned NOT NULL DEFAULT '0'
) ENGINE=InnoDB;

+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | bigint(20) unsigned | NO   |     | 0       |       | 
+-------+---------------------+------+-----+---------+-------+

この採番テーブルをどうやって扱うか。2パターン考えてみました。

1. アプリ側で処理する場合

update num set id = LAST_INSERT_ID(id + 1);
select LAST_INSERT_ID();

id を採番する前には必ず更新します。InnoDB では更新時（挿入時や削除時も）には対象の行に対して自動的にロックを掛けます。他のセッションから同じ行を更新しようとしてもロック待ちになるので、LAST_INSERT_ID を使って更新し、更新後の id を LAST_INSERT_ID を使って取得することで、確実にユニークな id を取得できる（はず）。で、この id を使ってdata_1テーブルやdata_2テーブルにデータを挿入する感じです。

2. トリガーで処理する場合

delimiter //
 
CREATE TRIGGER set_data_1_id BEFORE INSERT ON data_1
FOR EACH ROW
BEGIN
    UPDATE num SET id = LAST_INSERT_ID(id + 1);
    SET NEW.id = LAST_INSERT_ID();
END;
//

CREATE TRIGGER set_data_2_id BEFORE INSERT ON data_2
FOR EACH ROW
BEGIN
    UPDATE num SET id = LAST_INSERT_ID(id + 1);
    SET NEW.id = LAST_INSERT_ID();
END;
//

delimiter ;

こちらは対象テーブル（data_1テーブルやdata_2テーブル）に挿入する前に、採番テーブル（numテーブル）を更新し、挿入時の id に、LAST_INSERT_ID で取得した id を代入します。NEW.id をいうのが挿入されるデータの id のことを表します。

LAST_INSERT_ID とは？

通常、これは最後に挿入された値を返します。ですが、今回の例だと直前で LAST_INSERT_ID に引数を渡しています。このように LAST_INSERT_ID に引数を渡した後、引数無しで LAST_INSERT_ID を呼び出すと直前で渡した引数の値が返されます。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 11.10.3 情報関数
expr が LAST_INSERT_ID() への引数として与えられる場合、その引数の値は関数によって戻され、LAST_INSERT_ID() によって戻される次の値として記憶されます。

LAST_INSERT_ID を使うと何が嬉しいの？

このような場合を考えてみます。接続が複数行われているような場合です。

1. DB に接続 (A) し、採番テーブルを更新する
2. (A) にて、現在の id を取得する 例えば "20"
3. 1. とは別に DB に接続 (B) し、採番テーブルを更新する
4. (B) にて、現在の id を取得する 例えば "21"
5. (A) にて、現在の id を取得する

普通に id = id + 1 で採番テーブルを更新する場合（ LAST_INSERT_ID を使わない場合）、5. の時点で受けとる id は "21" になっています。これが LAST_INSERT_ID を使った場合、 "20" のままです。これは LAST_INSERT_ID で返される id がセッション毎に保持されていて、他のセッションの影響を受けないためです。

ってことで、採番テーブルの扱いはこんな感じで大丈夫かなぁ。↑の2つだとトリガー方式の方が楽そうですね。あ、良くない点とかあったら是非教えてください〜(=ﾟωﾟ)ﾉ
タグ ：