MySQL 没有 MERGE/RETURNING：REPLACE、ON DUPLICATE KEY UPDATE、INSERT IGNORE 的坑与选型

标准 SQL:2003 定义了 MERGE：一条语句按条件完成 INSERT/UPDATE/DELETE，也就是 upsert。MySQL 至今不支持 MERGE，社区从 2005 年起就在请求；MariaDB 也不支持 MERGE。SQL 标准 MERGE 里，RETURNING 是扩展。MySQL 没有 RETURNING。

MySQL 侧常见的替代是三件套：REPLACE、INSERT ... ON DUPLICATE KEY UPDATE、INSERT IGNORE。它们都非 ANSI 标准，并且都依赖目标表上已存在的 PRIMARY KEY / UNIQUE 约束；不能像 MERGE 的 ON 谓词那样自定义匹配条件，没有 WHEN NOT MATCHED BY SOURCE 对应物，也没有 OUTPUT / RETURNING 对应物。

-- 匹配条件由 PK/UNIQUE 决定，不能写成 MERGE 那样的 ON 谓词
INSERT INTO t (id, name) VALUES (1, 'a')
ON DUPLICATE KEY UPDATE name = VALUES(name); -- MySQL 8.0.20 起 VALUES(col) 弃用

REPLACE：冲突时先 DELETE 再 INSERT

REPLACE 的语义不是 UPDATE，而是冲突时先 DELETE 再 INSERT。

REPLACE INTO t (id, name) VALUES (1, 'a');

这带来几个直接后果：

• 未列出的列被重置为默认值/NULL，不是保留旧值；
• 有 AUTO_INCREMENT 会分配新 id；
• 触发 DELETE + INSERT 触发器，而不是 UPDATE 触发器；
• 有外键时，ON DELETE 会联动；若级联删除会删掉别的表行；若外键阻止删除，则整个 REPLACE 失败；
• 需要同时有 INSERT 和 DELETE 权限；
• 表上有多个 UNIQUE 索引时，可能删除多行冲突行。

INSERT ... ON DUPLICATE KEY UPDATE：冲突时原地 UPDATE

INSERT ... ON DUPLICATE KEY UPDATE 冲突时执行 UPDATE，未提及的列保持不变。

INSERT INTO t (id, name)
VALUES (1, 'a') AS new
ON DUPLICATE KEY UPDATE name = new.name;

几个容易踩的点：

• affected rows：INSERT=1，UPDATE=2，值未变=0；带 CLIENT_FOUND_ROWS 时未变算 1；
• 有 AUTO_INCREMENT 列时，即使最终是 update，也会消耗一个自增值，LONG ID 序列出现空洞；LAST_INSERT_ID() 返回该自增值；
• 表上有多个 UNIQUE 索引时，谁先匹配更新谁，只更新一行，不推荐；
• 引用插入值：老写法 VALUES(col) 在 MySQL 8.0.20 起被弃用，改用行别名 INSERT ... AS new ON DUPLICATE KEY UPDATE col = new.col；
• 语句型复制下，多行 INSERT ... SELECT ON DUPLICATE KEY UPDATE 被标记为 unsafe；MySQL 8 默认行复制，影响较小。

INSERT IGNORE：冲突时静默跳过

INSERT IGNORE INTO t (id, name) VALUES (1, 'a');

冲突时不报错，只发 warning。想拿到“哪些被跳过”，除非解析 mysql_info() 的 Duplicates，否则不直接可见。

MariaDB 10.5+ 的 RETURNING

MySQL 无 RETURNING。MariaDB 10.5+ 支持 INSERT ... RETURNING、REPLACE ... RETURNING、DELETE ... RETURNING（UPDATE ... RETURNING 到 13.0），且可与 ON DUPLICATE KEY UPDATE 组合。

INSERT INTO orders (product, qty) VALUES ('widget', 5)
RETURNING id, created_at;

DELETE FROM queue WHERE processed=1
RETURNING id;

限制：RETURNING 里子查询返回多行/多列不可用；聚合函数不可用；要拿行数可用 ROW_COUNT()。

选型对照表

参考链接

• 官方 MERGE（标准 SQL）说明
• MySQL ON DUPLICATE KEY UPDATE
• MySQL REPLACE
• MariaDB INSERT ... RETURNING
• MariaDB REPLACE
• MSSQL OUTPUT 对照 / 迁移 playbook