How to Perform Bulk Inserts with ON DUPLICATE KEY UPDATE in MySQL?

Detailed explanation of MySQL batch insert and ON DUPLICATE KEY UPDATE

When inserting multiple rows of data at the same time in MySQL, you may need to check whether there are duplicate values ​​in the unique key, and perform update or insert operations according to the situation.

To do this, you can use the ON DUPLICATE KEY UPDATE clause. However, for multi-row data, how this is implemented depends on your MySQL version.

MySQL 8.0.19 and above

For versions 8.0.19 and above, you can use an alias to identify the rows you want to update:

<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>

MySQL 8.0.19 or below

For earlier versions, you can use the VALUES keyword to specify the values ​​of the row to be updated:

<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>

With the above method, you can perform batch update or insert operations using a single query, ensuring data integrity, updating existing rows and inserting new rows when necessary.

The above is the detailed content of How to Perform Bulk Inserts with ON DUPLICATE KEY UPDATE in MySQL?. For more information, please follow other related articles on the PHP Chinese website!