MySQLで重複レコードを処理する3つの方法：INSERT ... ON DUPLICATE KEY UPDATE 以外の選択肢

MySQLにおける INSERT ... ON DUPLICATE KEY UPDATE で2行が影響を受ける理由

複合ユニークキーによる影響

テーブルに複合ユニークキーが設定されている場合、INSERT ... ON DUPLICATE KEY UPDATEステートメントは、そのキーの一部のみが一致する既存のレコードを更新する可能性があります。例えば、以下のテーブルがあるとします。

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) UNIQUE KEY,
  email VARCHAR(255) UNIQUE KEY
);

このテーブルに、名前とメールアドレスが既存のレコードと一致する新しいレコードを挿入しようとすると、2行が影響を受けることになります。1行目は新しいレコードとして挿入され、もう1行は既存のレコードが更新されます。

トリガーによる影響

CREATE TRIGGER user_update_trigger AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  UPDATE users
  SET last_login = NOW()
  WHERE id = NEW.id;
END;

このトリガーは、usersテーブルのレコードが更新されるたびに、そのレコードのlast_loginカラムを現在時刻に更新します。INSERT ... ON DUPLICATE KEY UPDATEステートメントが既存のレコードを更新する場合、このトリガーが起動し、影響を受ける行数が1行増加します。

競合条件による影響

複数のクライアントが同時にINSERT ... ON DUPLICATE KEY UPDATEステートメントを実行している場合、競合条件が発生し、2行が影響を受けるように見えることがあります。これは、特に、オートコミットが有効な場合に発生する可能性があります。

影響を受ける行数の確認

INSERT ... ON DUPLICATE KEY UPDATEステートメントの影響を受ける行数を確認するには、以下のいずれかの方法を使用できます。

• INSERT ... ON DUPLICATE KEY UPDATEステートメントの後にSELECT LAST_INSERT_ID()関数を使用して、挿入されたレコードのIDを取得します。
• INSERT ... ON DUPLICATE KEY UPDATEステートメントの後にROW_COUNT()関数を使用して、影響を受けた行数を取得します。

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) UNIQUE KEY,
  email VARCHAR(255) UNIQUE KEY
);

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]');

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]');

このコードを実行すると、以下の結果になります。

Query OK, 2 rows affected (0.00 sec)

2行が影響を受けた理由は、2番目のINSERTステートメントが既存のレコードと一致する名前とメールアドレスを持つレコードを更新するためです。

この例では、usersテーブルにトリガーが設定されており、last_loginカラムを現在時刻に更新します。

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) UNIQUE KEY,
  email VARCHAR(255) UNIQUE KEY,
  last_login DATETIME DEFAULT NULL
);

CREATE TRIGGER user_update_trigger AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  UPDATE users
  SET last_login = NOW()
  WHERE id = NEW.id;
END;

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]');

UPDATE users
SET name = 'Jane Doe'
WHERE id = 1;

Query OK, 2 rows affected (0.00 sec)

2行が影響を受けた理由は、UPDATEステートメントがトリガーを起動し、last_loginカラムを更新するためです。

この例では、複数のクライアントが同時にINSERT ... ON DUPLICATE KEY UPDATEステートメントを実行し、競合条件が発生します。

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) UNIQUE KEY,
  email VARCHAR(255) UNIQUE KEY
);

SET autocommit=0;

BEGIN TRANSACTION;

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]');

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]');

COMMIT;

Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

2行が影響を受けたように見えますが、これは競合条件が発生したためです。実際には、1行のみが挿入され、もう1行は既存のレコードが更新されました。

INSERT ... ON DUPLICATE KEY UPDATE 以外の代替方法

INSERT ... IGNOREステートメント

INSERT ... IGNOREステートメントは、重複レコードを挿入しようとするとエラーを無視し、既存のレコードを更新しません。このステートメントは、既存のレコードを保持する必要がある場合に役立ちます。

INSERT IGNORE INTO users (name, email)
VALUES ('John Doe', '[email protected]');

複数回のINSERTステートメント

INSERT ... ON DUPLICATE KEY UPDATEステートメントを使用せずに、複数回のINSERTステートメントを使用してレコードを挿入することもできます。この方法では、最初にレコードが存在するかどうかを確認し、存在する場合は更新し、存在しない場合は挿入します。

SELECT COUNT(*)
FROM users
WHERE name = 'John Doe' AND email = '[email protected]';

IF @@FOUND_ROWS() = 0 THEN
  INSERT INTO users (name, email)
  VALUES ('John Doe', '[email protected]');
ELSE
  UPDATE users
  SET name = 'John Doe',
      email = '[email protected]'
  WHERE name = 'John Doe' AND email = '[email protected]';
END IF;

UPSERT ステートメント

MySQL 8以降では、UPSERTステートメントを使用して、レコードを挿入または更新できます。このステートメントは、INSERT ... ON DUPLICATE KEY UPDATEステートメントとほぼ同じように動作しますが、構文がより簡潔です。

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]')
ON DUPLICATE KEY UPDATE
  name = 'John Doe',
  email = '[email protected]';

アプリケーションロジックによる処理

アプリケーションロジックを使用して、重複レコードを処理することもできます。この方法では、アプリケーション内でレコードが存在するかどうかを確認し、存在する場合は更新し、存在しない場合は挿入します。

import mysql.connector

def insert_or_update_user(name, email):
  connection = mysql.connector.connect(
    host='localhost',
    user='username',
    password='password',
    database='database_name'
  )
  cursor = connection.cursor()

  cursor.execute('SELECT COUNT(*) FROM users WHERE name = %s AND email = %s', (name, email))
  count = cursor.fetchone()[0]

  if count == 0:
    cursor.execute('INSERT INTO users (name, email) VALUES (%s, %s)', (name, email))
  else:
    cursor.execute('UPDATE users SET name = %s, email = %s WHERE name = %s AND email = %s', (name, email, name, email))

  connection.commit()
  connection.close()

insert_or_update_user('John Doe', '[email protected]')