Here's a working example. It does return the id either of the new record or the existing record. Unfortunately it also appears to change the auto-increment value with every update (even when no new records are inserted) so id's may not be continuous.
From the mysql docs : In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.
#!\C:\Strawberry\perl\bin\perl
use strict;
use warnings;
use DBI;
# CREATE TABLE test2 (
# id int(11) NOT NULL AUTO_INCREMENT,
# f1 varchar(45) DEFAULT NULL,
# PRIMARY KEY (id),
# UNIQUE KEY f1_UNIQUE (f1)
# ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
my $table = 'test2';
my $col = 'f1'; # unique key
my $entry = $ARGV[0] || 1;
#PERL DBI CONNECT
my $dbh = dbh();
my $sql = sprintf '
INSERT INTO %s (%s)
VALUES (?)
ON DUPLICATE KEY
UPDATE id = LAST_INSERT_ID(id)',
$dbh->quote_identifier($table),
$dbh->quote_identifier($col) ;
my $sth = $dbh->prepare($sql);
my $rv = $sth->execute($entry);
print $rv.' '.$dbh->last_insert_id(undef,undef,$table,$col);
sub dbh{
my $database = "test"; my $user = "user"; my $pw = "password";
my $dsn = "dbi:mysql:$database:localhost:3306";
my $dbh = DBI->connect($dsn, $user, $pw,
{ RaiseError=>1, AutoCommit=>1 } );
return $dbh;
}
poj |