MySQL中执行INSERT ON DUPLICATE UPDATE后如何获取增改行ID

针对你的需求，有两种无需依赖临时表的简便实现方式：

方式一：利用MySQL 8.0.19+的RETURNING子句（推荐） #

MySQL 8.0.19及以上版本支持在INSERT ... ON DUPLICATE KEY UPDATE语句后添加RETURNING子句，可直接返回所有新增/更新行的id，这是最直接的方案。

事务执行流程： #

1. 开启事务
2. 执行批量插入/更新Player表，同时获取目标玩家id：

INSERT INTO Player(name, level) VALUES (?, ?), (?, ?), ... ON DUPLICATE KEY UPDATE level = VALUES(level) RETURNING id;

执行后会得到包含所有处理过的玩家id的结果集。
3. 清除该角色的旧关联记录：

DELETE FROM PlayerCharacter WHERE characterId = ?;

4. 用步骤2拿到的id批量插入新关联记录：

INSERT INTO PlayerCharacter(playerId, characterId) VALUES (?, ?), (?, ?), ...;

（每个playerId对应同一个指定的characterId，批量生成参数即可）
5. 提交事务，若任意步骤失败则回滚。

方式二：兼容低版本MySQL #

如果你的MySQL版本低于8.0.19，可通过后续查询获取目标id：
由于你传入的[name, level]数组内无重复名称，在完成Player表的插入/更新后，直接通过name列表查询对应的id即可。

事务执行流程： #

1. 开启事务
2. 执行批量插入/更新Player表：

INSERT INTO Player(name, level) VALUES (?, ?), (?, ?), ... ON DUPLICATE KEY UPDATE level = VALUES(level);

3. 查询所有目标玩家的id：

SELECT id FROM Player WHERE name IN (?, ?, ...);

4. 清除该角色的旧关联记录：

DELETE FROM PlayerCharacter WHERE characterId = ?;

5. 用步骤3拿到的id批量插入新关联记录：

INSERT INTO PlayerCharacter(playerId, characterId) VALUES (?, ?), (?, ?), ...;

6. 提交事务，若任意步骤失败则回滚。

注意事项： #

• 必须确保整个流程在事务中执行，避免出现数据不一致的情况
• NodeJS中使用预编译查询时，注意批量参数的正确绑定（如mysql2库支持数组形式的批量参数）

内容的提问来源于stack exchange，提问作者user25485418