MySQL 8.0.36存储过程中获取INSERT...ON DUPLICATE KEY的增改ID列表

由于ROW_COUNT()仅返回受影响的总行数，无法区分具体是哪些ID被新增或更新，也没有直接等价于mysql_affected_rows()的内置函数可以返回明细ID，我们可以通过临时表+关联查询的方式实现需求，以下是具体实现示例：

假设存在业务主表test_table，结构如下：

CREATE TABLE test_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

实现步骤与存储过程示例 #

DELIMITER //

CREATE PROCEDURE batch_upsert_and_get_ids(IN input_data JSON)
BEGIN
    -- 1. 创建临时表存储待插入/更新的原始数据
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_batch_data (
        id INT,
        name VARCHAR(50),
        PRIMARY KEY (id)
    );

    -- 2. 将输入数据导入临时表（示例用JSON格式传入，可根据实际调整传入方式）
    INSERT INTO temp_batch_data (id, name)
    SELECT 
        JSON_UNQUOTE(JSON_EXTRACT(item, '$.id')) AS id,
        JSON_UNQUOTE(JSON_EXTRACT(item, '$.name')) AS name
    FROM JSON_TABLE(input_data, '$[*]' COLUMNS (item JSON PATH '$')) AS json_rows;

    -- 3. 创建临时表存储最终的变更记录（ID+操作类型）
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_change_log (
        id INT PRIMARY KEY,
        operation ENUM('INSERT', 'UPDATE') NOT NULL
    );

    -- 4. 执行UPSERT操作，同时记录被更新的ID
    INSERT INTO test_table (id, name)
    SELECT id, name FROM temp_batch_data
    ON DUPLICATE KEY UPDATE
        name = VALUES(name),
        -- 嵌套INSERT语句记录更新ID，避免重复写入
        id = (INSERT INTO temp_change_log (id, operation) VALUES (OLD.id, 'UPDATE') ON DUPLICATE KEY UPDATE operation = 'UPDATE');

    -- 5. 补充记录被新增的ID（主表中存在、临时输入表中存在且未被记录为更新的ID）
    INSERT INTO temp_change_log (id, operation)
    SELECT t.id, 'INSERT'
    FROM test_table t
    JOIN temp_batch_data tb ON t.id = tb.id
    LEFT JOIN temp_change_log tc ON t.id = tc.id
    WHERE tc.id IS NULL;

    -- 6. 输出所有变更的ID及操作类型
    SELECT id, operation FROM temp_change_log ORDER BY id;

    -- 清理临时表（会话结束会自动销毁，此处可选）
    DROP TEMPORARY TABLE IF EXISTS temp_batch_data;
    DROP TEMPORARY TABLE IF EXISTS temp_change_log;
END //

DELIMITER ;

调用示例 #

假设我们要批量处理以下数据：更新ID为5、23、91的记录，新增ID为92、93、94、95的记录，可通过以下方式调用存储过程：

CALL batch_upsert_and_get_ids('[
    {"id":5, "name":"updated_name_5"},
    {"id":23, "name":"updated_name_23"},
    {"id":91, "name":"updated_name_91"},
    {"id":92, "name":"new_name_92"},
    {"id":93, "name":"new_name_93"},
    {"id":94, "name":"new_name_94"},
    {"id":95, "name":"new_name_95"}
]');

调用后会返回所有被新增/更新的ID及对应的操作类型，结果如下：

方案说明 #

• 临时表temp_batch_data用于承接待处理的批量数据，避免直接与主表交互时的复杂关联
• 在UPSERT的UPDATE分支中，通过嵌套INSERT...ON DUPLICATE KEY UPDATE语句，直接记录被更新的ID
• 新增的ID通过主表与临时输入表的关联查询，排除已被记录为更新的ID，从而精准获取新增记录的ID
• 所有变更记录都存储在temp_change_log临时表中，可直接查询获取明细，也可根据业务需求进一步处理

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