How Can MySQL's ON DUPLICATE KEY UPDATE Handle Simultaneous Inserts and Updates of Multiple Rows?

MySQL's ON DUPLICATE KEY UPDATE: Efficiently Handling Concurrent Multi-Row Inserts and Updates

When inserting multiple rows into a MySQL table, situations arise where you need to check for the presence of a unique key (distinct from the primary key) before updating existing records. The ON DUPLICATE KEY UPDATE clause provides an elegant solution.

MySQL 8.0.19 and later versions offer improved syntax using aliases for inserted rows. This allows for cleaner, more readable updates:

<code class="language-sql">INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
    AS new
ON DUPLICATE KEY UPDATE
    age = new.age
    ...</code>

For MySQL versions prior to 8.0.19 (note that the VALUES method is deprecated as of 8.0.20), the following approach was necessary:

<code class="language-sql">INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
ON DUPLICATE KEY UPDATE
    age = VALUES(age),
     ...</code>

In both examples, if a unique key conflict arises during insertion, the affected row is updated with the provided values. Otherwise, a new row is added.

It's crucial to remember that VALUES within ON DUPLICATE KEY UPDATE doesn't reference the existing row's value, but rather the corresponding column value from the attempted new row insertion.

The above is the detailed content of How Can MySQL's ON DUPLICATE KEY UPDATE Handle Simultaneous Inserts and Updates of Multiple Rows?. For more information, please follow other related articles on the PHP Chinese website!