How Can MySQL's ON DUPLICATE KEY UPDATE Handle Bulk Inserts and Updates Efficiently?

Optimizing Bulk Inserts and Updates in MySQL using ON DUPLICATE KEY UPDATE

Managing unique values during bulk inserts into MySQL tables can be complex. The ON DUPLICATE KEY UPDATE clause offers an efficient solution for simultaneously inserting new rows and updating existing ones based on unique key constraints.

This guide demonstrates how to insert or update rows in a table (e.g., "beautiful") where the "name" column acts as a unique key. Two approaches are presented:

For MySQL 8.0.19 and later versions:

Leverage row aliases for concise 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
    -- ... other columns to update ...</code>

For MySQL versions prior to 8.0.19:

Use the VALUES keyword to reference inserted values:

<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),
    -- ... other columns to update ...</code>

In both methods, ON DUPLICATE KEY UPDATE specifies which columns to modify if a duplicate unique key is detected. This ensures data integrity while efficiently handling both insertions and updates in a single operation.

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