Fetching last insert id shows wrong number

Question

I have table with three records. There is one filed as auto_increment. The ID's are 1, 2, 3...

When I run query

SELECT LAST_INSERT_ID() FROM myTable

The result is 5, even the last ID is 3. Why?

And what is better to use? LAST_INSERT_ID() or SELECT MAX(ID) FROM myTable?

Answer 1

last_insert_id() has no relation to specific tables. In the same connection, all table share the same.

Below is a demo for it.

Demo:

mysql> create table t1(c1 int primary key auto_increment);
Query OK, 0 rows affected (0.11 sec)

mysql> create table t2(c1 int primary key auto_increment);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values(4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(null);
Query OK, 1 row affected (0.02 sec)

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

Answer 2

The LAST_INSERT_ID() function only returns the most recent autoincremented id value for the most recent INSERT operation, to any table, on your MySQL connection.

If you haven't just done an INSERT it returns an unpredictable value. If you do several INSERT queries in a row it returns the id of the most recent one. The ids of the previous ones are lost.

If you use it within a MySQL transaction, the row you just inserted won't be visible to another connection until you commit the transaction. So, it may seem like there's no row matching the returned LAST_INSERT_ID() value if you're stepping through code to debug it.

You don't have to use it within a transaction, because it is a connection-specific value. If you have two connections (two MySQL client programs) inserting stuff, they each have their own distinct value of LAST_INSERT_ID() for the INSERT operations they are doing.

edit If you are trying to create a parent - child relationship, for example name and email addresses, you might try this kind of sequence of MySQL statements.

 INSERT INTO user (name) VALUES ('Josef');
 SET @userId := LAST_INSERT_ID();
 INSERT INTO email (user_id, email) VALUES (@userId, '[email protected]');
 INSERT INTO email (user_id, email) VALUES (@userId, '[email protected]');

This uses LAST_INSERT_ID() to get the autoincremented ID from the user row after you insert it. It then makes a copy of that id in @userId, and uses it twice, to insert two rows in the child table. By using more INSERT INTO email requests, you could insert an arbitrary number of child rows for a single parent row.

Pro tip: SELECT MAX(id) FROM table is a bad, bad way to figure out the ID of the most recently inserted row. It's vulnerable to race conditions. So it will work fine until you start scaling up your application, then it will start returning the wrong values at random. That will ruin your weekends.

Answer 3

I don't think this function does what you think it does. It returns the last id inserted on the current connection.

If you compare that to SELECT MAX(ID) this selects the highest ID irrespective of connection, be careful not to get them mixed up or you will get unexpected results.

As for why it is showing 5 its probably because its the last id to be inserted, I believe that this value will remain even if the record is removed, perhaps someone could confirm this.

Answer 4

maybe you should restart the database connection than reconnected again for fresh data

Answer 5

Table level triggers is what can come to rescue here. e.g. before insert trigger.