3つの方法でマスターする「MySQL: Insert record if not exists in table」

MySQLで、テーブルに特定のレコードが存在しない場合のみ挿入する方法はいくつかあります。 ここでは、代表的な3つの方法を紹介します。

方法1: INSERT ... SELECT

INSERT INTO テーブル名 (カラム名1, カラム名2, ...)
SELECT 値1, 値2, ...
FROM テーブル名
WHERE 条件;

この方法は、SELECT で取得したレコードが空の場合のみ、INSERT を実行します。 以下のような例で説明します。

例:

-- テーブル users に、名前が "John Doe" のユーザーが存在しない場合のみ挿入する
INSERT INTO users (name, email)
SELECT 'John Doe', '[email protected]'
FROM users
WHERE name = 'John Doe';

方法2: INSERT ... ON DUPLICATE KEY UPDATE

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

この方法は、主キーまたはユニークキーが重複した場合、UPDATE を実行します。 以下のような例で説明します。

-- テーブル users に、名前が "John Doe" のユーザーが存在しない場合のみ挿入する
INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]')
ON DUPLICATE KEY UPDATE email = '[email protected]';

方法3: REPLACE INTO

REPLACE INTO テーブル名 (カラム名1, カラム名2, ...)
VALUES (値1, 値2, ...);

-- テーブル users に、名前が "John Doe" のユーザーが存在する場合は更新、存在しない場合は挿入する
REPLACE INTO users (name, email)
VALUES ('John Doe', '[email protected]');

注意点

• 上記の方法は、いずれも主キーまたはユニークキーが存在することを前提としています。
• INSERT ... SELECT は、複数レコードを挿入する場合に有効です。
• INSERT ... ON DUPLICATE KEY UPDATE は、重複レコードの更新処理を記述できます。
• REPLACE INTO は、既存のレコードを置き換えるため、注意が必要です。

-- テーブル users に、名前が "John Doe" かつメールアドレスが "[email protected]" のユーザーが存在しない場合のみ挿入する
INSERT INTO users (name, email)
SELECT 'John Doe', '[email protected]'
FROM users
WHERE name = 'John Doe' AND email = '[email protected]';

-- テーブル users に、名前が "John Doe" のユーザーが存在しない場合のみ挿入する
INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]')
ON DUPLICATE KEY UPDATE email = '[email protected]';

-- テーブル users に、名前が "John Doe" かつメールアドレスが "[email protected]" のユーザーが存在する場合は更新、存在しない場合は挿入する
REPLACE INTO users (name, email)
VALUES ('John Doe', '[email protected]');

実行環境

• MySQL 8.0

テストデータ

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

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

実行結果

• 方法1:

-- 挿入成功

-- 挿入成功

-- 更新成功

補足

• 上記のサンプルコードは、あくまで一例です。 実際のコードは、要件に合わせて変更する必要があります。
• 主キーまたはユニークキーの設定など、テーブルの設計も考慮する必要があります。

方法4: INSERT IGNORE

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

-- テーブル users に、名前が "John Doe" のユーザーが存在する場合は無視して、新しいユーザーを挿入する
INSERT IGNORE INTO users (name, email)
VALUES ('John Doe', '[email protected]');

• INSERT IGNORE は、エラーが発生しても何も通知されないため、注意が必要です。

INSERT INTO テーブル名 (カラム名1, カラム名2, ...)
SELECT 値1, 値2, ...
WHERE NOT EXISTS (
  SELECT *
  FROM テーブル名
  WHERE 条件
);

この方法は、EXISTS サブクエリを使用して、レコードが存在するかどうかを事前に確認してから挿入します。 以下のような例で説明します。

-- テーブル users に、名前が "John Doe" かつメールアドレスが "[email protected]" のユーザーが存在しない場合のみ挿入する
INSERT INTO users (name, email)
SELECT 'John Doe', '[email protected]'
WHERE NOT EXISTS (
  SELECT *
  FROM users
  WHERE name = 'John Doe' AND email = '[email protected]'
);

DELIMITER //

CREATE PROCEDURE insert_if_not_exists (
  IN name VARCHAR(255),
  IN email VARCHAR(255)
)
BEGIN
  DECLARE exists INT;

  -- レコードが存在するかどうかを確認
  SELECT COUNT(*) INTO exists
  FROM users
  WHERE name = name AND email = email;

  -- レコードが存在しない場合のみ挿入
  IF exists = 0 THEN
    INSERT INTO users (name, email)
    VALUES (name, email);
  END IF;
END //

DELIMITER ;

-- stored procedure を呼び出す
CALL insert_if_not_exists('John Doe', '[email protected]');

どの方法を使うべきかは、要件や状況によって異なります。 以下に、それぞれの方法のメリットとデメリットをまとめます。

上記のメリットとデメリットを参考に、最適な方法を選択してください。