MySQLで重複レコードを賢く処理！INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATEのしくみとサンプルコード

MySQLのINSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE構文は、データ挿入と更新を効率的に行うための便利な機能です。この構文は、挿入しようとするデータが既存のレコードと重複する場合、既存レコードを更新する動作を行います。これにより、データの整合性を保ちながら、重複データの挿入を防ぐことができます。

構文

INSERT INTO table_name (column1, column2, ...)
SELECT value1, value2, ...
FROM table_name2
ON DUPLICATE KEY UPDATE
column1 = new_value1,
column2 = new_value2,
...;

説明

• table_name: 挿入先のテーブル名
• column1, column2: 挿入する列名
• value1, value2: 挿入する値
• column1 = new_value1, column2 = new_value2: 重複レコードが存在する場合に更新する列名と値

例

次の例では、customersテーブルにデータが存在する場合、email列が一致するレコードを更新し、存在しない場合は新しいレコードを挿入します。

INSERT INTO customers (email, name, address)
SELECT email, name, address
FROM new_customers
ON DUPLICATE KEY UPDATE
name = new_customers.name,
address = new_customers.address;

注意事項

• ON DUPLICATE KEY UPDATE句は、UNIQUEインデックスまたはPRIMARY KEYに重複するレコードが存在する場合のみ実行されます。
• INSERT INTO ... SELECT FROM ...構文と組み合わせて使用する場合、SELECT句で取得した列名がINSERT句で指定した列名と一致する必要があります。
• ON DUPLICATE KEY UPDATE句で更新する列は、INSERT句で挿入する列とは異なる場合があります。

メリット

• データの整合性を保ちながら、重複データの挿入を防ぐことができる
• データ挿入と更新を1つのステートメントで実行できるため、効率的である
• 複雑な条件分岐を記述する必要がない

• SELECT句で取得したデータ量が多い場合、処理時間が長くなる可能性がある
• 重複レコードの更新処理が発生するため、パフォーマンスに影響を与える可能性がある

INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE構文は、データ挿入と更新を効率的に行うための便利な機能です。データの整合性を保ちながら、重複データの挿入を防ぎたい場合に有効です。ただし、処理時間やパフォーマンスへの影響に注意する必要があります。

INSERT INTO customers (email, name, address)
SELECT email, name, address
FROM new_customers
ON DUPLICATE KEY UPDATE
name = new_customers.name,
address = new_customers.address;

例2：商品在庫の更新

この例では、productsテーブルに商品情報が存在する場合、product_id列が一致するレコードの在庫数を更新します。

INSERT INTO products (product_id, quantity)
SELECT product_id, quantity
FROM new_products
ON DUPLICATE KEY UPDATE
quantity = quantity + new_products.quantity;

例3：注文履歴の挿入と更新

INSERT INTO orders (order_id, customer_id, product_id, status)
SELECT order_id, customer_id, product_id, status
FROM new_orders
ON DUPLICATE KEY UPDATE
status = new_orders.status;

これらの例はあくまでも基本的な使用方法を示したものです。実際の使用例では、状況に合わせてクエリを調整する必要があります。

補足

• サンプルコードでは、ON DUPLICATE KEY UPDATE句で更新する列のみを記述しています。すべての列を更新したい場合は、*ワイルドカードを使用できます。

MySQLにおける「INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE」の代替方法

MERGE句を使用する

MySQL 5.6以降では、MERGE句を使用して、挿入と更新を1つのステートメントで実行することができます。MERGE句は、INSERT句とUPDATE句の機能を組み合わせたものであり、より柔軟なデータ操作が可能です。

MERGE INTO table_name AS t
USING table_name2 AS s
ON t.id = s.id
WHEN MATCHED THEN
UPDATE SET
t.column1 = s.column1,
t.column2 = s.column2
...
WHEN NOT MATCHED THEN
INSERT (column1, column2, ...)
VALUES (value1, value2, ...);

• id: 結合条件となる列名

MERGE INTO customers AS t
USING new_customers AS s
ON t.email = s.email
WHEN MATCHED THEN
UPDATE SET
t.name = s.name,
t.address = s.address
WHEN NOT MATCHED THEN
INSERT (email, name, address)
VALUES (s.email, s.name, s.address);

• INSERT句とUPDATE句を1つのステートメントで記述できるため、可読性が高い
• ON DUPLICATE KEY UPDATE句よりも柔軟なデータ操作が可能

• MERGE句はINSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE構文よりも新しい機能であり、すべてのバージョンのMySQLで利用できるわけではない

2つのステートメントに分けて記述する

INSERT句とUPDATE句を2つのステートメントに分けて記述することもできます。この方法は、シンプルな処理の場合に有効です。

INSERT INTO customers (email, name, address)
SELECT email, name, address
FROM new_customers;

UPDATE customers
SET name = new_customers.name,
address = new_customers.address
WHERE email IN (SELECT email FROM new_customers);

• 構文がシンプルでわかりやすい

• 2つのステートメントが必要になるため、可読性がやや低い
• INSERT句とUPDATE句をトランザクションで実行する必要がある

ストアドプロシージャを使用して、挿入と更新をカプセル化することができます。ストアドプロシージャを使用すると、コードを再利用しやすくなり、可読性も向上します。

CREATE PROCEDURE update_or_insert_customer(
  IN customer_email VARCHAR(255),
  IN customer_name VARCHAR(255),
  IN customer_address VARCHAR(255)
)
BEGIN
  INSERT INTO customers (email, name, address)
  VALUES (customer_email, customer_name, customer_address)
  ON DUPLICATE KEY UPDATE
  name = customer_name,
  address = customer_address;
END;

使用方法

CALL update_or_insert_customer('[email protected]', 'John Doe', '123 Main Street');

• コードを再利用しやすい
• 可読性が向上する

• ストアドプロシージャの作成と管理が必要

最適な方法の選択

どの方法が最適かは、状況によって異なります。以下の点を考慮して選択してください。

• データ操作の複雑性
• 使用しているMySQLのバージョン
• コードの可読性
• メンテナンス性

• [MySQL 8.4 Reference Manual :: 13.2.11 MERGE Statement](https://