MySQLで重複レコードを処理する：INSERT IGNORE vs INSERT ... ON DUPLICATE KEY UPDATE

2024-04-02

INSERT IGNORE vs INSERT ... ON DUPLICATE KEY UPDATE

MySQLでデータを挿入する際、重複レコードの処理方法として INSERT IGNORE と INSERT ... ON DUPLICATE KEY UPDATE の2つの方法があります。それぞれ異なる動作をするので、状況に合わせて使い分けることが重要です。

動作比較

項目	INSERT IGNORE	INSERT ... ON DUPLICATE KEY UPDATE
重複レコード	無視して挿入	更新
エラー発生	なし	重複キーの場合はエラー
処理速度	速い	遅い
AUTO_INCREMENT	正常に動作	動作しない

詳細

INSERT IGNORE

重複レコードを無視して挿入します。
エラーは発生しません。
処理速度は速いです。
AUTO_INCREMENTカラムは正常に動作します。

例

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

INSERT ... ON DUPLICATE KEY UPDATE

重複レコードの場合は、指定されたカラムを更新します。
重複キーの場合はエラーが発生します。

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

使い分け

重複レコードを無視したい場合は INSERT IGNORE を使用します。
重複レコードを更新したい場合は INSERT ... ON DUPLICATE KEY UPDATE を使用します。
AUTO_INCREMENTカラムを使用する場合は INSERT IGNORE を使用します。

補足

INSERT IGNORE は、重複レコードの検査に時間がかかるとパフォーマンスが低下する可能性があります。
INSERT ... ON DUPLICATE KEY UPDATE は、更新するカラムを明示的に指定する必要があります。

上記以外にも、REPLACE INTO という方法もあります。
詳細は、MySQLのドキュメントを参照してください。

-- テーブル users に John Doe というユーザーが存在する場合、無視して挿入します。
INSERT IGNORE INTO users (name, email) VALUES ("John Doe", "[email protected]");

-- テーブル users に John Doe というユーザーが存在しない場合、挿入されます。
INSERT IGNORE INTO users (name, email) VALUES ("Jane Doe", "[email protected]");

-- テーブル users に John Doe というユーザーが存在する場合、email アドレスを更新します。
INSERT INTO users (name, email) VALUES ("John Doe", "[email protected]")
ON DUPLICATE KEY UPDATE email = "[email protected]";

-- テーブル users に John Doe というユーザーが存在しない場合、挿入されます。
INSERT INTO users (name, email) VALUES ("Jane Doe", "[email protected]")
ON DUPLICATE KEY UPDATE email = "[email protected]";

上記はあくまでもサンプルコードです。実際のコードは、要件に合わせて変更する必要があります。

重複レコードを処理する方法

方法

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

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

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

CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF EXISTS (SELECT * FROM users WHERE email = NEW.email) THEN
    SIGNAL SQLSTATE '23000' SET MESSAGE 'Duplicate email address';
  END IF;
END;