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?

Chaco

I use LAST_INSERT_ID() because it will return the correct ID when you have transactions – Bustee

Did you have a record with id = 5 and then delete it from the table? – Caaba

No, i didn't delete anything. This is way this situation confuses me. – Chaco

if it is auto incremented then you can select like this select max(id) from table – Pettiford

both working fine. if possible show your scenario. are you inserted multiple records ? – Squashy

@Chaco Note that it will increase the last inserted ID also for FAILED queries, so it's possible you did 1 insert query that failed, or that you just deleted some records from the table – Bustee

Answer 1

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.

Dehiscence

So what is the best solution to get last id? I have scenario with parent-child tablle insert. So after parent is filled i need to fill child table and the FK is ID – Chaco

Huh... good explanation, now the things are more clear to me. I have to change the way for inserting into parent-child tables. Another question, maybe for the new topic...how to insert multiple values into child table in my scenario? – Chaco

Inserting multiple rows into child table: I showed an example with two rows. It scales up to more. – Dehiscence

Answer 2

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)

Richma

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.

Kettie

Answer 4

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

Lefebvre

Answer 5

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

Gurango

Why would that help? – Literalism

in my humble experience, for the field that has AUTO INCREMENT, when you delete a record let say you have records 1,2,3 when you delete record number 3, when we add new record (with auto increment), it will be number 4 not 3. and the last insert id in reference says that it not show last id from specific table, but the last INSERT and UPDATE action at that connection (many insert update from and to whole table in that connection). Sorry for my bad English and explaination. – Gurango