Welcome to WebmasterWorld Guest from 34.204.189.171

Forum Moderators: open

MySQL and Perl, getting the PRIMARY after an INSERT IGNORE

     
2:28 am on May 3, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1193
votes: 119


I'm using Perl with the DBI module to insert a row to a table. The table has 3 columns: id (which is a PRIMARY and autoincrement), then colA (unique) and colB (text).

$dbh->do("INSERT IGNORE INTO table (colA, colB) VALUES (?, ?), undef,
'foo',
'bar') or die "Couldn't execute INSERT INTO table: " . $dbh->errstr;


What I want to find is either the new ID if the insert happens, or the old matching one if it didn't.

The only way that I can think to do this is with a second query:

$dbh->do("INSERT IGNORE INTO table (colA, colB) VALUES (?, ?), undef,
'foo',
'bar') or die "Couldn't execute INSERT INTO table: " . $dbh->errstr;

$id = $dbh->{'mysql_insertid'};

if (!$id) {
my ($id) =
$dbh->selectrow_array("SELECT id FROM table WHERE colA = ? LIMIT 1", undef,
'foo') or die "Couldn't execute SELECT FROM table: " . $dbh->errstr;
}


Can you guys and gals suggest a way to get the ID without the second query? In production I'll be doing 4 separate inserts (with each one referring to an ID from a previous insert), so doing it this way would be a potential of 8 queries!
10:33 am on May 3, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 19, 2002
posts:3505
votes: 82


i don't know perl, but you should be able to return last_insert_id() from your mysql
which will give you the newID (assuming it's a primary key) and if none was inserted it should return 0 or NULL, that way you could test for this and if it was zero/null then you'd need to run a second query but if it returned another value then you'd have your id value without running another query.
not sure if this helps you.
8:53 pm on May 3, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2643
votes: 6


Another option is to create a sequence table rather than using auto increment and pull the ids from it ahead of the insert.

Basically you create a function that will get "nextval" for your ids and then increment the sequence when you ask for it.

That way you get the id of the row you will be inserting before inserting and then pass that off to all your CREATE or UPDATE statements.

This is something PostgreSQL has built in, but I have used it in MySql previously with a function and it works great. Saves you a couple trips to the DB and makes rolling things back easier as you can put all your inserts in a single transaction because you know the id, and if there is an error for any of the inserts it will all roll back and you won't end up with orphaned or non related records you are expecting to be there.

This guy has a great guide online for setting it up:
[convert-in.com...]