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

You can have as many inserts as you like but with three parameters you have not that man possibilities. — nbk
– nbk, Commented Nov 24, 2023 at 9:29
Why do you use a function? It seems that the operation can be performed with single INSERT which uses recursive CTE or synthetic/service numbers table. All pre-conditions may be easily tested in WHERE. — Akina
– Akina, Commented Nov 24, 2023 at 10:14
I want the server to do the work which includes all the validation of the parameters, doing it outside a function means the validation has to be done outside too. — SPlatten
– SPlatten, Commented Nov 24, 2023 at 10:19
Again - this can be performed with single query. Both checking and insertion. The number of effected rows will report the output which you need in. — Akina
– Akina, Commented Nov 24, 2023 at 10:40

Pavan Bhatt · Accepted Answer · 2023-11-24 09:21:33Z

0

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();

answered Nov 24, 2023 at 9:21

Pavan Bhatt

14411 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SPlatten Over a year ago

I've edited my post with the content of the function as it is now...the timing doesn't seem right either, when I start the call, I count it myself and it takes around 5 seconds to create 100,000 records, but reports only 0.930171000 ?

Akina Over a year ago

@SPlatten it takes around 5 seconds to create 100,000 records, but reports only 0.930171000 ? 0.9s is spent for the code execution, additional 4s is needed for the inserted rows to be saved on disk, for example..

Bill Karwin · Accepted Answer · 2023-11-24 18:12:33Z

0

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.

answered Nov 24, 2023 at 18:12

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

2 Comments

SPlatten Over a year ago

I like the lateral thinking, so you are actually inserting just a single record which is a carrier for the JSON array ?

Bill Karwin Over a year ago

Read about JSON_TABLE(). It turns a JSON array into a set of rows.

Collectives™ on Stack Overflow

MySQL/MariaDB Create multiple inserts from function

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related