MySQL多行INSERT...ON DUPLICATE KEY UPDATE如何存储新增/更新ID

针对MySQL中多行INSERT ... ON DUPLICATE KEY UPDATE场景下无法批量获取所有新增/更新记录ID的问题，推荐使用临时表预存待插入数据+关联查询的方案，无需循环执行单条语句，也不用依赖3551的局限性。

实现步骤 #

• 创建临时表存储所有待执行Upsert的数据，临时表需包含目标表的唯一键（用于后续匹配）。
• 基于临时表执行批量Upsert操作。
• 通过关联临时表与目标表，根据唯一键匹配，一次性获取所有被操作记录的ID。

代码示例 #

假设你的目标表db.tab的唯一键是a列（请根据实际场景替换为你的唯一键字段）：

-- 1. 创建临时表存储待插入数据，确保唯一键与目标表一致
CREATE TEMPORARY TABLE tmp_upsert_data (
    a INT NOT NULL,
    b INT NOT NULL,
    c INT NOT NULL,
    PRIMARY KEY (a) -- 与目标表的UNIQUE KEY对应
);

-- 2. 插入所有待Upsert的数据到临时表
INSERT INTO tmp_upsert_data (a, b, c)
VALUES (1,2,3), (4,5,6);

-- 3. 执行批量Upsert
INSERT INTO db.tab (a, b, c)
SELECT a, b, c FROM tmp_upsert_data
ON DUPLICATE KEY UPDATE 
    c = tmp_upsert_data.c; -- 按需更新其他字段

-- 4. 获取所有被插入/更新记录的ID
SELECT tab.id 
FROM db.tab
JOIN tmp_upsert_data ON tab.a = tmp_upsert_data.a;

方案优势 #

• 无需循环执行单条Upsert，性能更优，尤其适合大数据量场景。
• 不依赖3551的单行限制，直接通过唯一键关联获取所有目标ID，结果准确可靠。
• 临时表会在会话结束后自动销毁，无需额外清理（也可手动执行DROP TEMPORARY TABLE IF EXISTS tmp_upsert_data;）。

备选方案：触发器+临时表 #

如果无法提前预存待插入数据，可使用触发器捕获每一条操作的ID：

-- 创建临时表存储ID
CREATE TEMPORARY TABLE tmp_operation_ids (id INT UNSIGNED NOT NULL);

-- 创建AFTER INSERT触发器，捕获新增记录的ID
DELIMITER //
CREATE TRIGGER trg_tab_insert
AFTER INSERT ON db.tab
FOR EACH ROW
BEGIN
    INSERT INTO tmp_operation_ids VALUES (NEW.id);
END //

-- 创建AFTER UPDATE触发器，捕获更新记录的ID
CREATE TRIGGER trg_tab_update
AFTER UPDATE ON db.tab
FOR EACH ROW
BEGIN
    INSERT INTO tmp_operation_ids VALUES (NEW.id);
END //
DELIMITER ;

-- 执行批量Upsert
INSERT INTO db.tab (a,b,c)
VALUES (1,2,3), (4,5,6)
ON DUPLICATE KEY UPDATE 
    c = VALUES(c);

-- 获取所有操作的ID
SELECT id FROM tmp_operation_ids;

-- 清理触发器（MySQL无临时触发器，需手动删除）
DROP TRIGGER IF EXISTS trg_tab_insert;
DROP TRIGGER IF EXISTS trg_tab_update;

注意：触发器方案需注意并发场景，同一会话内多次操作需清理触发器，避免重复插入ID；另外触发器会带来一定性能开销，更适合小数据量场景。

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