INSERT ... ON DUPLICATE KEY UPDATE を使って複数行を挿入する方法

2024-04-14

MySQL ON DUPLICATE KEY UPDATE で複数行を挿入するクエリについて

MySQL の INSERT ... ON DUPLICATE KEY UPDATE 句は、データ挿入時に重複キーが発生した場合、既存のレコードを更新する機能を提供します。これは、複数行を挿入する際に、重複レコードを処理するのに役立ちます。

構文

INSERT INTO table_name (column1, column2, ...)
VALUES (value11, value12, ...),
       (value21, value22, ...),
       ...
ON DUPLICATE KEY UPDATE column1 = VALUES(column1),
                        column2 = VALUES(column2),
                        ...;

説明

table_name: 挿入先のテーブル名
column1, column2: 挿入する列名
value11, value12: 挿入する値
...: 省略可能
ON DUPLICATE KEY UPDATE: 重複キーが発生した場合の処理を指定
column1 = VALUES(column1): 重複キーが発生した場合、column1 の値を挿入値で更新

例

次のクエリは、users テーブルに 2 行のデータを挿入します。user_id 列が重複している場合、email 列と address 列の値を更新します。

INSERT INTO users (user_id, email, address)
VALUES (1, '[email protected]', '123 Main Street'),
       (1, '[email protected]', '456 Elm Street')
ON DUPLICATE KEY UPDATE email = VALUES(email),
                        address = VALUES(address);

注意事項

ON DUPLICATE KEY UPDATE 句は、MySQL 5.0 以降でのみ使用できます。
重複キーが発生した場合、更新される列は ON DUPLICATE KEY UPDATE 句で指定された列のみです。
INSERT ... ON DUPLICATE KEY UPDATE 句を使用すると、パフォーマンスが低下する可能性があります。

MySQL の INSERT ... ON DUPLICATE KEY UPDATE 句は、複数行を挿入する際に重複レコードを処理するのに役立ちます。この機能を使用することで、データの整合性を保ちながら、効率的にデータを挿入することができます。

顧客情報テーブルへのデータ挿入

INSERT INTO customers (customer_id, name, email, phone)
VALUES (101, '田中 太郎', '[email protected]', '090-1234-5678'),
       (101, '田中 花子', '[email protected]', '090-8765-4321'),
       (102, '佐藤 健', '[email protected]', '080-1234-5678'),
       (103, '鈴木 優子', '[email protected]', '080-8765-4321')
ON DUPLICATE KEY UPDATE email = VALUES(email),
                        phone = VALUES(phone);

このクエリは、customers テーブルに 4 行のデータを挿入しようとします。
customer_id 101 のレコードが 2 つ存在するため、email 列と phone 列の値が更新されます。
customer_id 102 と 103 のレコードは新規レコードとして挿入されます。

商品在庫情報テーブルへのデータ挿入

次のコードは、products テーブルに商品在庫情報を挿入します。product_id 列が重複している場合、stock 列の値を更新します。

INSERT INTO products (product_id, name, price, stock)
VALUES (P1001, 'iPhone 13', 89800, 10),
       (P1001, 'iPhone 13', 89800, 15),
       (P1002, 'Galaxy S22', 79800, 5),
       (P1003, 'Pixel 6', 74800, 8)
ON DUPLICATE KEY UPDATE stock = VALUES(stock);

注文情報テーブルへのデータ挿入

INSERT INTO orders (order_id, customer_id, product_id, quantity, status)
VALUES (O1001, 101, P1001, 1, '処理中'),
       (O1001, 101, P1002, 1, '発送済み'),
       (O1002, 102, P1003, 2, '処理中'),
       (O1003, 103, P1001, 1, '未処理')
ON DUPLICATE KEY UPDATE status = VALUES(status);

これらのサンプルコードは、INSERT ... ON DUPLICATE KEY UPDATE 句の使い方を理解するのに役立ちます。具体的な状況に合わせて、クエリを修正してください。

MySQLで重複レコードを処理する他の方法

REPLACE INTO 句は、既存のレコードを置き換えるために使用できます。重複キーが発生した場合、既存のレコードが新しい値で置き換えられます。

REPLACE INTO table_name (column1, column2, ...)
VALUES (value11, value12, ...),
       (value21, value22, ...),
       ...;

REPLACE INTO users (user_id, email, address)
VALUES (1, '[email protected]', '123 Main Street'),
       (1, '[email protected]', '456 Elm Street');

REPLACE INTO 句を使用すると、既存のデータが失われる可能性があります。
REPLACE INTO 句は、INSERT ... ON DUPLICATE KEY UPDATE 句よりも古い方法です。

MERGE 句は、INSERT、UPDATE、DELETE 操作を 1 つのステートメントで実行するために使用できます。重複キーが発生した場合、既存のレコードが更新されます。

MERGE INTO table_name AS target
USING source
ON (target.column1 = source.column1)
WHEN MATCHED THEN
    UPDATE SET target.column2 = source.column2,
              ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...)
    VALUES (source.column1, source.column2, ...);

MERGE INTO users AS target
USING (
    SELECT 1 AS user_id, '[email protected]' AS email, '123 Main Street' AS address
    UNION ALL
    SELECT 1 AS user_id, '[email protected]' AS email, '456 Elm Street' AS address
) AS source
ON (target.user_id = source.user_id)
WHEN MATCHED THEN
    UPDATE SET target.email = source.email,
              target.address = source.address
WHEN NOT MATCHED THEN
    INSERT (user_id, email, address)
    VALUES (source.user_id, source.email, source.address);

MERGE 句は、MySQL 5.6 以降でのみ使用できます。
MERGE 句は、複雑なクエリを作成する際に役立ちます。

サブクエリを使用して、重複レコードを処理することもできます。

INSERT INTO users (user_id, email, address)
SELECT 1 AS user_id, '[email protected]' AS email, '123 Main Street' AS address
UNION ALL
SELECT 1 AS user_id, '[email protected]' AS email, '456 Elm Street' AS address
ON DUPLICATE KEY UPDATE email = VALUES(email),
                        address = VALUES(address);