Insert mit Rückgabe der ID

[hotti]

Hi,

Tabelle in MySQL 5.1.51 :

  
			CREATE TABLE user(  
				id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,  
				user_name VARCHAR(50) NOT NULL DEFAULT '',  
				user_id VARCHAR(50) NOT NULL DEFAULT '',  
				PRIMARY KEY (id),  
				UNIQUE KEY ident(user_name, user_id)  
			)DEFAULT CHARSET=UTF8  

Meine Funktion, die noch zu schreiben ist, soll bei einem Insert, die neue id und für den Fall, dass der Record aufgrund des Unique Key bereits vorhanden ist, die vorhandene id zurückgeben.

Ich könnte das mit Perl also abfragen, aber diese Art und Weise gefällt mir nicht so recht, möglicherweise geht das auch performanter und möglicherweise gibt es auch die Möglichkeit, dies von der DB-Engine erledigen zu lassen?

Konkret: Wie kriege ich MySQL dazu, mir die id rauszugeben, wenn ident bereits vorhanden ist?

Viele Grüße,
Hotti

[Tom]

Hello,

Tabelle in MySQL 5.1.51 :

  	CREATE TABLE user(  
  		id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,  
  		user_name VARCHAR(50) NOT NULL DEFAULT '',  
  		user_id VARCHAR(50) NOT NULL DEFAULT '',  
  		PRIMARY KEY (id),  
  		UNIQUE KEY ident(user_name, user_id)  
  	)DEFAULT CHARSET=UTF8  

>   
> Meine Funktion, die noch zu schreiben ist, soll bei einem Insert, die neue id und für den Fall, dass der Record aufgrund des Unique Key bereits vorhanden ist, die vorhandene id zurückgeben.  
  
Hast Du es schon mit REPLACE und "On DUPLICATE KEY UPDATE" ausprobiert?  
<http://dev.mysql.com/doc/refman/5.1/de/replace.html>  
Was steht dann hinterher in LAST\_INSERT\_ID() drin?  
<http://dev.mysql.com/doc/refman/5.1/en/information-functions.html>  
  
  
  
Liebe Grüße aus dem schönen Oberharz  
  
  
Tom vom Berg  
![](http://selfhtml.bitworks.de/Virencheck.gif)  
  

-- 
 ☻\_  
/▌  
/ \ Nur selber lernen macht schlau  
<http://bergpost.annerschbarrich.de>

[hotti]

hi Tom,

Hast Du es schon mit REPLACE und "On DUPLICATE KEY UPDATE" ausprobiert?

Proprietär und unperformant ;)

Überraschung:
Benchmark: timing 100000000 iterations of New Way, Old Way...
   New Way:  1 wallclock secs ( 0.30 usr +  0.00 sys =  0.30 CPU) @ 336700336.70/s (n=100000000)
            (warning: too few iterations for a reliable count)
   Old Way:  2 wallclock secs ( 1.77 usr +  0.00 sys =  1.77 CPU) @ 56657223.80/s (n=100000000)
               Rate Old Way New Way
Old Way  56657224/s      --    -83%
New Way 336700337/s    494%      --

Viele Grüße,
Horst ;)

PS: Perl-Code zum Nachvollziehen:

  
  
use strict;  
use warnings;  
use DBI;  
use Data::Dumper;  
use Benchmark qw(cmpthese);  
  
my $DBH = dbh() or die $@;  
  
  
#create('user') || drop('user');  
  
# Statements  
my $STH_IGNORE = $DBH->prepare("INSERT IGNORE INTO user(user_id, user_name)VALUES(?,?)");  
my $STH_INSERT = $DBH->prepare("INSERT INTO user(user_id, user_name)VALUES(?,?)");  
my $STH_TEST   = $DBH->prepare("SELECT id FROM user WHERE user_id=? AND user_name=?");  
  
my $user_id = 'hh10';  
my $user_name = 'Horst Haselhuhn';  
my $id = undef;  
  
#my $id = insert_ignore({user_id => $user_id, user_name => 'Horst Haselhuhn'}) or die $@;  
#print "Old Way: $id\n";  
  
#$id = insert({user_id => $user_id, user_name => $user_name}) or die $@;  
#print "New Way: $id\n";  
  
# Jetzt machen wir mal ein Benchmark  
cmpthese(100000000, {  
        'Old Way' => insert({user_id => $user_id, user_name => $user_name}),  
        'New Way' => insert_ignore({user_id => $user_id, user_name => $user_name}) ,  
});  
  
  
# Herkömmlicher Weg  
sub insert{  
	my $r = shift;  
	my @args = ($r->{user_id}, $r->{user_name});  
	my $id = '0E0';  
	eval{  
		$STH_TEST->execute(@args);  
		my $id_ist = $STH_TEST->fetchrow_array;  
		if($id_ist){  
			#print "DRIN: $id_ist\n";  
			$id = $id_ist;  
		}  
		else{  
			$STH_INSERT->execute(@args);  
			$id = $DBH->selectrow_array("SELECT LAST_INSERT_ID()");  
		}  
	};  
	return $@ ? undef : $id;	  
}  
  
  
# Neuer Weg mit INSERT IGNORE  
sub insert_ignore{  
	my $r = shift;  
	my @args = ($r->{user_id}, $r->{user_name});  
	my $id = '0E0';  
	eval{  
		my $rv = $STH_IGNORE->execute(@args); # IGNORE => 0E0, NEU => 1  
		if($rv == 0){  
			# 0E0, kein Fehler, Record schon drin  
			$id = $DBH->selectrow_array("SELECT id FROM user WHERE user_id=? AND user_name=?", {}, @args);  
			#print "Drin: $id\n";  
		}  
		else{  
			$id = $DBH->selectrow_array("SELECT LAST_INSERT_ID()");  
			#print "Neu: $id\n";  
		}  
	};  
	return $@ ? undef : $id;	  
}  
  
sub drop{  
	my $table_name = shift;  
	eval{ $DBH->do("DROP TABLE $table_name"); };  
	print "DROP\n";  
	return $@ ? undef : 1;  
}  
sub create{  
	my $table_name = shift;  
	my %qq = creates();  
	my $qtable = $qq{$table_name};  
	eval{ $DBH->do($qtable) };  
	print "CREATE\n";  
	return $@ ? undef : 1;  
}  
  
  
sub creates{  
	return (  
		user => q(  
			CREATE TABLE user(  
				id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,  
				user_name VARCHAR(50) NOT NULL DEFAULT '',  
				user_id VARCHAR(50) NOT NULL DEFAULT '',  
				PRIMARY KEY (id),  
				UNIQUE KEY ident(user_name, user_id)  
			)DEFAULT CHARSET=UTF8  
		),  
	);  
}  
###########################################################################  
sub dbh{  
	my %cfg = (  
		base => 'myweb',  
		host => 'localhost',  
		port => 3306,  
		user => '',  
		pass => '',  
  
	@_);  
	my $dbh = undef;  
	eval{  
		$dbh = DBI->connect("DBI:mysql:$cfg{base}:$cfg{host}:$cfg{port}", $cfg{user}, $cfg{pass},  
			{RaiseError => 1, PrintError => 0}  
		);  
	};  
	return $@ ? undef : $dbh;  
}  
  

[Tom]

Hello Hotti,

was willst Du denn nun eigentlich?

Soll der Datensatz ersetzt werden, wenn der Unique-Key schon vorhanden ist, oder soll er erhalten bleiben?

"Bei IGNORE wird der Datensatz zwar auch nicht eingefügt, aber es wird kein Fehler ausgegeben. "

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Linksetzender]

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

INSERT INTO table (a,b,c) VALUES (1,2,3)  
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

[Tom]

Hello,

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

INSERT INTO table (a,b,c) VALUES (1,2,3)

ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

  
Und was passiert mit Relationen bei diesem Statement?  
Sind die dann anschließend kaputt?  
  
  
  
  
Liebe Grüße aus dem schönen Oberharz  
  
  
Tom vom Berg  
![](http://selfhtml.bitworks.de/Virencheck.gif)  
  

-- 
 ☻\_  
/▌  
/ \ Nur selber lernen macht schlau  
<http://bergpost.annerschbarrich.de>

[hotti]

Hello,

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

INSERT INTO table (a,b,c) VALUES (1,2,3)

ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

>   
> Und was passiert mit Relationen bei diesem Statement?  
> Sind die dann anschließend kaputt?  
  
LAST\_INSERT\_ID() bezieht sich auf die Session mit der DB. Die Rückgabe dieser Funktion ist unabhängig davon, in welcher Relation ein auto\_increment-Wert erhöht wurde.  
  
Der Hack besteht nun darin, LAST\_INSERT\_ID(expr) zu verwenden, expr ist in meinem Fall das Feld 'id' und damit liefert LAST\_INSERT\_ID(id) die gewünschte id bei einem UPDATE.  
  
Es ist also unnötig, das Ermitteln der id über einen eigenen Code zu ziehen, wenn dies die Engine selbst tun kann.  
  
Schönen Sonntag,  
Hotti

[Tom]

Hello,

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

INSERT INTO table (a,b,c) VALUES (1,2,3)

ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

> >   
> > Und was passiert mit Relationen bei diesem Statement?  
> > Sind die dann anschließend kaputt?  
  
Ich habe das jetzt mal selber ausprobiert.  
  
    CREATE TABLE `duplicate\_test` (  
	`ID` INT(10) NOT NULL AUTO\_INCREMENT,  
	`name` VARCHAR(50) NOT NULL DEFAULT '',  
	`place` VARCHAR(50) NOT NULL DEFAULT '',  
	`number` INT(11) NOT NULL DEFAULT '1',  
	PRIMARY KEY (`ID`),  
	UNIQUE INDEX `name\_place` (`name`, `place`)  
    )  
    COLLATE='latin1\_swedish\_ci'  
    ENGINE=InnoDB;  
  
  
    insert into duplicate\_test set name='maria', place='rom'  
      on duplicate key update  
        id=LAST\_INSERT\_ID(id),  
        number=number+1;  
  
    select last\_insert\_id();  
  
Das funktioniert ja tatsächlich ganz hervorragend. Bei einem Update wird die ID des bereits vorhandenen Datensatzes zurückgegeben, somit bleibt die refenzielle Integrität also auch gewahrt.  
Beim Insert entsteht natürlich eine neue ID.  
  
Einzige Macke: es werden IDs vergeudet. Bei jedem Update wird eine neue ID verbraucht, aber (wie oben beschreiben) nicht im Datensatz eingetragen. Die nächste Autoincrement-ID ist dann ensprechend höher.  
  
Wozu das nun gut sein soll, oder ob es nur ein Bug ist, weiß ich noch nicht. Vielleicht hast Du ja eine Idee.  
  
  
  
  
Liebe Grüße aus dem schönen Oberharz  
  
  
Tom vom Berg  
![](http://selfhtml.bitworks.de/Virencheck.gif)  
  

-- 
 ☻\_  
/▌  
/ \ Nur selber lernen macht schlau  
<http://bergpost.annerschbarrich.de>

[Malcolm Beck´s]

مرحبا

insert into duplicate_test set name='maria', place='rom'
      on duplicate key update
        id=LAST_INSERT_ID(id),
        number=number+1;

Wofür ist das Feld number?

mfg

[Tom]

Hello E.

insert into duplicate_test set name='maria', place='rom'
      on duplicate key update
        id=LAST_INSERT_ID(id),
        number=number+1;

Wofür ist das Feld number?

Das war nur als Test, ob überhaupt etwas passiert. Es wird dann nur hochgezählt, wenn der Satz schon vorhanden war.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Malcolm Beck´s]

مرحبا

insert into duplicate_test set name='maria', place='rom'
      on duplicate key update
        id=LAST_INSERT_ID(id),
        number=number+1;

Wofür ist das Feld number?

Das war nur als Test, ob überhaupt etwas passiert. Es wird dann nur hochgezählt, wenn der Satz schon vorhanden war.

Hatte ich mir schon gedacht, Danke für die Erklärung.

mfg

[hotti]

hi Tom,

Wozu das nun gut sein soll, oder ob es nur ein Bug ist, weiß ich noch nicht. Vielleicht hast Du ja eine Idee.

Es soll weder ein Update erfolgen, noch eine neue ID bei vorhandenem Unique Key vergeben werden. Sinn und Zweck also:

Detailtabelle 'user' befüllen, wenn ident(user_id, user_name) bereits vorhanden sind, soll die (vorhandene)id zurückgegeben werden. Wenn ident() neu ist, soll ident(user_id, user_name) eingefügt werden und die (neue)id zurückgegeben werden.

Hier nochmal die Tabelle:

  
			CREATE TABLE user(  
				id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,  
				user_name VARCHAR(50) NOT NULL DEFAULT '',  
				user_id VARCHAR(50) NOT NULL DEFAULT '',  
				PRIMARY KEY (id),  
				UNIQUE KEY ident(user_name, user_id)  
			)DEFAULT CHARSET=UTF8  

Das Statement:

  
my $STH_INSERT_ON_DUPLICATE_KEY = $DBH->prepare(  
	q(  
		INSERT INTO user(user_id, user_name)VALUES(?,?)  
		ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)  
	)  
);  

und die Funktion:

  
sub insert_user{  
	state %cache; # 5.010  
	my $r = shift;  
	$cache{$r->{user_id}.$r->{user_name}} ||= eval{  
		# print "Anfrage\n"; # cache testen  
		$STH_INSERT_ON_DUPLICATE_KEY->execute($r->{user_id}, $r->{user_name});  
		$DBH->selectrow_array("SELECT LAST_INSERT_ID()");  
	};  
	return $@ ? undef : $cache{$r->{user_id}.$r->{user_name}};  
}  

LAST_INSERT_ID() hat hier eine Doppelrolle:
LAST_INSERT_ID()
LAST_INSERT_ID(expr)

Hotti

[hotti]

Ohne Cache und ohne Gewehr:

  
$conn = mysql_connect("localhost", '','') or die("Keine DB Verbindung");  
mysql_select_db("myweb", $conn) or die(mysql_error());  
  
$id = insert_user('hh22', 'Horst Haselhuhn');  
print $id;  
  
function insert_user($user_id, $user_name){  
	$st = "INSERT INTO user(user_id, user_name)VALUES('$user_id','$user_name')";  
	$st .= "ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)";  
	mysql_query($st);  
	$res = mysql_query("SELECT LAST_INSERT_ID() as myid");  
	$id = mysql_fetch_assoc($res);  
	return $id['myid'];  
}  

Horst Kopfschuss ;)

[hotti]

hi danke!!!

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

INSERT INTO table (a,b,c) VALUES (1,2,3)

ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

  
Das gefällt mir, ich werde mal die notwendigen Benchmarks machen, prinzipiell funktioniert es mit der Rückgabe der id in beiden Fällen.  
  
Wenn es produktive geht, wird es dazu ein prepared Statement geben und das Ganze wird außerdem noch gechached.  
  
Viele Grüße,  
Hotti

-- 
Wozu das Rad neu erfinden? Lasst uns Raketen bauen!