MySQL 多行 upsert 如何获取并保存插入或更新的 id?
结论
LAST_INSERT_ID() 只能返回一个值,无法收集多行 upsert 中每条记录的 id。通常可以先批量执行 INSERT ... ON DUPLICATE KEY UPDATE,再根据输入数据的 UNIQUE KEY 回查目标表,将匹配到的 id 写入 tmp_ids。
如果无法通过稳定的唯一键回查,可以逐行执行 upsert,并在每次执行后立即保存 LAST_INSERT_ID()。
为什么临时表里只有一个 id
下面的语句只执行一次,所以只会写入一行:
INSERT INTO tmp_ids VALUES(LAST_INSERT_ID());
id=LAST_INSERT_ID(tab.id) 会在更新分支中设置当前连接保存的值,但不会追加 id 记录,也不会让后面的语句按输入行数重复执行。
普通的多行 INSERT 中,LAST_INSERT_ID() 返回的是第一个成功插入行生成的 AUTO_INCREMENT 值,而不是最后一个 id。批量 upsert 还可能执行 LAST_INSERT_ID(expr),因此也不能认为它固定返回最后一个输入行的 id。它无法提供完整的 id 列表。MySQL 官方文档:LAST_INSERT_ID()
按 UNIQUE KEY 回查并保存全部 id
原问题没有给出 UNIQUE KEY 的具体列。下面以 UNIQUE KEY (a,b) 为例,作以下假设:
a、b均为NOT NULL,本批次的唯一键冲突由(a,b)决定。- upsert 只更新
c,不会改变用于回查的(a,b)或目标记录的id,也没有 trigger 改写这些值。 - 目标表使用 InnoDB,所有语句都在同一个连接中执行。
示例沿用问题中的 AS incoming 语法,该语法从 MySQL 8.0.19 开始支持。MySQL 官方文档:行别名支持
DROP TEMPORARY TABLE IF EXISTS tmp_ids;
CREATE TEMPORARY TABLE tmp_ids
ENGINE = InnoDB
AS
SELECT id
FROM db.tab
WHERE 1 = 0;
START TRANSACTION;
INSERT INTO db.tab (a,b,c)
VALUES
(1,2,3), (4,5,6) AS incoming
ON DUPLICATE KEY UPDATE
c = incoming.c;
INSERT INTO tmp_ids (id)
SELECT t.id
FROM db.tab AS t
WHERE (t.a, t.b) IN ((1,2), (4,5))
FOR UPDATE;
COMMIT;
SELECT id
FROM tmp_ids;
tmp_ids 通过空查询创建,无需自行猜测目标表 id 的整数类型。批量 upsert 完成后,第二条 INSERT 按唯一键查找目标行并保存 id。无论目标行是刚插入的还是原先已存在的,即使 c 的值没有实际变化,都能查到对应记录。
写入和回查在同一个事务中进行,并使用 FOR UPDATE 读取当前数据,避免回查依赖较早的一致性读快照。锁在事务结束时释放;发生错误时,应用程序应回滚事务。MySQL 官方文档:锁定读
如果实际 UNIQUE KEY 是单列 a,回查条件应改成 a IN (...);如果是其他组合,则必须使用对应的全部列。MySQL 可能因任意 UNIQUE 索引或 PRIMARY KEY 冲突进入更新分支,因此存在多个业务唯一约束时,需要确认回查条件能定位到被更新的行。MySQL 官方文档:ON DUPLICATE KEY UPDATE
需要与每条输入逐一对应时
上面的 IN 查询返回目标记录集合。如果同一批输入中有多条数据使用相同的唯一键,查询结果中只会出现该目标记录一次,返回顺序也不保证与输入顺序一致。
如果需要知道每条输入对应哪个 id,可以先把输入放入临时表,保存 seq 和唯一键列,再通过 JOIN 查询目标表,并按 seq 排序。即使多条输入对应同一个目标 id,也能分别保留映射。数据量较大时,也可以直接从输入临时表执行批量 upsert,避免在回查 SQL 中重复列出整批键值。
逐行执行也是可行方案
如果更看重实现简单,可以为每条输入重复执行下面这组语句。tmp_ids 需提前创建,整个循环必须使用同一个连接:
INSERT INTO db.tab (a,b,c)
VALUES
(1,2,3) AS incoming
ON DUPLICATE KEY UPDATE
c = incoming.c,
id=LAST_INSERT_ID(tab.id);
INSERT INTO tmp_ids (id)
VALUES (LAST_INSERT_ID());
每次 upsert 成功后立即保存 id,再处理下一条输入。这样可以记录插入行和更新行的 id;重复输入同一个唯一键时,也会保存多条结果。应用程序应使用参数绑定传入数据,并在事务成功提交后将这些 id 作为成功结果使用。
逐行处理会增加 SQL 执行次数和网络往返。把循环放在一个事务中可以减少逐条提交的成本,但实际性能差距仍取决于批量大小、连接延迟和数据库负载。
注意事项
不要根据某个返回 id 加上行号推算其他 id。upsert 可能更新已有记录,而且 AUTO_INCREMENT 分配可能存在空洞,整批结果不保证连续。MySQL 官方文档:ON DUPLICATE KEY UPDATE 与 AUTO_INCREMENT
临时表和 LAST_INSERT_ID() 都依赖当前连接。使用连接池时,应在完成整批操作后再归还连接,并及时清理临时表。
按唯一键回查得到的是对应记录的 id,不能据此判断每条输入执行了 INSERT 还是 UPDATE。如果业务需要区分操作类型,就要另外记录。
备注:内容仅供参考。
