MySQL - Upsert

MySQL - UPSERT 操作

什么是 UPSERT？

UPSERT 是 UPDATE（更新）和 INSERT（插入）的合成词。它是一种数据库操作，如果表中不存在新行，则插入新行；如果已存在，则更新现有行。这在同步数据、跟踪统计信息或导入记录时是常见的需求。

要执行 upsert，表必须具有 PRIMARY KEY 或 UNIQUE 索引。MySQL 使用此键来确定某行是否为“重复”行。

我们使用 product_inventory 表作为示例：

CREATE TABLE product_inventory (
    product_sku VARCHAR(50) PRIMARY KEY,
    quantity INT NOT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO product_inventory (product_sku, quantity)
VALUES
    ('LAPTOP-001', 25),
    ('MOUSE-002', 150);

方法 1：INSERT … ON DUPLICATE KEY UPDATE (推荐)

这是在 MySQL 中执行 upsert 最灵活、最强大且最广泛使用的方法。它尝试执行 INSERT，如果因重复键冲突而失败，则转而执行 UPDATE 子句。

语法

INSERT INTO table_name (col1, col2, ...)
VALUES (val1, val2, ...)
ON DUPLICATE KEY UPDATE
    col1 = new_val1, col2 = new_val2, ...;

示例：更新库存

假设我们收到一批新货。我们想更新 ‘MOUSE-002’ 的数量并添加一个新产品 ‘KEYBOARD-003’。

-- 这将更新现有的 'MOUSE-002' 记录
INSERT INTO product_inventory (product_sku, quantity) VALUES ('MOUSE-002', 200)
ON DUPLICATE KEY UPDATE quantity = 200;
-- 查询成功，影响 2 行

-- 这将插入新的 'KEYBOARD-003' 记录
INSERT INTO product_inventory (product_sku, quantity) VALUES ('KEYBOARD-003', 75)
ON DUPLICATE KEY UPDATE quantity = 75;
-- 查询成功，影响 1 行

更强大的模式是使用 VALUES() 函数，它引用本来要插入的值：

-- 将现有产品的数量增加 50
INSERT INTO product_inventory (product_sku, quantity) VALUES ('LAPTOP-001', 50)
ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity);

方法 2：INSERT IGNORE

INSERT IGNORE 语句尝试插入一行，但如果发生重复键错误，它会静默忽略该错误并丢弃新行。它不执行更新。

用例：最适合批量加载数据，当存在一些重复记录时，您希望防止整个操作失败。您只想插入新的数据。

示例

-- 这将被忽略，因为 'LAPTOP-001' 已经存在。
INSERT IGNORE INTO product_inventory (product_sku, quantity) VALUES ('LAPTOP-001', 999);
-- 查询成功，影响 0 行，1 个警告

-- 'LAPTOP-001' 的原始数量保持不变。

方法 3：REPLACE

REPLACE 语句的工作方式与 INSERT 类似，但如果旧行在 PRIMARY KEY 或 UNIQUE 索引上与新行具有相同的值，则旧行会在插入新行之前被删除。

警告： 请谨慎使用 REPLACE。因为它执行的是 DELETE 后再 INSERT，所以它具有显著的副作用。

示例

-- 这将删除现有的 'MOUSE-002' 记录并插入一条新记录。
REPLACE INTO product_inventory (product_sku, quantity) VALUES ('MOUSE-002', 175);

比较：您应该使用哪种 UPSERT 方法？

方法	重复时的行为	副作用	最佳应用场景
INSERT … ON DUPLICATE KEY UPDATE	更新现有行。	副作用最小。AUTO_INCREMENT 不受影响。触发器正确触发。	真正的 upsert、数据同步、计数器更新。
INSERT IGNORE	跳过插入，无更改。	副作用最小。生成警告而不是错误。	批量导入数据时，希望跳过重复项而不中断。
REPLACE	删除旧行，插入新行。	显著。会生成一个新的 AUTO_INCREMENT ID。DELETE 和 INSERT 触发器会触发。可能发生外键级联。	极少数情况下，需要完全覆盖记录且副作用可接受时。

在绝大多数情况下，INSERT ... ON DUPLICATE KEY UPDATE 是最安全、最可预测且最强大的选择。

常见陷阱和最佳实践

• REPLACE 副作用： 务必注意 REPLACE 是一个破坏性操作。如果其他表有指向被替换行的外键，则该操作的 DELETE 部分可能会产生您不希望的级联效应。
• 多列唯一键： 这三种方法都适用于多列 UNIQUE 键。重复项检查将对键中所有列的组合执行。
• 检查受影响行： 在 INSERT ... ON DUPLICATE KEY UPDATE 查询之后，返回的受影响行数在插入时为 1，更新现有行时为 2（一个删除标记，一个插入），如果新值与旧值相同则为 0。