MySQLのTRIGGERとLAST_INSERT_IDについて

MySQL5.5のTRIGGERを使ってIDを生成するってのをやってて、
LAST_INSERT_IDが取れなかったので一応メモ。

TRIGGERって何？

例えばユーザーテーブルを分割していてAUTOINCRIMENTでは一意なユーザーIDが発行できないようなケースってありますよね？
そんな時、シーケンステーブルみたいなものを作って、そこからIDをとってきたりするわけですが、
いちいちアプリ側で実装するのは面倒。

そんなときに使えるのがTRIGGER。

TRIGGERを使うと任意のテーブルに対して特定イベントが発生した時にゴニョゴニョやってくれるやつです。
つまりこれを使ってユーザーを追加した時にシーケンステーブルを更新してその値をユーザーIDにするみたいなことができます。

具体的には下記のような感じです。

まずテーブル２個と初期値を入れます。

USE last_insert_test;
CREATE TABLE table1 (
id1 int
) ENGINE=InnoDB;
CREATE TABLE table2 (
id2 int
) ENGINE=InnoDB;
INSERT INTO table2 VALUES(NULL); 

view raw ddl.sql This Gist brought to you by GitHub and Gist2Ameblo.

次にTRIGGERを作ります。
table1にinsertする前にゴニョゴニョしてねってやつです。

delimiter //
CREATE TRIGGER table1_before_insert BEFORE INSERT ON table1 FOR EACH ROW
BEGIN
DECLARE last_id1 INT;
IF NEW.id1 IS NULL THEN
BEGIN
SELECT MAX(id1) INTO last_id1 FROM table1;
IF last_id1 IS NULL THEN
SET NEW.id1 = 101;
ELSE
SET NEW.id1 = last_id1 + 5;
END IF;
UPDATE table2 SET id2=LAST_INSERT_ID(NEW.id1);
END;
END IF;
END;//
delimiter ; 

view raw trigger.sql This Gist brought to you by GitHub and Gist2Ameblo.

実際にtable1にinsertしてみます。

INSERT INTO table1 VALUES (NULL); 

view raw insert.sql This Gist brought to you by GitHub and Gist2Ameblo.

id1に101がセットされていることが確認できますね！
便利ですねー。
アプリ側で何も意識しなくていいのが便利だと思います。

LAST_INSERT_IDについて

しかしこのTRIGGERを使ってinsertしたレコードのID、LAST_INSERT_IDで取得できません。
さっきのSQLを実行後に下記のSQLを実行すると

SELECT LAST_INSERT_ID();

view raw last_insert_id.sql This Gist brought to you by GitHub and Gist2Ameblo.

0が帰ってきます。

理由はLAST_INSERT_ID() と LAST_INSERT_ID(expr) への参照を混ぜると、その効果は未定義になるからです。
うーむ。。
なんとも微妙な仕様。

もっといい方法なないかなー

エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド
	奥野 幹也 技術評論社 2010-06-12 売り上げランキング : 15333 Amazonで詳しく見る