INSERT … ON DUPLICATE KEY UPDATE¶

INSERT … ON DUPLICATE KEY UPDATE 用于在向数据库表中插入数据时，如果数据已经存在，则更新该数据，否则插入新的数据。

语法描述¶

INSERT ... ON DUPLICATE KEY UPDATE 用于在向数据库表中插入数据时，如果数据已经存在，则更新该数据，否则插入新的数据。

INSERT INTO 语句是用于向数据库表中插入数据的标准语句；ON DUPLICATE KEY UPDATE 语句用于在表中有重复记录时进行更新操作。如果表中存在具有相同唯一索引或主键的记录，则使用 UPDATE 子句来更新相应的列值，否则使用 INSERT 子句插入新记录。

INSERT ... ON DUPLICATE KEY UPDATE 支持检测 PRIMARY KEY 和二级 UNIQUE KEY 约束上的冲突。当发现冲突时，会更新行而不是插入。冲突解决优先级为：PRIMARY KEY > UNIQUE KEY（按定义顺序）。如果传入行在多个唯一键上与不同已存在行冲突，则以第一个冲突键为准。

ON DUPLICATE KEY UPDATE 支持所有表类型，包括：

无显式主键的表（fake PK + UNIQUE KEY）
包含外键的表
包含全文索引（fulltext index）的表
包含向量索引（ivfflat）的表
包含前缀唯一索引的表
包含 ON UPDATE CURRENT_TIMESTAMP 列的表

语法结构¶

> INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
  [ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, column3 = value3, ...];

示例¶

CREATE TABLE user (
    id INT(11) NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT(3) NOT NULL
);
-- 插入一条新数据，id 不存在，于是录入新数据
INSERT INTO user (id, name, age) VALUES (1, 'Tom', 18)
ON DUPLICATE KEY UPDATE name='Tom', age=18;

mysql> select * from user;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | Tom  |   18 |
+------+------+------+
1 row in set (0.01 sec)

-- 将一个已经存在的记录的 age 字段增加 1，同时 name 字段保持不变
INSERT INTO user (id, name, age) VALUES (1, 'Tom', 18)
ON DUPLICATE KEY UPDATE age=age+1;

mysql> select * from user;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | Tom  |   19 |
+------+------+------+
1 row in set (0.00 sec)

-- 插入一条新记录，将 name 和 age 字段更新为指定值
INSERT INTO user (id, name, age) VALUES (2, 'Lucy', 20)
ON DUPLICATE KEY UPDATE name='Lucy', age=20;

mysql> select * from user;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | Tom  |   19 |
|    2 | Lucy |   20 |
+------+------+------+
2 rows in set (0.01 sec)

高级示例¶

无主键表（Fake PK + UNIQUE KEY）¶

有 UNIQUE KEY 但没有显式 PRIMARY KEY 的表完全支持。系统使用隐藏的 fake primary key 进行内部行标识。

DROP TABLE IF EXISTS t_odku_fakepk;
CREATE TABLE t_odku_fakepk (a INT, b VARCHAR(20), UNIQUE KEY(a));

INSERT INTO t_odku_fakepk VALUES (1, 'x');
-- UNIQUE KEY 冲突 -> 更新
INSERT INTO t_odku_fakepk VALUES (1, 'y') ON DUPLICATE KEY UPDATE b = 'updated';
SELECT * FROM t_odku_fakepk ORDER BY a;

-- 无冲突 -> 插入新行
INSERT INTO t_odku_fakepk VALUES (2, 'new') ON DUPLICATE KEY UPDATE b = 'z';
SELECT * FROM t_odku_fakepk ORDER BY a;

DROP TABLE IF EXISTS t_odku_fakepk;

外键表¶

ON DUPLICATE KEY UPDATE 支持包含外键约束的子表。外键验证是行作用域的：只验证该语句自身的行，不验证整个表。引用有效父键的更新会成功；引用不存在的父键会失败。

DROP TABLE IF EXISTS t_odku_child;
DROP TABLE IF EXISTS t_odku_parent;
CREATE TABLE t_odku_parent (id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE t_odku_child (
    cid INT PRIMARY KEY,
    pid INT,
    v INT,
    FOREIGN KEY (pid) REFERENCES t_odku_parent(id)
);

INSERT INTO t_odku_parent VALUES (1, 'p1'), (2, 'p2');
INSERT INTO t_odku_child VALUES (10, 1, 100);

-- PK 冲突，更新非 FK 列
INSERT INTO t_odku_child VALUES (10, 1, 999) ON DUPLICATE KEY UPDATE v = 999;
SELECT * FROM t_odku_child ORDER BY cid;

-- ODKU 插入有效 FK 的新行
INSERT INTO t_odku_child VALUES (20, 2, 200) ON DUPLICATE KEY UPDATE v = 200;
SELECT * FROM t_odku_child ORDER BY cid;

DROP TABLE IF EXISTS t_odku_child;
DROP TABLE IF EXISTS t_odku_parent;

NULL 唯一键处理¶

全 NULL 的唯一键永远不会与另一个全 NULL 行冲突。每次使用全 NULL 唯一键值的 INSERT ... ON DUPLICATE KEY UPDATE 都会插入新行，而不是更新已存在的行。同一唯一键上的非 NULL 值仍然遵循正常的 ODKU 语义。

DROP TABLE IF EXISTS t_odku_null_unique;
CREATE TABLE t_odku_null_unique (a INT UNIQUE KEY, b INT);
-- 两行都插入新行，因为 NULL 永远不会与 NULL 冲突
INSERT INTO t_odku_null_unique VALUES (NULL, NULL) ON DUPLICATE KEY UPDATE b = VALUES(b);
INSERT INTO t_odku_null_unique VALUES (NULL, NULL) ON DUPLICATE KEY UPDATE b = VALUES(b);
SELECT COUNT(*) AS row_count FROM t_odku_null_unique;

-- 非 NULL 键遵循正常的 ODKU 语义
INSERT INTO t_odku_null_unique VALUES (1, 10) ON DUPLICATE KEY UPDATE b = VALUES(b);
INSERT INTO t_odku_null_unique VALUES (1, 20) ON DUPLICATE KEY UPDATE b = VALUES(b);
SELECT a, b FROM t_odku_null_unique WHERE a = 1;
DROP TABLE IF EXISTS t_odku_null_unique;

ON UPDATE CURRENT_TIMESTAMP 空操作检测¶

当表包含 ON UPDATE CURRENT_TIMESTAMP 列时，空操作的 ODKU（写入值等于现有值）不会推进时间戳。只有真正的值变更才会触发时间戳更新。

DROP TABLE IF EXISTS t_odku_onupdate;
CREATE TABLE t_odku_onupdate (
  id INT PRIMARY KEY,
  v INT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO t_odku_onupdate(id, v) VALUES (1, 10);

-- 空操作更新：时间戳不推进
INSERT INTO t_odku_onupdate(id, v) VALUES (1, 10) ON DUPLICATE KEY UPDATE v = v;
SELECT v, updated_at FROM t_odku_onupdate WHERE id = 1;

-- 真正变更：时间戳推进
INSERT INTO t_odku_onupdate(id, v) VALUES (1, 99) ON DUPLICATE KEY UPDATE v = VALUES(v);
SELECT v, updated_at FROM t_odku_onupdate WHERE id = 1;
DROP TABLE IF EXISTS t_odku_onupdate;

多列唯一键冲突解决¶

当存在多个 UNIQUE KEY 约束时，冲突优先级为：PRIMARY KEY > UNIQUE KEY（按定义顺序）。如果传入行在多个唯一键上与不同已存在行冲突，则以定义顺序中第一个冲突键为准，更新其所在行。

DROP TABLE IF EXISTS t_odku_realpk;
CREATE TABLE t_odku_realpk (
    id INT PRIMARY KEY,
    uk1 INT UNIQUE,
    uk2 INT UNIQUE,
    val INT
);
INSERT INTO t_odku_realpk VALUES (1, 10, 100, 1000), (2, 20, 200, 2000);

-- PK 冲突（最高优先级）：更新 id=1 的行
INSERT INTO t_odku_realpk VALUES (1, 99, 999, 5) ON DUPLICATE KEY UPDATE val = val + 1;
SELECT * FROM t_odku_realpk ORDER BY id;

-- 跨行冲突：uk1 命中第 1 行，uk2 命中第 2 行 -> uk1 优先（定义顺序）
INSERT INTO t_odku_realpk VALUES (4, 10, 200, 5) ON DUPLICATE KEY UPDATE val = val + 1;
SELECT * FROM t_odku_realpk ORDER BY id;

DROP TABLE IF EXISTS t_odku_realpk;

限制¶

对于没有任何 PRIMARY KEY 或 UNIQUE KEY 的表，ON DUPLICATE KEY UPDATE 退化为普通 INSERT（没有键就没有重复概念）。
子表上的 INSERT ... ON DUPLICATE KEY UPDATE 以行作用域验证外键：表中已存在的孤立行会被忽略，但语句产生的最终行镜像必须满足所有外键约束。