SELECT LAST_INSERT_ID()

Question

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

Solution 1

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).

Solution 2

LAST_INSERT_ID()

is per user and per connection.

You can read more in MySQL doc.

Comments

[Staba]

maybe mybatis creates two sessions, i will try to create a query to insert and select in one... thanks

[Braiba]

That would just call LAST_INSERT_ID() once for every row in my_table

[frank]

@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.