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

Wait

Is your id defined as autoincrement ? – Completion

It could also be a The id is injected in the object case like the one happened to me, aka "you didn't read well the docs". – Makowski

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

Admass

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

Answer 2

LAST_INSERT_ID()

is per user and per connection.

You can read more in MySQL doc.

Lording

@Lording 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. – Isolt

Answer 3

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

Maremma

Answer 4

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!

Wait

Answer 5

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!!!

Elroy

Answer 6

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

Example:

SELECT LAST_INSERT_ID() FROM my_table;

Holding

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