[MySQL] INSERTしたデータのIDを取得したい

2013年6月15日土曜日
　結構需要があると思うんですが、MySQLでINSERTした時、挿入されたデータのID(Auto IncrementなPrimary Key)が取りたいことってよくあると思います。INSERTが1つだけならLAST_INSERT_ID()を用いて取得することができるのですが、バルクインサート(1つのクエリで複数のデータを挿入)では取得出来ません。今回はそれを取得するための方法をまとめたいと思います。MySQLのバージョンは5.1なので、それ以降のMySQLでは苦労しなくてもうまくいくかもしれません。

問題となるのはどこか

　おおまかに以下の様なことをやりたいわけです。

  • バルクインサートしたデータのIDを取得したい(AI+PK)
  • ON DUPLICATE KEY UPDATEで変更されたデータのIDも取得したい

　こんな感じです。まず1つめの条件を外して、単一のINSERT ... ON DUPLICATE KEY UPDATEを利用した場合は、以下のようなクエリでIDが取得出来ます。 

// テーブル構造は以下の様な感じ
CREATE TABLE test(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    data INT
)

INSERT INTO test (id, data) VALUES (1, 100)
ON DUPLICATE KEY UPDATE
    data = VALUES(data),
    id = LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();

　INSERT文の4行目がミソで、LAST_INSERT_IDに引数を渡すと、その値が次回LAST_INSERT_IDを呼び出した時の戻り値になります。これで取得できるわけですね。

　問題はバルクインサートによる複数データの挿入です。複数データを挿入した場合、LAST_INSERT_IDで取得できるのは、一番初めに挿入されたデータのIDです。つまり、本当に最後に挿入されたデータのIDではなく、あくまで最後に実行されたINSERTの最初のIDが返ってくる、というわけです。

　これは困りました。

　簡単に思い浮かぶ解決策としては、FOUND_ROWS関数で挿入されたデータ数を取得しつつ、LAST_INSERT_IDで取得したIDから連番でIDを取得する、といった方法が考えられますが、この方法ではデータが連番でしっかり保存されることが前提となります。MyISAMなら大丈夫そうな気がしますが、InnoDBではなんかうまくいかなそうな気がします(私はDB(SQL)が全くわからない人間なので、もしかするとトランザクション中なら挿入が連番になるのかもしれません。知ってる人は教えて下さい)。

　ではどうするか、ということで、以下の2つの方法で取得できるようにしてみたいと思います。

CONNECTION_IDを利用する

　MySQLにはCONNECTION_ID()という関数があり、現在の接続IDを取得することができます。この接続IDはユニークなものになります(ただし、関数呼び出し時においてユニークであり、何らかのタイミングでIDがリセットされることがあるようです)。このIDをテーブルに保存しておき、バルクインサート後にSELECTで検索してやれば、その接続中にINSERTあるいはUPDATEしたデータが取得出来ます。 

INSERT INTO test (data, cid) VALUES
    (100, CONNECTION_ID()), 
    (200, CONNECTION_ID()),
    (300, CONNECTION_ID())
ON DUPLICATE KEY UPDATE
    data = VALUES(data),
    cid = CONNECTION_ID();
SELECT id FROM test WHERE cid = CONNECTION_ID();

　こんな感じです。テーブルtestには、先ほど示したカラムの他に「cid」というカラムを追加しておきます。ここに接続IDを保存し、あとからSELECTで検索します。

　ただし、先述の通り接続IDはどこかのタイミングでリセットされているようで(DBサーバー再起動時とかかな)、過去に経験があったわけではありませんが接続IDが重複してしまう可能性がありそうです(ここもソースがなくあくまで仮説です。知ってる人は教えて下さい!)。そのため、検索後にcidの値をリセットしなければなりません。

　この方法の問題点として、

  • テーブルに余計なカラム(接続ID用)が必要になる
  • INSERT、SELECT、UPDATEが必要になるため、負荷が大きい

　が挙げられます。負荷は測ったわけではないのですが、問題は1つ目の無駄なカラムが出来てしまうことではないかと思います。そこで、もう1つ、なんだかうまくやれそうな方法を考えてみたいと思います。

一時テーブルとトリガ

　聞いたことがない方もいるかもしれませんが、MySQLには一時テーブル(temporary table)とトリガ(trigger)というものがあります。

　一時テーブルは、その名の通り一時的にメモリ上に生成されるテーブルのことで、接続が切れた段階で自動的に破棄されます。EXPLAINをした時に、Extraに「Using temporary」が表示されることがあるかと思いますが、その時に生成されているのが一時テーブルです。

　続いてトリガについてです。トリガは、INSERTやUPDATE、DELETEと言ったDMLが実行される前や後に実行されるプロシージャです。詳しくはググってくださいですが、INSERT後に何か処理をしたい…要するに今回のような場合に利用できる便利な機能です。ただし、MySQLではFOR EACH ROWしかサポートされていない、つまりINSERTでデータが1つ挿入される度にトリガが呼ばれることになるので、一度に複数のデータを挿入する場合…例えば今回のような場合に利用する場合には少し注意が必要かもしれません。

　頭のいい皆様ならきっともうお分かりになったはず。そうです、トリガを用いて挿入されたデータのIDを一時テーブルに保存しておくことでIDを取得しよう、っていうわけです。SQLを以下に示します。 

// これがトリガ
CREATE TRIGGER GET_ID AFTER INSERT
    ON test FOR EACH ROW INSERT INTO ids(id) VALUES(NEW.id);

// 一時テーブルの生成
CREATE TEMPORARY TABLE ids(id INT(11));
INSERT INTO test (data) VALUES (100), (200), (300);
SELECT id FROM ids;

　こんな感じです。トリガに関しての詳しい説明は省きますが、「CREATE TRIGGER トリガ名 { AFTER | BEFORE } { INSERT | UPDATE | DELETE} ON テーブル名 FOR EACH ROW 実行するSQL」な感じでトリガを設定出来ます。イベント発火元の処理は「INSERT」「UPDATE」「DELETE」を選ぶことが出来、また「AFTER」で操作後、「BEFORE」で操作前にそれぞれトリガを実行することができます。

　また、トリガ中に出ている「NEW」ですが、これには実際に挿入される情報が入っています。つまり、「NEW.id」は新しくAuto Incrementによって生成されたIDが入っているわけですね。この値を一時テーブルidsに保存します。ちなみに「OLD」というものもあり、この中には古い情報…つまりUPDATEで変更される前のデータ、あるいはDELETEによって削除される前のデータが入っています。

　これによってバルクインサートで挿入されたデータのIDのリストを取得することが可能です。が、今回はON DUPLICATE KEY UPDATEが使いたいのでした…。INSERTだけならこの方法でうまくいきますが、ON DUPLICATE KEY UPDATEがあるとうまくいきません。

ON DUPLICATE KEY UPDATE

　こいつがかなり厄介な存在です。「UPDATE」とついていますが実際にはUPDATEでもなんでもなく、INSERT文なのです。つまり、UPDATEに対しトリガを作成してもトリガは発火しません。じゃぁどうするのかというと、「BEFORE INSERT」にトリガを仕掛けます。INSERT ... ON DUPLICATE KEY UPDATEでは、まずINSERTが実行され、失敗した場合にUPDATEを実行する構造になっているようです。

　おおまかな流れとして、INSERTが成功する場合は

"BEFORE INSERT" → INSERT → "AFTER INSERT"

が、失敗してUPDATEを実行する場合は

"BEFORE INSERT" → INSERT → "BEFORE UPDATE" → UPDATE

の流れになるようです。「AFTER UPDATE」がなぜ呼ばれないのかはわかりません。ちなみにドキュメントにはしっかり「AFTER UPDATEが呼ばれる」と書いてあります。上記の流れから、「BEFORE INSERT」にトリガを貼れば、どちらの場合でもトリガを起動することができます。

　さて、ここで問題になるのが、挿入されたデータのIDを取得できるのかどうか、という点です。まず、UPDATEが実行された場合ですが、この場合、既にIDが定まっているのでBEFORE INSERT中でも「NEW.id」でIDが取得出来ます。一方で、INSERTが実行されAuto Incrementによって新しくIDが生成される場合、当然ですがINSERT前にはIDが定まっていません。つまり、ここでは「NEW.id」は0になってしまいます。

　したがって、「AFTER INSERT」と「BEFORE UPDATE」の両方にトリガを作成し、IDを取得する方法が考えられるのですが、なぜか一時テーブルに対して2度操作を加えることができません。一時テーブルに対しUNIONや自己結合ができないのはよく知られていますが、なぜか複数のトリガ中でINSERTしてもダメなようです(ちなみに同じトリガ中では何度INSERTしても大丈夫なようなのですが…よくわかりません)。 

CREATE TRIGGER UPDATE_ID BEFORE INSERT
    ON test FOR EACH ROW INSERT INTO ids(id) VALUES(NEW.id);
CREATE TRIGGER GET_ID AFTER INSERT
    ON test FOR EACH ROW INSERT INTO ids(id) VALUES(NEW.id);

　(たぶん)3〜4行目で「Can't reopen ids」と言われてしまいます。うーん、よくわからない。難しい。

　色々考えたのですがどうやら以下の方法で回避できるようです。

ストアド・プロシージャ

　複数のトリガの中で操作できないなら、1つのストアド・プロシージャの中で呼ばべいいじゃない！というわけでトリガの内容をそのままストアド・プロシージャに入れます。 

DELIMITER $$
CREATE PROCEDURE SAVE_ID(IN id INT)
BEGIN
    INSERT INTO ids(id) VALUES(id);
END; $$
DELIMITER ;

　こんな感じ。「END」のあとに「$$」を打ち忘れると、エラーも何も出ないのにプロシージャが定義されないのでちょっとハマります。そして、このプロシージャをトリガから呼んでやればいいわけです。 

DELIMITER $$
CREATE PROCEDURE SAVE_ID(IN id INT)
BEGIN
    INSERT INTO ids(id) VALUES(id);
END; $$
DELIMITER ;

CREATE TRIGGER INSERT_ID AFTER INSERT
    ON test FOR EACH ROW CALL SAVE_ID(NEW.id);
CREATE TRIGGER UPDATE_ID BEFORE UPDATE
    ON test FOR EACH ROW CALL SAVE_ID(NEW.id);

CREATE TEMPORARY TABLE ids(id INT(11));
INSERT INTO test (id, data) VALUES
    (1, 100),
    (null, 200),
    (null, 300)
ON DUPLICATE KEY UPDATE
    data = VALUES(data);
SELECT id FROM ids;

　これでうまくいくのではないかと思います。ちなみにストアド・プロシージャとトリガは一度定義したらあとは使いまわせるので、実際のクエリは 

CREATE TEMPORARY TABLE ids(id INT(11));
INSERT INTO test (id, data) VALUES
    (1, 100),
    (null, 200),
    (null, 300)
ON DUPLICATE KEY UPDATE
    data = VALUES(data);
SELECT id FROM ids;

　この部分になります。

最後に

　MySQLとか全然勉強してない私ですが、なんとかうまい具合にID取れたなーと思っています。多分やり方としてはあんまり良くないと思いますし、パフォーマンスも酷いことになっていると思いますが、まぁ個人で使う分には問題ないのかなーと思ってもいます。もしかしたらこの方法でよくないことが起こるかもしれませんし、もっと簡単かつパフォーマンスも出る方法が有るかもしれませんので、そういう方法を見つけたら教えてくれるとうれしいです。

　だいぶ長くなってしまいましたが以上です。疲れた！

0 件のコメント :

コメントを投稿