Posted by & filed under MySQL.

Recently, while working on ObsceneArt, we had the need to quickly either insert new data into a database, or update an existing record, specifically used for the rating system. For example, when a user would rate a quote, they might have already rated it once and would like to change their mind, or they would like to rate it for the first time. Of course, doing a lookup to see if the record exists already and then either updating or inserting would be an expensive process (existing items are defined either by a unique key or a primary key). Luckily, MySQL offers two functions to combat this (each with two very different approaches).

The first method is REPLACE. The syntax is the same as the INSERT function. When dealing with a record with a unique or primary key, REPLACE will either do a DELETE and then an INSERT, or just an INSERT. Now, this most likely isn’t the solution you need. This function will cause a record to be removed, and inserted at the end, which will cause the indexing to get broken apart, decreasing the efficiency of your table. If, however, you would require a different primary key with the new data, this may be your ideal solution. Here is the syntax:

REPLACE INTO
  ratings
SET
  quote_id = 100,
  user_id = 200,
  score = 10

The second method is the ON DUPLICATE KEY UPDATE clause to the INSERT function. This one actively hunts down an existing record in the table which has the same UNIQUE or PRIMARY KEY as the one we’re trying to update. If it finds an existing one, you specify a clause for which column(s) you would like to UPDATE. Otherwise, it will do a normal INSERT. Here’s the syntax:

INSERT INTO
  ratings
SET
  quote_id = 100,
  user_id = 200,
  score = 10
ON DUPLICATE KEY UPDATE
  score = 10

2 Responses to “MySQL REPLACE vs INSERT ON DUPLICATE KEY UPDATE”

  1. Larry

    You just made my life so much better, just be your being knowledgeable and smart. You cannot imagine the frustration this is going to save me in the future. Thank you!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>