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
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:
For information on
LAST_INSERT_ID()
, which can
be used within an SQL statement, see
Section 11.11.3, “Information Functions”.
For information on
mysql_insert_id()
, the
function you use from within the C API, see
Section 20.9.3.37, “mysql_insert_id()
”.
For information on obtaining the auto-incremented value when using Connector/J, see Section 20.3.5, “Connector/J Notes and Tips”.
For information on obtaining the auto-incremented value when using Connector/ODBC, see Section 20.1.7.1.1, “Obtaining Auto-Increment Values”.
User Comments
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;
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.
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.
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()
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;
----------------------------------
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();
or
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.
Add your own comment.