あればその行を更新し、なければ新しく追加する処理は、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のアソシエイトとして、当サイトは適格販売により収入を得ています。
参考リンク
- MySQL 8.0 Reference Manual: INSERT … ON DUPLICATE KEY UPDATE Statement
- MySQL 8.0 Reference Manual: REPLACE Statement
- MySQL 8.0 Reference Manual: INSERT Statement
- PostgreSQL Documentation: INSERT(ON CONFLICT)
アイキャッチ画像: Photo by Lucas George Wendt on Unsplash
