INSERT ... ON DUPLICATE KEY UPDATE vs INSERT IGNORE: どっちを選ぶ？

MySQLでINSERT時に重複行を挿入せず、デッドロックも発生させない方法

MySQLでINSERTを行う際、すでに同じデータが存在する場合は重複行の挿入を防ぎたい場合があります。また、複数の接続が同時にINSERTを行う場合、デッドロックが発生する可能性も考慮する必要があります。

解決策

以下の3つの方法があります。

INSERT ... ON DUPLICATE KEY UPDATE

UNIQUEまたはPRIMARY KEY制約のあるカラムに対してINSERTを行う場合、この方法が最適です。

• 存在しない場合はINSERT、存在する場合はUPDATEを実行します。
• デッドロックが発生する可能性は低くなります。

例：

INSERT INTO テーブル名 (カラム名1, カラム名2)
VALUES (?, ?)
ON DUPLICATE KEY UPDATE
  カラム名2 = VALUES(カラム名2);

INSERT IGNORE

重複行の挿入を無視します。

• 主キーが重複する場合、エラーが発生せず既存の行が更新されます。
• 他のカラムの重複も無視するため、データの整合性に問題が発生する可能性があります。

INSERT IGNORE INTO テーブル名 (カラム名1, カラム名2)
VALUES (?, ?);

SELECT ... FOR UPDATEを使用して、行をロックしてからINSERTを行います。

• デッドロックが発生する可能性はありますが、確実に重複行の挿入を防ぐことができます。
• 他の接続からの更新もロックされるため、処理速度が低下する可能性があります。

START TRANSACTION;

SELECT * FROM テーブル名
WHERE カラム名1 = ?
FOR UPDATE;

INSERT INTO テーブル名 (カラム名1, カラム名2)
VALUES (?, ?);

COMMIT;

それぞれの方法の比較

推奨方法

• 主キーまたはUNIQUE制約のあるカラムがある場合は、INSERT ... ON DUPLICATE KEY UPDATEを使用するのがおすすめです。
• 主キー重複時の挙動が許容できる場合は、INSERT IGNOREを使用すると処理速度を向上できます。
• データの整合性を重視する場合は、SELECT ... FOR UPDATE + INSERTを使用する必要があります。

補足

• 上記の方法は、InnoDBエンジンを使用している場合にのみ有効です。
• その他にも、アプリケーションロジックで重複チェックを行う方法もあります。

# テーブル作成
CREATE TABLE IF NOT EXISTS users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) UNIQUE,
  email VARCHAR(255)
);

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

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

SELECT ... FOR UPDATE + INSERT

# START TRANSACTION
START TRANSACTION;

# ロック
SELECT * FROM users
WHERE name = 'John Doe'
FOR UPDATE;

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

# COMMIT
COMMIT;

• 上記のコードは、あくまでサンプルです。実際の使用状況に合わせて変更してください。
• エラー処理など、必要な処理を追加してください。

INSERT ... SELECT ... WHERE NOT EXISTS

• 複雑なSQLになる可能性があります。

INSERT INTO テーブル名 (カラム名1, カラム名2)
SELECT ?, ?
WHERE NOT EXISTS (
  SELECT * FROM テーブル名
  WHERE カラム名1 = ?
);

アプリケーションロジックで重複チェック

INSERTを行う前に、アプリケーションロジックで重複チェックを行います。

• アプリケーション側の処理が増えます。

def insert_user(name, email):
  # 重複チェック
  if User.objects.filter(name=name).exists():
    return

  # INSERT
  user = User(name=name, email=email)
  user.save()

• 状況に合わせて最適な方法を選択してください。