The REPLACE [INTO] syntax allows us to INSERT a row into a table, except that if a UNIQUE KEY (including PRIMARY KEY) violation occurs, the old row is deleted prior to the new INSERT, hence no violation.
Sounds very attractive, and has a nice syntax as well: the same syntax as a normal INSERT INTO’s. It certainly has a nicer syntax than INSERT INTO … ON DUPLICATE KEY UPDATE, and it’s certainly shorter than using a SELECT to see if a row exists, then doing either INSERT or UPDATE.
But weak hearted people as myself should be aware of the following: it is a heavyweight solution. It may be just what you were looking for in terms of ease of use, but the fact is that on duplicate keys, a DELETE and INSERT are performed, and this calls for a closer look.
Whenever a row is deleted, all indexes need to be updated, and most importantly the PRIMARY KEY. When a new row is inserted, the same happens. Especially on InnoDB tables (because of their clustered nature), this means much overhead. The restructuring of an index is an expensive operation. Index nodes may need to be merged upon DELETE. Nodes may need to be split due to INSERT. After many REPLACE INTO executions, it is most probable that your index is more fragmented than it would have been, had you used SELECT/UPDATE or INSERT INTO … ON DUPLICATE KEY
Also, there’s the notion of “well, if the row isn’t there, we create it. If it’s there, it simply get’s updated”. This is false. The row doesn’t just get updated, it is completely removed. The problem is, if there’s a PRIMARY KEY on that table, and the REPLACE INTO does not specify a value for the PRIMARY KEY (for example, it’s an AUTO_INCREMENT column), the new row gets a different value, and this may not be what you were looking for in terms of behavior.
Many uses of REPLACE INTO have no intention of changing PRIMARY KEY (or other UNIQUE KEY) values. In that case, it’s better left alone. On a production system I’ve seen, changing REPLACE INTO to INSERT INTO … ON DPLICATE KEY resulted in a ten fold more throughput (measured in queries per second) and a drastic decrease in IO operations and in load average.
@Jeremy,
I think you’re looking for what’s “known” as the UPSERT statement. There isn’t one.
Really, the SQL syntax, as defined by ANSI SQL, is far from being friendly.
Jeremy: You can easy reuse values in the UPDATE part from the INSERT.
INSERT INTO objects (val1, val2, val3, val4) VALUES (1, 2, 3, 4) ON DUPLICATE KEY UPDATE val1=VALUES(val1), val2=VALUES(val2), val3=VALUES(val3), val4=VALUES(val4);
http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_values
@Z
How is that any better? It’s still a lot of extra typing.
@Z,
I know about the val1=VALUES(val1), val2=VALUES(val2), val3=VALUES(val3), val4=VALUES(val4); option, however this increases the size of the SQL dramatically. It’s not uncommon for there to be tables with 100+ columns, updating all of those columns would require my application to generate SQL statements that are many times longer, which slows down the query generation, and increases the time it takes to parse the query on the other end. It’s not necessarily extra ‘typing’, but it’s just inefficient all the way around, especially when you need to update many rows at a time.
Nice article. REPLACE INTO is dangerous, as it can actually cause you to lose data unpredictably. This has happened to me before. Didn’t know about INSERT INTO .. ON DUPLICATE KEY UPDATE before this article. Seems okay, but I don’t know. I like to update and then do an insert if no rows were affected. Silly, but not dangerous.