INSERT 〜〜 ON DUPLICATE 時における、LAST_INSERT_ID()の挙動

INSERT 〜〜 ON DUPLICATE も LAST_INSERT_ID() も便利な関数なので、よく使わせてもらっています。
しかしこれらの関数を同時に利用した場合に、MySQL 5.1.12 より前のバージョンの場合に少し困る事があります。

autoincrementを利用していた場合に、LAST_INESRT_ID()の返す値が意図した値ではない場合があります。

検証

例えば下記のようなテーブル構造の場合を考えてみましょう。

CREATE TABLE `insert_test` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `test_id` int(11) NOT NULL,
  `test_name` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY (`test_id`)
);

さて、データをinsertしてみます。

INSERT INTO insert_test (test_id,test_name) value(1,"hoge");
SELECT LAST_INSERT_ID();

この処理の結果は、下記の通り

mysql> INSERT INTO insert_test (test_id,test_name) value(1,"hoge");
Query OK, 1 row affected (0.37 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 | 
+------------------+
1 row in set (0.00 sec)

これは意図した通りの動きですね。

次に、ON DUPLICATEを使ってみます。

INSERT INTO insert_test (test_id,test_name) value(2,"fuga") ON DUPLICATE KEY UPDATE test_name = 'foo';
SELECT LAST_INSERT_ID();

mysql> INSERT INTO insert_test (test_id,test_name) value(2,"fuga") ON DUPLICATE KEY UPDATE test_name = 'foo';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 | 
+------------------+
1 row in set (0.00 sec)

これも意図した通り、最後にinsertされた2というidが帰ってきました。
さて、問題は次のパターンです。

INSERT INTO insert_test (test_id,test_name) value(2,"foo") ON DUPLICATE KEY UPDATE test_name = 'foo';
SELECT LAST_INSERT_ID();

この場合、UNIQUE_KEYが設定されているtest_idの値（2）はすでに存在するため、insert処理ではなく、update処理がかかります。

mysql> INSERT INTO insert_test (test_id,test_name) value(2,"foo") ON DUPLICATE KEY UPDATE test_name = 'foo';
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 | 
+------------------+
1 row in set (0.00 sec)

お。update処理をしたはずなのに、LAST_INSERT_IDの値は、incrementされてますね。
ためしに全データを見てみても、

mysql> SELECT * FROM insert_test;
+----+---------+-----------+
| id | test_id | test_name |
+----+---------+-----------+
|  1 |       1 | hoge      | 
|  2 |       2 | foo       | 
+----+---------+-----------+
2 rows in set (0.00 sec)

２件しかありません。

解決方法

できればinsertした場合も、updateした場合も、最後に処理したidを返してもらいたいもの。。
これを実現するには、SQLを下記のように修正します。

INSERT INTO insert_test (test_id,test_name) value(2,"foo") ON DUPLICATE KEY UPDATE test_name = 'foo', id = LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();

試してみましょう。

mysql> INSERT INTO insert_test (test_id,test_name) value(2,"foo") ON DUPLICATE KEY UPDATE test_name = 'foo', id = LAST_INSERT_ID(id);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 | 
+------------------+
1 row in set (0.00 sec)

意図した動きになりました。

ま、mysqlのversionをさっさとあげろって話かもしれませんが、仕事上そう簡単にversionあげれない方もいると思うので。
ここに書いた事は、MySQL :: MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 8.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE 構文こちらに詳しく書いてあります。