Rusty Razor Blade
REPLACE INTO will actually perform a delete and then an insert, while INSERT … ON DUPLCIATE KEY UPDATE will perform an update (as the name suggests). I would think the latter would be faster. I have not done any performance testing between the two, but it only seems logical the update would be faster than the delete/insert. Please correct me if I’m wrong.
Since the two statements both end up with the same result, I’m not sure yet what the benefit of REPLACE into it.
Yes, I realize both are MySQL additions, so please don’t bother telling me they’re not ANSI standard.
References:
If you enjoyed this article, please consider sharing it!
Purely hypothetical, but…
CREATE TABLE foo (
mykey INT PRIMARY KEY,
cola CHAR(1) DEFAULT ‘A’,
colb CHAR(2) DEFAULT ‘B’
);
INSERT INTO foo (mykey, cola, colb)
VALUES (1, ‘X’, ‘Y’), (2, ‘X’, ‘Y’);
INSERT INTO foo (mykey, cola) VALUES (1, ‘Z’) ON DUPLICATE KEY UPDATE cola=’Z';
REPLACE INTO foo (mykey, cola) VALUES (2, ‘Z’);
We should now have (1, ‘Z’, ‘Y’), (2, ‘Z’, ‘B’). Of course, this could still be done with UPDATE by appending colb=DEFAULT. The maintainability of that would decrease though. Not that relying on REPLACE to set fields back to their default values is very maintainable either.
Real world case? Nope, can’t think of one either.
REPLACE is dangerous with InnoDB and cascading DELETEs if something references that row. It will also change the auto_increment of the record.
ON DUPLICATE also allows for more freedom because, while you may opt to update with the values of the INSERT clause you are not tied down to that. You can use any kind of complex expression in order to achieve different results upon INSERT or UPDATE
FYI update is not always faster! sometimes a DELETE followed by an INSERT may actually be faster.
I use DUPLICATE very often, yet I have yet to find use for REPLACE in an environment that enforces referential integrity
Think you don’t have a primary key and want to replace a tuple which might exist (or not) in that table …
They’re not equivalent; ODKU allows you to specify exactly which fields get updated, where REPLACE wipes out all existing data and makes a new row. Sometimes you want this, for example when you have a timestamp column and don’t want to explicitly set it. Most queries can be written with either syntax, but in practice sometimes I find one or the other solves the problem better. I generally do not use REPLACE.