6

Can somebody explain how works MySQL function LAST_INSERT_ID(). I'm trying to get id of last inserted row in database, but every time get 1.

I use mybatis.

Example query is :

<insert id="insertInto" parameterType="Something" timeout="0">
  INSERT INTO something (something) VALUES (#{something})
  <selectKey resultType="int">
    SELECT LAST_INSERT_ID()
  </selectKey>
</insert>

Code:

System.out.println("Id : " + id)

Output:

Id : 1
abatishchev
  • 98,240
  • 88
  • 296
  • 433
Staba
  • 61
  • 1
  • 1
  • 2
  • 1
    Is your id defined as `autoincrement` ? – j0k Apr 24 '12 at 08:20
  • It could also be a [The id is injected in the object](http://stackoverflow.com/a/12106243/1504300) case like the one happened to me, aka "you didn't read well the docs". – reallynice Jan 04 '17 at 13:14

6 Answers6

10

LAST_INSERT_ID returns the last value implicitly inserted into an AUTO_INCREMENT column in the current session.

CREATE TABLE mytable (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, value INT NOT NULL);

To make the column autoincrement, you should omit it from the INSERT list:

INSERT
INTO    mytable (value)
VALUES  (1)

or provide it with a NULL value:

INSERT
INTO    mytable (id, value)
VALUES  (NULL, 1)

After that,

SELECT  LAST_INSERT_ID()

will return you the value AUTO_INCREMENT has inserted into the id column.

This will not work if:

  1. You provide the explicit value for the AUTO_INCREMENT column
  2. You call LAST_INSERT_ID in another session
  3. You insert more than one row in the same statement (LAST_INSERT_ID() will return the value of the first row inserted, not the last one).
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • maybe mybatis creates two sessions, i will try to create a query to insert and select in one... thanks – Staba Apr 24 '12 at 08:39
4
LAST_INSERT_ID()

is per user and per connection.

You can read more in MySQL doc.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Silviu
  • 835
  • 14
  • 22
  • @Silviu is it safe in connection pool? I saw someone said `last_insert_id` has problem in connection pool because same connection can be reuse in connection pool. – frank May 23 '18 at 08:46
0

I haven't used MyBatis yet, but after you insert something into the table, check that the auto_increment value is being updated with the following MySQL query: 

SELECT Auto_increment FROM (SHOW TABLE STATUS LIKE '<table-name>') as A;

Also make sure that you have no explicit value you're giving to the auto_increment field. You need to let the DB set it for itself.

Here are some other things you could try:

  1. Make sure you read the result as an integer. This shows an analogous case that required explicit conversion to Int32.

  2. If you're doing multiple inserts, know that only the ID of the first inserted tuple is treated as the LAST_INSERT_ID. According to this (Search for use test).

Karthik V
  • 1,033
  • 1
  • 14
  • 29
0

I have two solutions, after implements much complicated i find out about second one... I will tell you second which is better one ... It's pretty simple... just in query insert this keyProperty="id" Like this : <insert id="insertInto" parameterType="Something" keyProperty="id" timeout="0"> INSERT INTO something (something) VALUES (#{something}) </insert> Query returns id of inserted row Thanks!

Staba
  • 61
  • 1
  • 1
  • 2
0

Example 1: 

mysql> CREATE TABLE prime2 LIKE prime;
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT LAST_INSERT_ID(); //From table prime!!!
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)


mysql> INSERT INTO prime2 VALUES(1,1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT LAST_INSERT_ID(); 
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec) //From table prime!!!
zloctb
  • 10,592
  • 8
  • 70
  • 89
-1

You have to use a Table name to select the last insert id.

Example:

SELECT LAST_INSERT_ID() FROM my_table;
Ben
  • 51,770
  • 36
  • 127
  • 149
Rim
  • 7
  • 1