last insert id deep dive

example.sql

-- This file will try to show how LAST_INSERT_ID() works.
-- LAST_INSERT_ID is a bit confusing, because it is a few different things tied up into one.
-- First of all, it is state that the mysql server keeps per client-session.
-- Whenever you INSERT a row into a table that uses an auto-incrementing column, this state will be
-- updated with the new id. Remember that this is per session and not per table or database. 
-- You will not see new auto-incrementing values when other connections insert new data.

-- Secondly, as a function call without any arguments, it will evaluate to said state. 
-- This can be a little bit misleading. If you do SELECT last_insert_id() FROM table, you might be fooled
-- into thinking that you are showing the last inserted id from `table`, but you would be wrong. 
-- last_inserted_ud() just shows state for the session, and it is not related to the table 
-- you are selecting from. The only influence that the table has is that it controls how many rows 
-- you will see. Here are some examples showing this basic functionality:

mysql> CREATE TABLE t (id INTEGER AUTO_INCREMENT PRIMARY KEY, f VARCHAR(1));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t(f) VALUES('a');
Query OK, 1 row affected (0.02 sec)

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

mysql> SELECT LAST_INSERT_ID() from dual; -- which table we are selecting from is of no importance
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t2 (id INTEGER AUTO_INCREMENT PRIMARY KEY, f VARCHAR(1));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t2 (f) VALUES('a');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO t2 (f) VALUES('b');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t2 (f) VALUES('c');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t2 (f) VALUES('d');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 (f) VALUES('e');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+----+------+
| id | f    |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

mysql> select last_insert_id() from t; -- just driving home the point.
+------------------+
| last_insert_id() |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)

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

mysql> select last_insert_id() from t2;
+------------------+
| last_insert_id() |
+------------------+
|                5 |
|                5 |
|                5 |
|                5 |
|                5 |
+------------------+
5 rows in set (0.00 sec)

-- The last functionality provided by last_insert_id is when used with a single argument. It will update the
-- session state with whatever is sent in, and then return this value.

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

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