SELECT LAST_INSERT_ID()
java, last-insert-id, mybatis, mysql, sql
Solution
`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:
- You provide the explicit value for the `AUTO_INCREMENT` column
- You call `LAST_INSERT_ID` in another session
- 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).
Problem
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 ```