How Can I Efficiently Perform Upserts in MySQL?

MySQL Upsert operation: update if it exists, insert if it does not exist

Question:

In MySQL, is there an efficient way to perform an "Upsert" operation, i.e. insert a new row if it does not exist, and update the existing row if it exists?

Answer:

Yes, MySQL provides the INSERT ... ON DUPLICATE KEY UPDATE syntax to achieve this functionality. Here's an example:

Example:

<code class="language-sql">INSERT INTO `usage`
(`thing_id`, `times_used`, `first_time_used`)
VALUES
(4815162342, 1, NOW())
ON DUPLICATE KEY UPDATE
`times_used` = `times_used` + 1</code>

Instructions:

The
• INSERT INTO clause attempts to insert a new row with the specified value.
The
• ON DUPLICATE KEY UPDATE clause defines what should happen if a unique key conflict occurs (i.e. if a row with the same thing_id already exists).
• In this example, the existing row is updated by adding 1 to the times_used column.

Advantages:

• Efficient: Perform Upsert operations through a single query, reducing the number of database round-trips.
• Safety: Smoothly handle duplicate row conflicts and ensure data integrity.
• Flexible: can be used to insert new rows, update existing rows, or both.

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