Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  How to Get the Unique ID for the Last Inserted Row How to Get the Unique ID for the Last Inserted Row

If you insert a record into a table that contains an AUTO_INCREMENT column, you can obtain the value stored into that column by calling the mysql_insert_id() function.

You can check from your C applications whether a value was stored in an AUTO_INCREMENT column by executing the following code (which assumes that you've checked that the statement succeeded). It determines whether the query was an INSERT with an AUTO_INCREMENT index:

if ((result = mysql_store_result(&mysql)) == 0 &&
    mysql_field_count(&mysql) == 0 &&
    mysql_insert_id(&mysql) != 0)
    used_id = mysql_insert_id(&mysql);

When a new AUTO_INCREMENT value has been generated, you can also obtain it by executing a SELECT LAST_INSERT_ID() statement with mysql_query() and retrieving the value from the result set returned by the statement.

When inserting multiple values, the last automatically incremented value is returned.

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.

If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like this:

INSERT INTO foo (auto,text)
    VALUES(NULL,'text');         # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
    VALUES(LAST_INSERT_ID(),'text');  # use ID in second table

mysql_insert_id() returns the value stored into an AUTO_INCREMENT column, whether that value is automatically generated by storing NULL or 0 or was specified as an explicit value. LAST_INSERT_ID() returns only automatically generated AUTO_INCREMENT values. If you store an explicit value other than NULL or 0, it does not affect the value returned by LAST_INSERT_ID().

For more information on obtaining the last ID in an AUTO_INCREMENT column:

Download this Manual
User Comments
  Posted by Anthony Eden on August 19, 2005
If you want store the last id for use in multiple insert statements:

create table person (
id bigint not null primary key auto_increment,
name char(100) not null
insert into person set name = 'Joe';
select @id:=id as id from class where id = last_insert_id();
insert into some_other_table set person_id = @class_id;
insert into yet_another_table set person_id = @class_id;
  Posted by Matt Lavallee on October 2, 2005
It's worth noting that LAST_INSERT_ID() does not work through connectors if the "Use Concurrent Inserts" is enabled in MyISAM.

I spent a dizzying amount of time troubleshooting to find that out.
  Posted by Russell Levy on March 3, 2006
LAST_INSERT_ID() will return the first id from a multi row insert. For example,

create table person (
id bigint not null primary key auto_increment,
name char(100) not null

INSERT INTO person (name) VALUES ('first'), ('second');
will return 1, not 2.
  Posted by Joey Yew Joe on January 30, 2007
for the benefit of mankind.. please refer to the following..

keywords : how to get index after insert, select @@identity from mysql, mysql_insert_id() in sql, mysql_insert_id() in java, jsp, mysql_last_id()
  Posted by J Walker on February 14, 2007
The only safe way I've discovered to establish the proper value for an INSERT . . . SELECT "from/to the same table" is:

SELECT id FROM aircraft_main ORDER BY id DESC LIMIT 1;

add one (1) to that AUTO_INCREMENT id value.

do your INSERT . . . SELECT using a literal for the AUTO_INCREMENT field and list all the other data fields - twice.


  Posted by Lewis Roberts on February 26, 2010
I'm in the middle of developing a database for a small online shop that has products with many options and many option values (bespoke blinds) so this will no doubt come in handy when I actually get the to the point of building the procedures and transactions. Glad I found it!

Two ways of storing the last auto_increment value as a user variable.

SET @lastid = LAST_INSERT_ID();
SELECT @lastid2 := LAST_INSERT_ID();

As mentioned in previous comments, although it is of course possible to do multiple inserts in one hit, it's perhaps safer for data integrity to be generous with your SQL INSERTs. Obviously this increases load but I'd rather know my data was correct and put another 16GB of memory in my server than deal with the alternative.
Sign Up Login You must be logged in to post a comment.