sql - How to translate PostgreSQL "merge_db" (aka upsert) function into MySQL -


straight manual, here's canonical example of merge_db in postgresql:

create table db (a int primary key, b text); create function merge_db(key int, data text) returns void $$ begin loop -- first try update key update db set b = data = key; if found return; end if; -- not there, try insert key -- if else inserts same key concurrently, -- unique-key failure begin insert db(a,b) values (key, data); return; exception when unique_violation -- nothing, , loop try update again. end; end loop; end; $$ language plpgsql; select merge_db(1, 'david'); select merge_db(1, 'dennis');

can expressed user-defined function in mysql, , if so, how? there advantage on mysql's standard insert...on duplicate key update?

note: i'm looking user-defined function, not insert...on duplicate key update.

tested on mysql 5.5.14.

create table db (a int primary key, b text); delimiter // create procedure merge_db(k int, data text) begin declare done boolean; repeat begin -- if there unique key constraint error -- made concurrent insert. reset sentinel -- , try again. declare er_dup_unique condition 23000; declare continue handler er_dup_unique begin set done = false; end; set done = true; select count(*) @count db = k; -- race condition here. if concurrent insert made after -- select before insert below we'll duplicate -- key error. handler above take care of that. if @count > 0 update db set b = data = k; else insert db (a, b) values (k, data); end if; end; until done end repeat; end// delimiter ; call merge_db(1, 'david'); call merge_db(1, 'dennis');

some thoughts:

  • you can't update first , check @row_count() because returns number of rows changed. 0 if row has value trying update.
  • also, @row_count() not replication safe.
  • you use replace...into.
  • if using innodb or table transaction support might able use select...for update (untested).

i see no advantage solution on using insert...on duplicate key update.


Comments

Post a Comment

Popular posts from this blog

javascript - backbone.js Collection.add() doesn't `construct` (`initialize`) an object -

<!doctype html public "-//w3c//dtd xhtml 1.0 strict//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-strict.dtd"> <html> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script> <script src="http://documentcloud.github.com/underscore/underscore-min.js"></script> <script src="http://documentcloud.github.com/backbone/backbone-min.js"></script> </head> <body> <button id="cmd_create_event" name="cmd_create_event" type="button">create new `event`</button> <script type="text/javascript"> var eventmodel = backbone.model.extend({ initialize : function() { console.log("`event` initialized. id: " + this.cid); this.bind("change:status", function() { console.log(this.get("status") + " value `status`"); }); this.bind("error", function(model, e...
Read more

php - Get uncommon values from two or more arrays -

is there function in php give array of uncommon values 2 or more arrays? for example: $array1 = array( "green", "red", "blue"); $array2 = array( "green", "yellow", "red"); .... $result = function_needed($array1, $array2,...); print_r($result); should give output: array("blue", "yellow", ...); use array_diff , array_merge : $result = array_merge(array_diff($array1, $array2), array_diff($array2, $array1)); here's demo. for multiple arrays, combine callback , array_reduce : function unique(&$a, $b) { return $a ? array_merge(array_diff($a, $b), array_diff($b, $a)) : $b; } $arrays = array( array('green', 'red', 'blue'), array('green', 'yellow', 'red') ); $result = array_reduce($arrays, 'unique'); and here's demo of that.
Read more

Adding duplicate array rows in Php -

i create function in php adds rows of array shared column value. so input. $test = array( array("c", 5, 6), array("c", 2, 3), array("test", 5, 6) ); and output. $testduplicatefree = array( array("c", 7, 9), array("test", 5, 6) ) ); i'm thinking function combine_duplicates($array,$col){ ... ... return $duplicatefreearray; } where $col duplicate free array. so, in case, combine_duplicates($test,0); would me desired output. this. <?php function combine_duplicates($array,$col) { $index = array(); foreach ($array $row) { $key = $row[$col]; if (!isset($index[$key])) { $index[$key] = $row; } else { ($i = 0; $i < count($row); ++$i) { if ($i != $col) { $index[$key][$i] += $row[$i]; } } } } return array_values($index); } $array = array( array("c", 5, 6), array("c", 2, 3), array("test", 5, 6) ); print_r(combine_duplicates($array, 0)); try here: http://codepad.org/mdhesqhi
Read more