MySQL Perl DBI last_insert

July 2, 2005

MySQL Perl DBI last_insert_id

Posted in: Tech

For some reason I couldn't get a quick answer on how to use last_insert_id(), just reams of P°H°P pages or out-of-date and/or driver-specific stuff e.g. $dbh->{'mysql_insert_id'}, so here's the answer:

$dbh->do('INSERT INTO a_table ...');
my $id = $dbh->last_insert_id(undef, undef, qw(a_table a_table_id))
    or die "no insert id?";

The two undefs are for catalog and schema which don't appear to be necessary. The DBI docs say they can be ignored for MySQL so you might not even need the table and field parameters. Doesn't hurt to programmatically document though.

Enjoy!

PS I'm only using MySQL as it's a legacy system. Of course, real men use sequences and PostgreSQL!

Posted by Paul Makepeace at July 2, 2005 19:39 | TrackBack

Comments

A much gentler solution with myql would be:
my $in_id = $dbh->{ q{mysql_insertid}};

and it works...

Posted by: faf at October 10, 2005 21:18

He clearly knows your suggestion is possible, faf, he mentions it in his opening sentence. The whole point of his post is to show people how to actually use the function, in what he proposes is a DB independent way.
Please, for the love of God, read before you write.

Posted by: perlmonkee at December 2, 2005 13:12

Thanks ... this saved me alot of time!

Posted by: Henry at June 29, 2006 16:36

Ditto, thanks for this blog post, this helped me figure out the last_insert_id function which isn't made very clear in the docs.

Posted by: Cyde Weys at June 19, 2007 00:31

Thanks. Such a simple and useful function, yet a pain to search for!

Posted by: Richard at April 6, 2008 23:36

guys..things are worked out..! thanks

Posted by: Karthik at April 12, 2008 04:48

Although his use of last_insert_id. If you start delving into the PODs and old mailinglists, you'll find out that last_insert_id() although portable, is less reliable than using something driver specific, eg $dbh->{'mysql_insert_id'}.

The one upside is that this isn't deprecated. officially, funcs like last insert id are very much Database dependent, and the onus is actually on the individual DBD authors to maintain exposure of these things.

Posted by: There's one problem at April 24, 2009 17:56

Thanks bro...
i gone through many other sites but ur solution worked.U saved my lots of time :)

Posted by: Bharat at February 23, 2012 13:10