如何在MySQL函数中自动生成指定数量的插入记录？

我希望在SQL函数中根据传入的参数生成多条记录，参数包括会员主键ID、时间戳以及要创建的记录数量。目前我通过循环实现插入操作，但好奇是否存在让数据库引擎自动创建指定数量记录的方法。以下是我当前的函数代码：

CREATE DEFINER=`root`@`localhost` FUNCTION `reserveTickets`(idMbr BIGINT(11),                                                          
                           dtWhen DATETIME, intTickets INT(11)) RETURNS varchar(24) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
BEGIN
    DECLARE dtStartRef DATETIME;
    DECLARE idMbrVerified BIGINT(11);
    DECLARE intTicket INT(11);
/*Initialise reference time*/    
    SET dtStartRef = NOW(3);
/*Is member id valid?*/
    SELECT id INTO idMbrVerified FROM tblMembers WHERE id=idMbr;
    IF ISNULL(idMbrVerified) THEN 
        RETURN 0;
    END IF;    
/*Is date/time valid, can't be older that 60 seconds?*/
    IF SECOND(TIMEDIFF(NOW(), dtWhen)) > 60 THEN
        RETURN 0;
    END IF;
/*Is the number of tickets valid, must be at least 1?*/    
    IF intTickets <= 0 THEN
        RETURN 0;    
    END IF;
/*Create the records*/  
    SET intTicket = 0;
    insertTicket: LOOP
        IF intTicket < intTickets THEN
            INSERT INTO tblTickets (idMbr, dtWhen) VALUES (idMbr, dtWhen);
        ELSE
            LEAVE insertTicket;
        END IF;
        SET intTicket = intTicket + 1;
    END LOOP insertTicket;    
    RETURN TIMESTAMPDIFF(MICROSECOND, dtStartRef, NOW(3)) / 100000;
END

优化方案：用递归CTE实现批量插入 #

在MySQL 8.0及以上版本中，可以利用**递归CTE（公共表表达式）**生成指定数量的行，然后一次性完成插入操作，替代循环逻辑，大幅提升性能。

修改后的函数代码如下：

CREATE DEFINER=`root`@`localhost` FUNCTION `reserveTickets`(idMbr BIGINT(11),                                                          
                           dtWhen DATETIME, intTickets INT(11)) RETURNS varchar(24) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
BEGIN
    DECLARE dtStartRef DATETIME;
    DECLARE idMbrVerified BIGINT(11);
/*Initialise reference time*/    
    SET dtStartRef = NOW(3);
/*Is member id valid?*/
    SELECT id INTO idMbrVerified FROM tblMembers WHERE id=idMbr;
    IF ISNULL(idMbrVerified) THEN 
        RETURN 0;
    END IF;    
/*Is date/time valid, can't be older that 60 seconds?*/
    IF SECOND(TIMEDIFF(NOW(), dtWhen)) > 60 THEN
        RETURN 0;
    END IF;
/*Is the number of tickets valid, must be at least 1?*/    
    IF intTickets <= 0 THEN
        RETURN 0;    
    END IF;
/*Create the records via recursive CTE (no loop needed)*/  
    INSERT INTO tblTickets (idMbr, dtWhen)
    SELECT idMbr, dtWhen
    FROM (
        WITH RECURSIVE ticket_numbers AS (
            SELECT 1 AS num
            UNION ALL
            SELECT num + 1 FROM ticket_numbers WHERE num < intTickets
        )
        SELECT * FROM ticket_numbers
    ) AS temp;
    
    RETURN TIMESTAMPDIFF(MICROSECOND, dtStartRef, NOW(3)) / 100000;
END

方案说明 #

1. 递归CTE生成行数：通过ticket_numbers递归生成从1到intTickets的连续数字，每一行对应一条票务记录。
2. 批量插入：一次性将所有生成的行插入tblTickets，避免了循环中多次执行INSERT的解析和执行开销，数据量越大优势越明显。
3. 低版本兼容方案：若使用MySQL 8.0以下版本，可以预先创建一个包含足够多连续数字的辅助表（比如numbers表，存储1到10000的数字），然后通过SELECT idMbr, dtWhen FROM numbers LIMIT intTickets来获取指定数量的行完成插入。

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