How Can I Efficiently Perform Upserts in MySQL with Duplicate Key Handling?

Efficient Upsert Query for Duplicate Key Update

When inserting data into a MySQL table with a unique index, it's often necessary to update an existing record if the key already exists. To accomplish this, an "ON DUPLICATE KEY UPDATE" clause is typically used, specifying the fields to be modified. However, specifying all the fields again can be inefficient.

Alternative Syntax

A simpler approach is to use the VALUES() function, as shown below:

INSERT INTO table (id,a,b,c,d,e,f,g) VALUES (1,2,3,4,5,6,7,8) 
ON DUPLICATE KEY UPDATE a=VALUES(a),b=VALUES(b),c=VALUES(c),d=VALUES(d),e=VALUES(e),f=VALUES(f),g=VALUES(g);

This syntax avoids the need to specify each field twice, making the query more concise.

Consider Avoiding Updates

It's important to note that updating fields with the same values they already have is unnecessary. If the existing record already contains the same data as the insert, there's no need to perform an update. The following syntax can be used to prevent unnecessary updates:

INSERT INTO table (id,a,b,c,d,e,f,g)
VALUES (1,2,3,4,5,6,7,8) 
ON DUPLICATE KEY
    UPDATE a=a, b=b, c=c, d=d, e=e, f=f, g=g;

Retrieving the Last Insert ID

To obtain the ID of the newly inserted or updated record, the syntax depends on the backend app being used. For example, in LuaSQL, the conn:getlastautoid() function can be used to retrieve the value.

The above is the detailed content of How Can I Efficiently Perform Upserts in MySQL with Duplicate Key Handling?. For more information, please follow other related articles on the PHP Chinese website!