Can I use always INSERT ....ON DUPLICATE UPDATE for simple updates/inserts?

(MYSQL) Is there any significant performance differences, or other reasons not to use the INSERT ... ON DUPLICATE UPDATE sql to开发者_JAVA技巧 update by PK/insert new stuff?

No, there is none.

INSERT ON DUPLICATE KEY UPDATE will locate the record and update it just as a simple UPDATE would do.

In fact this is just a UPDATE, followed by an INSERT should the UPDATE fail.

INSERT ON DUPLICATE KEY UPDATE can be a faster alternative to a grouped update (when you have a source table with multiple records per key and want to increment the target table once per record).

If there are few values per key, this query:

INSERT
INTO    t_target
SELECT  target, cnt
FROM    t_sparse d
ON DUPLICATE KEY UPDATE
        t_target.cnt = t_target.cnt + d.cnt

will be more efficient than this one:

INSERT
INTO    t_target
SELECT  target, cnt
FROM    (
        SELECT  target, SUM(cnt) AS cnt
        FROM    t_sparse di
        GROUP BY
                target
        ) d
ON DUPLICATE KEY UPDATE
        t_target.cnt = t_target.cnt + d.cnt

See this article in my blog for more detail:

• Efficient INSERT ON DUPLICATE KEY UPDATE

I would like to give a more nuanced answer. It depends on the implementation of the DBMS. Nevertheless, from experience I can tell you that most DBMS have some pretty good support for INSERT ... ON DUPLICATE UPDATE.

Actually, it can often lead to better performance to use INSERT ... ON DUPLICATE UPDATE since the DMBS can group these commands, resulting in less work to be done. This is the case with mysql and you can often see some 10 to 20% increase in speed compare to a simple insert + update strategy without any kind of batching. Overall I would recommend the INSERT ... ON DUPLICATE UPDATE because it is simpler to use, your code is easier to maintain and it often leads to an additional speed benefit.