MySQLのlast_insert_id()は「最後にインサートしたレコードのID」じゃなかった

今日たまたま引っかかってへ～っと思ったネタ。

ごく単純に「インサートしたレコードのIDを取りたいならlast_insert_id()使えば良い」と認識していたので、ちょっと驚いたというか勉強になったというか。

↓こんなテーブルを考える。

mysql> desc tbl;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| value | text    | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+

1. 主キーを指定せずにインサート

mysql> insert tbl (value) values("test1");
Query OK, 1 row affected (0.04 sec)

当然入る。

mysql> select * from tbl;
+----+-------+
| id | value |
+----+-------+
|  1 | test1 |
+----+-------+
1 row in set (0.00 sec)

last_insert_id()を取ると・・・

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

もちろん挿入した1が返ってくる。

2. 主キーを指定してインサートすると・・・？

次に主キーを明示的に指定したインサートの場合。

mysql> insert tbl (id, value) values(2,"test2");
Query OK, 1 row affected (0.03 sec)

mysql> select * from tbl;
+----+-------+
| id | value |
+----+-------+
|  1 | test1 |
|  2 | test2 |
+----+-------+
2 rows in set (0.00 sec)

新たにインサートした２が帰ってきて欲しいのだが・・・

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

要するセッションのうちで最後にインサートしたAUTO INCREMENTの値が保存されているのであり、今回のインサート文ではAUTO INCREMENTが働かないため、前回の1がそのまま帰ってきているのだ。

last_insert_id()の挙動

そんなわけでついでなんでlast_insert_id()について調べると、どうも引数を入れると次に引数を入れなかった時にその値が返ってくる動きをするらしい。

↓こんな具合。

mysql> select last_insert_id(12);
+--------------------+
| last_insert_id(12) |
+--------------------+
|                 12 |
+--------------------+
1 row in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               12 |
+------------------+
1 row in set (0.00 sec)

なので使う場面があるかは分からないが、今回の例でいえば↓こんな感じにすることもできる。

mysql> insert tbl (id, value) values(last_insert_id(3), "test3");
Query OK, 1 row affected (0.03 sec)

mysql> select * from tbl;
+----+-------+
| id | value |
+----+-------+
|  1 | test1 |
|  2 | test2 |
|  3 | test3 |
+----+-------+
3 rows in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

---

おまけ、Sqlite3の場合

↓同様のスキーマのテーブルを用意したとして

sqlite> .schema tbl
CREATE TABLE tbl (
id integer primary key autoincrement,
value text
);

1'. 主キーを指定せずにインサート

sqlite> insert into tbl (value) values ("test1");
sqlite> select * from tbl;
1|test1

MySQLでのlast_insert_id()にあたる、last_insert_rowid()で取得できる。

sqlite> select last_insert_rowid();
1

2'. 主キーを指定してインサートすると・・・？

sqlite> insert into tbl (id, value) values (2, "test2");
sqlite> select * from tbl;
1|test1
2|test2
sqlite> select last_insert_rowid();
2

こっちだと普通に2が返ってくる！

---

普段何気なく使ってるデータベースだけど、まだまだ学ぶことはいっぱいありそうだ。

理論から学ぶデータベース実践入門 ~リレーショナルモデルによる効率的なSQL (WEB+DB PRESS plus)