Angular Ansible Apache HTTP Server C C++ CMake Composer CSS Cypress date-fns Django Django REST Framework Eigen3 ESLint FastAPI Git GNU Make HTML htmx HTTP JavaScript Julia LaTeX MariaDB Node.js NumPy Octave pandas Perl PHP PostgreSQL Pygame Python PyTorch Qt R SQLite Tailwind CSS

MariaDBで重複レコード処理を極める！INSERT ON DUPLICATE KEY UPDATEと代替方法の比較


従来のINSERT文では、重複するキー値が検出されるとエラーが発生していましたが、INSERT ON DUPLICATE KEY UPDATEを使用すると、以下の2つの動作を選択できます。

  1. 重複レコードを無視する
  2. 既存レコードを更新する

この機能は、重複レコードの発生が想定される場面で、データ整合性を維持しながら効率的なデータ挿入を実現するのに役立ちます。

構文

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...),
      (value3, value4, ...),
      ...
ON DUPLICATE KEY UPDATE
    column1 = NEW_VALUE1,
    column2 = NEW_VALUE2,
    ...;

解説

  • column1 = NEW_VALUE1, column2 = NEW_VALUE2, ...: 重複レコードの場合に更新する列と値
  • ON DUPLICATE KEY UPDATE: 重複レコード検出時の処理を指定
  • value1, value2, ...: 挿入する値
  • column1, column2, ...: 挿入する列名
  • table_name: 挿入先のテーブル名
  • IGNOREオプションを指定すると、重複レコードを無視して挿入処理を続行できます。
  • 複数行の挿入にも対応できます。
  • NEW_VALUEは、INSERT文で挿入しようとした値を参照します。

例

例1：重複レコードを無視する

INSERT INTO users (user_id, username, email)
VALUES (1, 'alice', '[email protected]'),
      (1, 'bob', '[email protected]'),
      (2, 'charlie', '[email protected]');

この例では、user_id列に重複する値(1)が指定されていますが、INSERT ON DUPLICATE KEY UPDATEを使用しているため、2番目のレコードは無視され、1番目のレコードのみ挿入されます。

例2：既存レコードを更新する

INSERT INTO products (product_id, product_name, price)
VALUES (101, 'Laptop', 1200),
      (101, 'Laptop', 1300),
      (102, 'Smartphone', 500);

この例では、product_id列に重複する値(101)が指定されていますが、INSERT ON DUPLICATE KEY UPDATEを使用しているため、2番目のレコードは既存レコードを更新します。具体的には、price列の値が1200から1300に変更されます。

  • 重複レコードの更新処理においては、競合が発生する可能性があることに注意する必要があります。ロック機構などを適切に利用して、データ整合性を確保する対策が必要です。
  • INSERT ON DUPLICATE KEY UPDATEは、重複するキー値に基づいて処理を分岐するため、ユニークキーまたはプライマリキー列に適切なインデックスが作成されていることを確認する必要があります。

INSERT ON DUPLICATE KEY UPDATEは、MariaDB/MySQL特有の拡張機能であり、重複レコードの処理を柔軟に制御することで、データ整合性を維持しながら効率的なデータ挿入を実現するのに役立ちます。


例1：主キーによる重複レコードの処理

CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE
);

INSERT INTO users (username, email)
VALUES ('alice', '[email protected]'),
      ('bob', '[email protected]'),
      ('alice', '[email protected]'),  -- 重複レコード
      ('charlie', '[email protected]');

このSQL文を実行すると、以下の結果になります。

user_id | username | email
------- | -------- | --------
       1 |  alice   | [email protected]
       2 |   bob   |  [email protected]
       2 |  alice   | [email protected]  -- 2番目のレコードは既存レコードを更新
       3 | charlie | [email protected]

解説

  • username = VALUES(username), email = VALUES(email): 重複レコードの場合に更新する列と値
  • ON DUPLICATE KEY UPDATE: 重複レコード検出時の処理を指定
  • VALUES ('alice', '[email protected]'), ...: 挿入するデータの値を指定
  • INSERT INTO users (username, email): テーブルusersにデータを挿入するINSERT文

この例では、username列とemail列にユニーク制約が設定されているため、重複するレコードが検出されると、INSERT ON DUPLICATE KEY UPDATEによって既存レコードのusername列とemail列が更新されます。

例2：ユニークインデックスによる重複レコードの処理

この例では、ユニークインデックスを持つテーブルにデータを挿入し、重複レコードが発生した場合にエラーを発生させる処理を示します。

CREATE TABLE products (
  product_id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  UNIQUE KEY product_name_unique (product_name)
);

INSERT INTO products (product_name, price)
VALUES ('Laptop', 1200),
      ('Smartphone', 500),
      ('Laptop', 1300);  -- 重複レコード

このSQL文を実行すると、以下のエラーが発生します。

ERROR 1062 (Duplicate key entry): Duplicate entry 'Laptop' for key 'product_name_unique'

解説

  • VALUES ('Laptop', 1200), ...: 挿入するデータの値を指定
  • INSERT INTO products (product_name, price): テーブルproductsにデータを挿入するINSERT文
  • CREATE TABLE products (... UNIQUE KEY product_name_unique (product_name) ...): テーブルproductsにproduct_name列にユニークインデックスを作成

この例では、product_name列にユニークインデックスが設定されているため、重複するレコードが検出されると、エラーが発生します。INSERT ON DUPLICATE KEY UPDATEは使用されていませんが、この機能を使用すれば、エラー発生時の処理を制御することができます。

この例では、INSERT IGNOREオプションを使用して、重複レコードを無視して挿入処理を続行する処理を示します。

INSERT IGNORE INTO products (product_id, product_name, price)
VALUES (101, 'Laptop', 1200),
      (101, 'Laptop', 1300),
      (102, 'Smartphone', 500);

user_id | product_name | price
------- | -------- | --------
     101 |  Laptop   | 1200
     102 | Smartphone |  500

解説

  • VALUES (101, 'Laptop', 1200), ...: 挿入するデータの値を指定
  • INSERT IGNORE INTO products ...: 重複レコードを無視して挿入処理を続行するINSERT文

この例では、INSERT IGNOREオプションを使用しているため、2番目のレコードは重複レコードとして無視され、1番目のレコードと3番目のレコードのみ挿入されます。


代替方法の選択肢

  1. MERGE文を使用する

MERGE文は、SQL ServerやOracleなどの他のデータベースでも標準でサポートされている機能であり、INSERTとUPDATEを組み合わせたような操作を実行できます。INSERT ON DUPLICATE KEY UPDATEと同様の処理を実現できますが、構文が少し複雑になるという欠点があります。

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

例

MERGE INTO users USING (
  SELECT 1 AS user_id, 'alice' AS username, '[email protected]' AS email
  UNION ALL
  SELECT 2 AS user_id, 'bob' AS username, '[email protected]' AS email
  UNION ALL
  SELECT 1 AS user_id, 'charlie' AS username, '[email protected]' AS email
) AS source_table
  ON users.user_id = source_table.user_id
WHEN MATCHED THEN
  UPDATE SET
    username = source_table.username,
    email = source_table.email
WHEN NOT MATCHED THEN
  INSERT (user_id, username, email)
  VALUES (source_table.user_id, source_table.username, source_table.email);
  1. 複数回のINSERT文とUPDATE文を組み合わせる

INSERT ON DUPLICATE KEY UPDATEの挙動を再現するために、INSERT文とUPDATE文を組み合わせる方法もあります。ただし、この方法では、2回のクエリを実行する必要があり、処理が冗長になるという欠点があります。

-- 1回目のINSERT文：重複レコードを無視して挿入
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]'),
      ('bob', '[email protected]'),
      ('alice', '[email protected]');

-- 2回目のUPDATE文：既存レコードを更新
UPDATE users
SET username = 'charlie', email = '[email protected]'
WHERE user_id = 2;
  1. ストアドプロシージャを使用する

INSERT ON DUPLICATE KEY UPDATEの処理をカプセル化するために、ストアドプロシージャを作成する方法もあります。この方法では、複雑な処理を抽象化し、コードをより読みやすくすることができます。

CREATE PROCEDURE upsert_user(
  IN username VARCHAR(255),
  IN email VARCHAR(255)
)
BEGIN
  INSERT INTO users (username, email)
  VALUES (username, email)
  ON DUPLICATE KEY UPDATE
    username = VALUES(username),
    email = VALUES(email);
END PROCEDURE;

CALL upsert_user('alice', '[email protected]');
CALL upsert_user('bob', '[email protected]');
CALL upsert_user('alice', '[email protected]');
CALL upsert_user('charlie', '[email protected]');
方法利点欠点
INSERT ON DUPLICATE KEY UPDATEシンプルでわかりやすいMariaDB/MySQL特有の機能
MERGE文標準的な機能で移植性が高い構文が複雑
複数回のINSERT文とUPDATE文汎用性が高い処理が冗長
ストアドプロシージャコードを抽象化できる開発・保守の手間がかかる