INSERT ... ON DUPLICATE KEY (do nothing) の詳細解説

MySQL における INSERT ... ON DUPLICATE KEY (do nothing) のプログラミング解説

MySQL の INSERT ... ON DUPLICATE KEY 構文は、レコード挿入時に重複キーが発生した場合、既存のレコードを更新するか何もしないかを制御するために使用されます。 (do nothing) オプションを指定すると、重複キーが発生した場合、何も処理せずに処理を続行します。

構文

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY (column1, column2, ...)
DO NOTHING;

例

次の例では、users テーブルにレコードを挿入しようとしますが、user_id 列で重複キーが発生した場合、何も処理せずに処理を続行します。

INSERT INTO users (user_id, username, email)
VALUES (1, 'alice', '[email protected]')
ON DUPLICATE KEY (user_id)
DO NOTHING;

用途

INSERT ... ON DUPLICATE KEY (do nothing) オプションは、以下の場合に役立ちます。

• 重複レコードを挿入しても問題ない場合
• 重複レコードを検出して処理するロジックを別の方法で実装したい場合
• 既存のレコードを更新したくない場合

注意点

• DO NOTHING オプションは、INSERT 文の失敗を防ぎません。重複キーが発生した場合、エラーは発生しません。
• DO NOTHING オプションは、パフォーマンスに影響を与える可能性があります。重複キーが発生する可能性が高い場合は、代わりに INSERT IGNORE オプションを使用することを検討してください。

補足

• INSERT ... ON DUPLICATE KEY は、MySQL と MariaDB でのみサポートされています。
• UNIQUE インデックスまたは PRIMARY KEY でのみ使用できます。
• 複数の列を指定できます。

次のコードは、users テーブルに user_id 列で重複するレコードを挿入しようとします。 DO NOTHING オプションにより、重複キーが発生しても何も処理されずに処理が続行されます。

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

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

INSERT INTO users (user_id, username, email)
VALUES (1, 'bob', '[email protected]')
ON DUPLICATE KEY (user_id)
DO NOTHING;

SELECT * FROM users;

このコードを実行すると、次の結果が出力されます。

user_id | username | email
------- | -------- | --------
1       | alice   | [email protected]
1       | bob     | [email protected]

重複レコードを検出して処理する

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

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

INSERT INTO users (user_id, username, email)
VALUES (1, 'bob', '[email protected]')
ON DUPLICATE KEY (user_id)
UPDATE email = '[email protected]';

SELECT * FROM users;

user_id | username | email
------- | -------- | --------
1       | alice   | [email protected]

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

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

INSERT INTO users (user_id, username, email)
VALUES (1, 'bob', '[email protected]')
ON DUPLICATE KEY (user_id)
DO NOTHING;

SELECT * FROM users;

user_id | username | email
------- | -------- | --------
1       | alice   | [email protected]

MySQL における重複レコード処理の代替方法

INSERT IGNORE オプションは、INSERT ... ON DUPLICATE KEY (do nothing) オプションと似ていますが、エラーが発生せずに処理を続行します。

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

REPLACE INTO ステートメントは、既存のレコードを新しいレコードで置き換えます。

REPLACE INTO users (user_id, username, email)
VALUES (1, 'bob', '[email protected]');

INSERT ... SELECT ステートメントは、別のテーブルからレコードを挿入するために使用できます。このステートメントを使用すると、重複レコードを排除できます。

INSERT INTO users (user_id, username, email)
SELECT user_id, username, email
FROM temp_users
WHERE NOT EXISTS (
  SELECT 1
  FROM users
  WHERE user_id = temp_users.user_id
);

トリガーを使用して、レコード挿入時に重複レコードを処理できます。

CREATE TRIGGER user_insert_trigger BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF EXISTS (
    SELECT 1
    FROM users
    WHERE user_id = NEW.user_id
  ) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate user ID';
  END IF;
END;

import mysql.connector

def insert_user(user_id, username, email):
  connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='mydb'
  )
  cursor = connection.cursor()

  try:
    cursor.execute('INSERT INTO users (user_id, username, email) VALUES (%s, %s, %s)', (user_id, username, email))
    connection.commit()
  except mysql.connector.errors.IntegrityError as e:
    if e.errno == 1062:
      print('Duplicate user ID')
    else:
      raise e

  cursor.close()
  connection.close()

insert_user(1, 'alice', '[email protected]')
insert_user(1, 'bob', '[email protected]')