【MySQL初心者必見】ON DUPLICATE KEYとLAST_INSERT_IDの使い方をわかりやすく解説！

2024-05-26

MySQLにおけるON DUPLICATE KEYとLAST_INSERT_IDのしくみとプログラミング解説

MySQLのINSERT ... ON DUPLICATE KEY構文は、レコードの挿入を試行した際に、主キーの重複が発生した場合の処理を指定するために使用されます。この構文と併用されるLAST_INSERT_ID()関数は、最後に挿入または更新されたレコードのIDを取得するために使用されます。

ON DUPLICATE KEYの概要

ON DUPLICATE KEY句は、INSERTステートメント内に記述され、主キーが重複した場合の動作を決定します。以下の2つのオプションが用意されています。

UPDATE: 重複レコードが存在する場合、そのレコードの値を更新します。
IGNORE: 重複レコードが存在する場合、何もしません。

LAST_INSERT_ID()関数の役割

LAST_INSERT_ID()関数は、最後に挿入または更新されたレコードのIDを取得します。これは、INSERTまたはUPDATEステートメントを実行した後に使用されます。

ON DUPLICATE KEYとLAST_INSERT_ID()の併用

ON DUPLICATE KEYとLAST_INSERT_ID()関数は、重複レコードの処理とID取得を組み合わせるために使用できます。具体的には、以下の2つのシナリオが考えられます。

シナリオ1：重複レコードの更新とID取得

このシナリオでは、ON DUPLICATE KEY UPDATEを使用して重複レコードを更新し、LAST_INSERT_ID()関数を使用して更新されたレコードのIDを取得します。

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

SELECT LAST_INSERT_ID();

上記の例では、usersテーブルにレコードを挿入しようとします。もしnameとemailの組み合わせが既に存在する場合、email列の値が更新されます。その後、LAST_INSERT_ID()関数を使用して、更新されたレコードのIDを取得します。

INSERT INTO orders (customer_id, product_id, quantity)
VALUES (123, 456, 10)
ON DUPLICATE KEY IGNORE;

SELECT LAST_INSERT_ID();

上記の例では、ordersテーブルにレコードを挿入しようとします。もしcustomer_idとproduct_idの組み合わせが既に存在する場合、レコードは挿入されず、LAST_INSERT_ID()関数は0を返します。一方、レコードが新規に挿入された場合、LAST_INSERT_ID()関数は挿入されたレコードのIDを返します。

補足事項

LAST_INSERT_ID()関数は、スレッド単位で管理されます。つまり、複数のスレッドが同時にINSERTまたはUPDATEステートメントを実行した場合、最後に実行されたステートメントのIDのみが取得されます。
LAST_INSERT_ID()関数は、トランザクション内でのみ有効です。トランザクションがロールバックされた場合、LAST_INSERT_ID()関数は0を返します。

ON DUPLICATE KEYとLAST_INSERT_ID()関数は、重複レコードの処理とID取得を柔軟に行うために役立つ機能です。これらの機能を理解することで、より効率的で正確なMySQLアプリケーション開発が可能になります。

このシナリオでは、usersテーブルにレコードを挿入または更新し、LAST_INSERT_ID()関数を使用してIDを取得します。

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

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

SELECT LAST_INSERT_ID();

説明

usersテーブルを作成します。このテーブルには、id、name、emailの3つの列があります。
id列は主キーであり、自動的にインクリメントされます。
email列はユニーク制約が設定されています。
INSERTステートメントを使用して、Taro Yamadaという名前と[email protected]というメールアドレスを持つレコードをusersテーブルに挿入しようとします。
ON DUPLICATE KEY UPDATE句を使用して、主キーが重複した場合の処理を指定します。
重複レコードが存在する場合、email列の値が[email protected]に更新されます。
LAST_INSERT_ID()関数を使用して、更新されたレコードのIDを取得します。

実行結果

上記コードを実行すると、以下の結果が出力されます。

これは、usersテーブルに1レコードが更新されたことを意味します。また、LAST_INSERT_ID()関数は1を返します。これは、更新されたレコードのIDが1であることを意味します。

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL
);

INSERT INTO orders (customer_id, product_id, quantity)
VALUES (123, 456, 10)
ON DUPLICATE KEY IGNORE;

SELECT LAST_INSERT_ID();

ordersテーブルを作成します。このテーブルには、id、customer_id、product_id、quantityの4つの列があります。
INSERTステートメントを使用して、customer_idが123、product_idが456、quantityが10であるレコードをordersテーブルに挿入しようとします。
重複レコードが存在する場合、レコードは挿入されず、LAST_INSERT_ID()関数は0を返します。
一方、レコードが新規に挿入された場合、LAST_INSERT_ID()関数は挿入されたレコードのIDを返します。

ON DUPLICATE KEYとLAST_INSERT_ID()以外の代替方法

INSERT ... SELECTステートメントは、別のテーブルからデータを抽出して挿入する際に有効です。このステートメントを使用すると、重複レコードの処理を個別に記述する必要がなく、コードを簡潔に記述できます。

利点

コードが簡潔になる
重複レコードの処理を個別に記述する必要がない

欠点

ON DUPLICATE KEYと比べて処理速度が遅くなる場合がある

例

INSERT INTO orders (customer_id, product_id, quantity)
SELECT customer_id, product_id, quantity
FROM order_details;

トリガーの使用

トリガーは、特定のイベントが発生したときに自動的に実行される一連のSQLステートメントです。トリガーを使用すると、重複レコードの検出と処理を柔軟にカスタマイズできます。

重複レコードの検出と処理を柔軟にカスタマイズできる

トリガーの管理が煩雑になる

CREATE TRIGGER orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF EXISTS (
    SELECT 1
    FROM orders
    WHERE customer_id = NEW.customer_id AND product_id = NEW.product_id
  ) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate order';
  END IF;
END;

独自のロジックを実装することで、ON DUPLICATE KEYとLAST_INSERT_ID()に依存せずに、重複レコードの処理とID取得を行うことができます。

完全な制御が可能
複雑な処理にも対応できる

テストとデバッグが難しくなる

INSERT INTO orders (customer_id, product_id, quantity)
VALUES (123, 456, 10);

SELECT id
FROM orders
WHERE customer_id = 123 AND product_id = 456
ORDER BY id DESC
LIMIT 1;