MySQL/MariaDB Create multiple inserts from function

Question

In an SQL function I want to create multiple records based on the parameters passed into the function, for example the function takes a member primary ID, a date/time stamp and a number specifying the number of records to create.

I could of course create a loop to perform the inserts, however I am curious to know if there is a way to get the server engine to create a specific number of records automatically ?

My function so far:

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

Answer 1

Can you try below adding instead of using RETURN 1;?

WITH RECURSIVE numbers AS (
 SELECT 1 AS number
 UNION ALL
 SELECT number + 1 FROM numbers WHERE number < intTickets
 )
INSERT INTO tblTickets(member_id, timestamp)
SELECT idMbr, dtWhen
FROM numbers;
RETURN ROW_COUNT();

Answer 2

Here's another solution. I create a JSON array with a number of elements equal to the tickets requested. It doesn't matter what values are in this array, it's the length of the array that is important.

I convert that array to a set of rows using JSON_TABLE(), and use that as a source for an INSERT...SELECT statement, inserting a set of rows in one statement, without using a loop.

By joining to the tblMembers table, it will produce zero rows if there is no member matching the input.

I also followed a habit of naming the function arguments differently than any columns referenced by the query.

CREATE FUNCTION reserveTickets(p_idMbr BIGINT, p_dtWhen DATETIME, p_intTickets INT)
RETURNS VARCHAR(24)
MODIFIES SQL DATA
BEGIN
    DECLARE dtStartRef DATETIME;
    DECLARE ordinals JSON;

    SET dtStartRef = SYSDATE(3);
    SET ordinals = CAST(CONCAT('[', REPEAT('1,', p_intTickets-1), '1]') AS JSON);

    INSERT INTO tblTickets(idMbr, dtWhen)
    SELECT p_idMbr, p_dtWhen
    FROM tblMembers
    CROSS JOIN JSON_TABLE(ordinals, '$[*]' COLUMNS(ord FOR ORDINALITY)) AS j
    WHERE tblMembers.id = p_idMbr;

    RETURN TIMESTAMPDIFF(MICROSECOND, SYSDATE(3), dtStartRef);
END

I tested this with MySQL 8.2. In my test, the timestampdiff returns wildly erratic values, sometimes even resulting a negative diff. I'm not going to attempt to explain that.

But the time reported by the client is consistently about 0.24 or 0.25 seconds.