如何针对ID列表中的每个ID执行MySQL事务？

问题描述 #

我原本有一段单条插入的SQL脚本：

SET @userId=12345;

SET @roleId=5;

BEGIN;
INSERT INTO mydatabase.Users(userId, roleId) VALUES (@userId, @roleId);
COMMIT;

现在希望对指定的userId列表（比如[12345, 23456, 34567]）执行相同的插入操作，伪代码逻辑如下：

SET @userIdList=[12345, 23456, 34567];

SET @roleId=5;

FOR EACH id in @userIdList:
    BEGIN;
    INSERT INTO mydatabase.Users(userId, roleId) VALUES (@userId, @roleId);
    COMMIT;
END

请问在MySQL中实现该需求的正确语法是什么？

实现方法 #

方法1：单条INSERT批量插入（推荐） #

直接用一条INSERT语句插入多条数据，无需循环，效率更高且事务易管理：

SET @roleId=5;

BEGIN;
INSERT INTO mydatabase.Users(userId, roleId)
VALUES 
(12345, @roleId),
(23456, @roleId),
(34567, @roleId);
COMMIT;

这是MySQL官方推荐的批量操作方式，能减少数据库连接开销，避免不必要的事务提交次数。

方法2：存储过程实现循环逻辑 #

如果业务必须用循环执行，可以创建存储过程遍历userId列表：

DELIMITER //

CREATE PROCEDURE InsertBatchUsers()
BEGIN
    DECLARE roleId INT DEFAULT 5;
    DECLARE userIdList CURSOR FOR 
        SELECT 12345 UNION ALL SELECT 23456 UNION ALL SELECT 34567;
    DECLARE currentUserId INT;
    DECLARE done INT DEFAULT FALSE;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN userIdList;
    read_loop: LOOP
        FETCH userIdList INTO currentUserId;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        BEGIN;
        INSERT INTO mydatabase.Users(userId, roleId) VALUES (currentUserId, roleId);
        COMMIT;
    END LOOP;
    CLOSE userIdList;
END //

DELIMITER ;

-- 调用存储过程执行批量插入
CALL InsertBatchUsers();

注意：每条插入单独提交会增加IO开销，非业务必需的话，建议把事务放在循环外，循环内仅执行插入，最后一次性提交。

方法3：临时表配合INSERT...SELECT #

先将userId列表存入临时表，再通过查询完成批量插入：

SET @roleId=5;

-- 创建临时表并写入userId列表
CREATE TEMPORARY TABLE TempUserIds (userId INT);
INSERT INTO TempUserIds VALUES (12345), (23456), (34567);

BEGIN;
INSERT INTO mydatabase.Users(userId, roleId)
SELECT userId, @roleId FROM TempUserIds;
COMMIT;

-- 可选：用完临时表后删除
DROP TEMPORARY TABLE TempUserIds;

这种方式适合userId列表较长，或者需要多次复用该列表的场景。

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