Skip navigation links

User Comments

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 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 Joey Yew Joe 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 Lewis Roberts on February 26 2010 10:30pm[Delete] [Edit]

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.