MySQLのON DUPLICATE KEY UPDATEの使い方

色とりどりの色鉛筆が並んだ様子 SQL

あればその行を更新し、なければ新しく追加する処理は、UPSERT(アップサート)と呼ばれます。MySQL では INSERT … ON DUPLICATE KEY UPDATE を使うと、これを1文で書けます。

SELECT で行があるかを確かめてから INSERT と UPDATE を出し分ける書き方より短く、確かめてから書きこむまでのあいだに、別の接続が同じ行を入れてしまう心配もありません。基本の書き方から、8.0.19 で入った行の別名(AS new)、REPLACE・INSERT IGNORE との違い、AUTO_INCREMENT の番号が飛ぶ点まで、文房具の在庫表を使って順に試します。

以下の結果は MySQL 8.0 で実際に実行したものです。

文房具の在庫表で考える

仕入れ先から毎日、商品コード(SKU)と在庫数の一覧が届くとします。一覧には、もう表にある商品と、新しく扱い始めた商品が混ざっています。

SKU の列には UNIQUE 制約を付けておきます。ON DUPLICATE KEY UPDATE は、主キーか UNIQUE 制約の重複を手がかりに、もうある行を見つけるからです。UNIQUE 制約の付け方は「NOT NULL・UNIQUE・CHECK制約で不正データを防ぐ」で説明しています。

CREATE TABLE product_stock (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  sku   VARCHAR(20) NOT NULL,
  name  VARCHAR(50) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note  VARCHAR(50) NOT NULL DEFAULT '',
  UNIQUE KEY uk_sku (sku)
);

INSERT INTO product_stock (sku, name, stock, note) VALUES
  ('A-001', 'ボールペン 黒', 120, '定番'),
  ('A-002', 'ボールペン 赤',  80, ''),
  ('B-010', 'ノート A5',      45, '');

SELECT * FROM product_stock;
+----+-------+---------------------+-------+--------+
| id | sku   | name                | stock | note   |
+----+-------+---------------------+-------+--------+
|  1 | A-001 | ボールペン 黒       |   120 | 定番   |
|  2 | A-002 | ボールペン 赤       |    80 |        |
|  3 | B-010 | ノート A5           |    45 |        |
+----+-------+---------------------+-------+--------+

note 列は、あとで REPLACE との違いを見るために使います。

ON DUPLICATE KEY UPDATE の基本

ふつうの INSERT のあとに ON DUPLICATE KEY UPDATE と、更新したい列を続けます。主キーか UNIQUE の値がすでにあれば、INSERT の代わりに後ろの UPDATE が動きます。

-- 新しい SKU:追加される
INSERT INTO product_stock (sku, name, stock)
VALUES ('C-100', '付箋 75mm', 200)
ON DUPLICATE KEY UPDATE stock = 200;
SELECT ROW_COUNT();   -- 1

-- 既にある SKU:更新される
INSERT INTO product_stock (sku, name, stock)
VALUES ('A-002', 'ボールペン 赤', 60)
ON DUPLICATE KEY UPDATE stock = 60;
SELECT ROW_COUNT();   -- 2

-- 同じ文をもう一度:値が同じなので何も変わらない
INSERT INTO product_stock (sku, name, stock)
VALUES ('A-002', 'ボールペン 赤', 60)
ON DUPLICATE KEY UPDATE stock = 60;
SELECT ROW_COUNT();   -- 0

ROW_COUNT() は、直前の文で変わった行の数を返す関数です。この数を見ると、追加と更新のどちらが起きたのかがわかります。

起きたこと ROW_COUNT() の値
新しい行を追加した 1
もうある行を更新した 2
もうある行に同じ値を入れた(変化なし) 0

更新のときに 2 になるのは MySQL の決まりです。アプリの側で、1 なら新規、2 なら更新と分けて記録したいときに使えます。ただし接続の設定で CLIENT_FOUND_ROWS を付けていると、変化なしのときも 1 が返ります。

+----+-------+---------------------+-------+--------+
| id | sku   | name                | stock | note   |
+----+-------+---------------------+-------+--------+
|  1 | A-001 | ボールペン 黒       |   120 | 定番   |
|  2 | A-002 | ボールペン 赤       |    60 |        |
|  3 | B-010 | ノート A5           |    45 |        |
|  4 | C-100 | 付箋 75mm           |   200 |        |
+----+-------+---------------------+-------+--------+

行の別名 AS new で入れようとした値を使う

上の例では、更新する値を VALUES と UPDATE の2か所に書いていました。MySQL 8.0.19 からは、VALUES のあとに AS new のような別名を付けると、入れようとした値を new.列名 で参照できます。

複数行をまとめて入れるときに便利です。行ごとに、その行の値で更新されます。

INSERT INTO product_stock (sku, name, stock) VALUES
  ('A-001', 'ボールペン 黒', 110),
  ('B-010', 'ノート A5 方眼', 50),
  ('D-200', 'クリップ 小',  300)
AS new
ON DUPLICATE KEY UPDATE
  name  = new.name,
  stock = new.stock;
SELECT ROW_COUNT();   -- 5(更新 2 + 更新 2 + 追加 1)

SELECT * FROM product_stock;
+----+-------+---------------------+-------+--------+
| id | sku   | name                | stock | note   |
+----+-------+---------------------+-------+--------+
|  1 | A-001 | ボールペン 黒       |   110 | 定番   |
|  2 | A-002 | ボールペン 赤       |    60 |        |
|  3 | B-010 | ノート A5 方眼      |    50 |        |
|  4 | C-100 | 付箋 75mm           |   200 |        |
|  7 | D-200 | クリップ 小         |   300 |        |
+----+-------+---------------------+-------+--------+

A-001 の note は UPDATE に書いていないので、定番のまま残っています。新しく入った D-200 の id が 5 ではなく 7 になった理由は、落とし穴の節で説明します。

入荷した分を今の在庫に足したいときは、今の値と入れようとした値を両方使います。テーブル名.列名(または列名だけ)が今の行の値、new.列名が入れようとした値です。

INSERT INTO product_stock (sku, name, stock)
VALUES ('A-001', 'ボールペン 黒', 30) AS new
ON DUPLICATE KEY UPDATE stock = product_stock.stock + new.stock;

SELECT sku, stock FROM product_stock WHERE sku = 'A-001';
+-------+-------+
| sku   | stock |
+-------+-------+
| A-001 |   140 |
+-------+-------+

同じ形で、ページごと・日ごとのアクセス数を数える表も作れます。主キーを (page, view_date) にしておき、初めての日は 1 で追加、2回目からは 1 ずつ足します。

CREATE TABLE daily_pv (
  page      VARCHAR(100) NOT NULL,
  view_date DATE NOT NULL,
  pv        INT NOT NULL,
  PRIMARY KEY (page, view_date)
);

-- /top を3回、/sql を1回
INSERT INTO daily_pv VALUES ('/top', '2026-11-16', 1) ON DUPLICATE KEY UPDATE pv = pv + 1;
INSERT INTO daily_pv VALUES ('/top', '2026-11-16', 1) ON DUPLICATE KEY UPDATE pv = pv + 1;
INSERT INTO daily_pv VALUES ('/top', '2026-11-16', 1) ON DUPLICATE KEY UPDATE pv = pv + 1;
INSERT INTO daily_pv VALUES ('/sql', '2026-11-16', 1) ON DUPLICATE KEY UPDATE pv = pv + 1;

SELECT * FROM daily_pv;
+------+------------+----+
| page | view_date  | pv |
+------+------------+----+
| /sql | 2026-11-16 |  1 |
| /top | 2026-11-16 |  3 |
+------+------------+----+

8.0.19 より前は、入れようとした値を VALUES(列名) で参照していました。この書き方は 8.0.20 から非推奨になり、使うと警告が出ます。

INSERT INTO product_stock (sku, name, stock)
VALUES ('A-002', 'ボールペン 赤', 70)
ON DUPLICATE KEY UPDATE stock = VALUES(stock);
SHOW WARNINGS;
| Warning | 1287 | 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead |

古い記事やコードで VALUES() を見かけたら、AS new の形に書きかえておくと、将来のバージョンでも動きます。別名にはテーブル名と同じ名前を使えない点に気をつけてください。

PostgreSQL では書き方が違い、INSERT … ON CONFLICT (sku) DO UPDATE SET stock = EXCLUDED.stock のように、ぶつかる列を自分で指定します。標準 SQL の MERGE 文は PostgreSQL 15 から使えますが、MySQL 8.0 にはありません。

REPLACE・INSERT IGNORE との違い

重複したときの動きが似た書き方が、MySQL にはあと2つあります。違いは次の表のとおりです。

書き方 重複したとき 書かなかった列 id
ON DUPLICATE KEY UPDATE 指定した列だけ更新 そのまま残る 変わらない
REPLACE 古い行を消して入れ直す 初期値に戻る 新しい番号になる
INSERT IGNORE 何もしない(警告だけ) そのまま残る 変わらない

REPLACE は、同じキーの行があると、その行を削除してから新しい行を入れます。

REPLACE INTO product_stock (sku, name, stock)
VALUES ('A-001', 'ボールペン 黒', 100);
SELECT ROW_COUNT();   -- 2(削除 1 + 追加 1)

SELECT * FROM product_stock WHERE sku = 'A-001';
+----+-------+---------------------+-------+------+
| id | sku   | name                | stock | note |
+----+-------+---------------------+-------+------+
| 12 | A-001 | ボールペン 黒       |   100 |      |
+----+-------+---------------------+-------+------+

id が 1 から 12 に変わり、note の定番も消えました。ほかの表がこの id を参照していると、つながりが切れます。外部キーに ON DELETE CASCADE を付けている子の行は、REPLACE の削除と一緒に消えます。

INSERT IGNORE は、重複した行をとばして警告だけを出します。

INSERT IGNORE INTO product_stock (sku, name, stock)
VALUES ('B-010', 'ノート A5', 999);
SHOW WARNINGS;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1062 | Duplicate entry 'B-010' for key 'product_stock.uk_sku' |
+---------+------+--------------------------------------------------------+

B-010 の在庫は 50 のままです。困るのは、IGNORE が重複以外のエラーも警告に変えてしまう点です。NOT NULL の列に NULL を入れてみます。

INSERT IGNORE INTO product_stock (sku, name, stock)
VALUES ('E-300', NULL, 10);
SHOW WARNINGS;

SELECT * FROM product_stock WHERE sku = 'E-300';
+---------+------+------------------------------+
| Level   | Code | Message                      |
+---------+------+------------------------------+
| Warning | 1048 | Column 'name' cannot be null |
+---------+------+------------------------------+

+----+-------+------+-------+------+
| id | sku   | name | stock | note |
+----+-------+------+-------+------+
| 14 | E-300 |      |    10 |      |
+----+-------+------+-------+------+

本来はエラーで止まるはずの NULL が、空の文字列に変わって入りました。取り込みに INSERT IGNORE を使うなら、SHOW WARNINGS で警告の中身を確かめる手順を必ず入れてください。

よくある落とし穴

更新でも AUTO_INCREMENT の番号が進む

InnoDB では、ON DUPLICATE KEY UPDATE が更新で終わったときも、AUTO_INCREMENT の番号を1つ使います。タグの数を数える小さな表で試します。

CREATE TABLE tag_count (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  tag  VARCHAR(30) NOT NULL UNIQUE,
  cnt  INT NOT NULL
);

-- mysql を3回、sql を1回
INSERT INTO tag_count (tag, cnt) VALUES ('mysql', 1) AS new
ON DUPLICATE KEY UPDATE cnt = tag_count.cnt + 1;
-- (同じ文をあと2回)
INSERT INTO tag_count (tag, cnt) VALUES ('sql', 1) AS new
ON DUPLICATE KEY UPDATE cnt = tag_count.cnt + 1;

SELECT * FROM tag_count;
+----+-------+-----+
| id | tag   | cnt |
+----+-------+-----+
|  1 | mysql |   3 |
|  4 | sql   |   1 |
+----+-------+-----+

2行しかないのに、sql の id は 4 です。mysql の2回の更新で 2 と 3 が使われました。在庫表で D-200 が 7 になったのも、その前に A-002 を入れた2つの文(更新と、変化なし)が 5 と 6 を使ったからです。

1日に何百万回も数を足す表だと、INT の上限(2147483647)に思ったより早く届きます。数えるための表には AUTO_INCREMENT の id を付けず、daily_pv のように数える単位そのものを主キーにすると、この問題は起きません。欠番が出るほかの理由と番号のリセットは「AUTO_INCREMENTのリセットと確認方法:欠番が出る理由も」で説明しています。

UNIQUE が2つ以上ある表では1行しか更新されない

たとえば email と login_name の両方に UNIQUE がある会員の表で、新しい行が email では A さん、login_name では B さんとぶつかったとします。このとき更新されるのは、どちらか1行だけです。MySQL の公式ドキュメントでも、UNIQUE が複数ある表ではこの書き方を避けるよう書かれています。

こうした表に取り込むときは、先にそれぞれのキーで SELECT して、ぶつかる行が2行にならないかを確かめておくと、思っていない行が書きかわるのを防げます。

まとめ

ON DUPLICATE KEY UPDATE は、主キーか UNIQUE の重複をきっかけに、INSERT を UPDATE に切りかえる書き方です。8.0.19 以降は AS new で入れようとした値を参照し、VALUES() は使わないようにします。書かなかった列の値を残したいときは、REPLACE よりこちらが向いています。INSERT IGNORE は重複以外のエラーまで警告にするので、使うなら SHOW WARNINGS を確かめてください。回数を数える表は、AUTO_INCREMENT の id を付けない作りにすると番号切れを防げます。

もっと学ぶなら【PR】

  • MySQL徹底入門 第5版:MySQL の機能を広く解説した本。日付関数などの細かい動きを調べたいときに

※Amazonのアソシエイトとして、当サイトは適格販売により収入を得ています。

参考リンク

アイキャッチ画像: Photo by Lucas George Wendt on Unsplash

タイトルとURLをコピーしました