Additional languages
« 24.2.14.2 What Results You Can Get from a Query
24.2.14.4 Problems Linking with the C API »
Section Navigation      [Toggle]

24.2.14.3. 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 non-magic 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

Note that 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:


User Comments

Posted by Juan Rios on June 8 2005 8:47pm[Delete] [Edit]

Well the reason i was reading this examples, was so i can get the next key the auto_increment was going to use.
But i had my own problems, I was using JAVA and I needed that when i just opened the program; without inserting anytihng yet.

So i needed to keep looking...What help me and may help you is the SHOW TABLE STATUS;

This show the complete status of the tables inside the currently used database; if you want to narrow it down to your a particular table you can use:

SHOW TABLE STATUS FROM DB_NAME LIKE 'TABLE_NAME';

Now to put it in JAVA, you use this code

//First Establisch Connection:

Connection connection = null;

String driver = "com.mysql.jdbc.Driver"; // Download mysql-connector-java.jar to connect from JAVA to MySQL
Class.forName( driver );

/* Test the connection */
// Create a connection to the database
String serverName = "localhost"; //Or your host
String mydatabase = "db_name";
String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url
String username = "scott";
String password = "tiger";
connection = DriverManager.getConnection(url, username, password);

Statement stmt = connection.createStatement();

// Create table called my_table
ResultSet data = stmt.executeQuery("show table status from db_name like 'table_name'");

if(data.next()) //To move to the first row, returns false if there is no mathces founds
data.getInt(11); //The 11th column, for the Next Row ID from Auto_Increment
} else {
System.out.println("Table not found");
}

this throws ClassNotFoundException and a SQLException

So make sure you place it inside of a try {} catch("Exceptions") {} block

Posted by Anthony Eden on August 19 2005 1:06am[Delete] [Edit]

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 4:03pm[Delete] [Edit]

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 Bob Armour on December 13 2005 12:24pm[Delete] [Edit]

Interesting technique used by Juan Rios - I have one question about it, though.

Why use...

getInt(11);

...when...

getInt("Auto_increment");

...would be more future proof?

Posted by Abhinayak Mishra on February 5 2006 6:41am[Delete] [Edit]

Interesting technique used by Juan Rios - but I think it would be a problem if more than one client tries to work on the database simultaneously...

The option that I find useful is to actually, insert a field like a temporary id first, get the last insert id and then use this for the whole of the execution cycle...which spans over a time of 10-15 minutes for my application.

This basically takes care of multiple accesses while still not locking the table in any manner....

Posted by Russell Levy on March 3 2006 2:41pm[Delete] [Edit]

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');
SELECT LAST_INSERT_ID();
will return 1, not 2.

Posted by Jason McManus on June 8 2006 12:49pm[Delete] [Edit]

I recently also ran into the above issue with LAST_INSERT_ID() returning apparently odd results from a multiple-insert query:

mysql> insert into wiseguys (name) values ('Jimmy Two Times'),('Freddie No Nose');
Query OK, 2 rows affected (0.00 sec)
mysql> select last_insert_id()\G
last_insert_id(): 6
mysql> select max(id) from wiseguys\G
max(id): 7

After searching a bit, there is more documentation on the behaviour, return value and its intent here:

http://lists.mysql.com/bugs/10604

As well as being officially and thoroughly documented here:

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
(under the LAST_INSERT_ID() section).

In further analysis, since the multiple insert statement *should* be atomic, assuming you are using an auto_increment column correctly, you should be able to calculate the last few row IDs inserted via the last_insert_id() and your language's implementation of affected_rows() or mysql_affected_rows(). This begs further testing, however.

Posted by jurgen DEBO on December 25 2006 1:17pm[Delete] [Edit]

Do NOT use it. You will have problems in a multi-user environment. Use a separate table to store counters for
OrderID, CustomerID, etc.

Posted by Joey on January 30 2007 5:16am[Delete] [Edit]

for the benefit of mankind.. please refer to the following..

http://dev.mysql.com/doc/refman/4.1/en/connector-j-usagenotes-basic.html#connector-j-examples-autoincrement-getgeneratedkeys
http://dev.mysql.com/doc/refman/4.1/en/connector-j-usagenotes-basic.html#connector-j-examples-execute-select

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 3:34am[Delete] [Edit]

The only safe way I've discovered to establish the proper value for an INSERT . . . SELECT "from/to the same table" is:

----------------------------------
START TRANSACTION;
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.

COMMIT;
----------------------------------


Posted by Luciano Fantuzzi on March 19 2007 5:35pm[Delete] [Edit]

Este es un ejemplo de un script que usa los metodos explicados arriba y una variable para almacenar una consulta temporal (con el mayor id) para luego ejecutar la consulta con el Insert/Select/Update... (usando el resultado de la variable como parametro). Es un metodo util para hacer todo en un solo script, sin la necesidad de revisar los resultados obtenidos y enviarlos nuevamente al servidor.

Here is an example of a simple script that uses the explained methods above and a variable to store the temporary query (with the highest id) and then run the Insert/Select/Update... query (using the result in the variable). Is an usefull method to do everything in only one step, instead checking and resending the results with another script.

SET @max_id=(SELECT max(id) from MyTable);
INSERT INTO MyTable(id,...) VALUES(@max_id+1,...);

Add your own comment.