CODEWITHSUNDEEP
mysqlsql
John
John

Reputation: 41

Pass list of values to be inserted as parameter to stored procedure

I have this stored procedure in MySQL version 5.7. If I want to create n elements it would be much faster to insert n elements in one statement two times rather than call the stored procedure n times.

How can I create a stored procedure that takes a "list" of names, a team id and does this?

CREATE PROCEDURE create_data_user(IN name VARCHAR(100), IN data_user_team_id INT)
BEGIN
START TRANSACTION;
INSERT INTO users (users.name, users.type)
VALUES (name, "team_data");
INSERT INTO team_members (team_id, user_id, mod_time)
VALUES (data_user_team_id, LAST_INSERT_ID(), UNIX_TIMESTAMP());
COMMIT;
END ;;

A stored procedure that creates two elements would look like this:

CREATE PROCEDURE create_data_user(IN name VARCHAR(100), IN name2 VARCHAR(100), IN 
data_user_team_id INT)
BEGIN
START TRANSACTION;
INSERT INTO users (users.name, users.type)
VALUES 
(name, "team_data"),
(name2, "team_data");
INSERT INTO team_members (team_id, user_id, mod_time)
VALUES 
(data_user_team_id, LAST_INSERT_ID(), UNIX_TIMESTAMP()),
(data_user_team_id, LAST_INSERT_ID()+1, UNIX_TIMESTAMP());
COMMIT;
END ;;

Edit: There are different ways to tackle this. You could just scrap the SP and copy-paste batch insert code. Rick James presented a complex solution, but I think you can do something simpler, perhaps at some cost in performance (but better than many individual inserts).

On the caller side you do something like this:

CREATE TEMPORARY TABLE names (name);

INSERT INTO names
VALUES
(name1),
(name2),
...;

CALL create_data_users(1);

And then the stored procedure is like this:

CREATE PROCEDURE create_data_users(IN data_user_team_id INT)
BEGIN
START TRANSACTION;
    INSERT INTO users (users.name, users.type) 
    SELECT name, "team_data" FROM names; 
    SET @num=LAST_INSERT_ID(); 
    INSERT INTO team_members (team_id, user_id, mod_time) 
    SELECT data_user_team_id, @num:=@num+1, UNIX_TIMESTAMP() FROM names;
COMMIT;
END ;;

Upvotes: 0

Views: 2525

Answers (2)

Rick James
Rick James

Reputation: 142278

The best efficient generalization, especially for long lists, I have found is to

  1. Build a temp table
  2. Use a batch INSERT to populate the table
  3. INSERT ... SELECT ... to get the items in the real table
  4. Do a complex update with a join to pull back all the ids.

(No loops involved.)

Specifics: http://mysql.rjweb.org/doc.php/staging_table#normalization

(That is focused around the very likely situation where the names are already in the table. However, it should work fine for your case.)

Upvotes: 1

Akina
Akina

Reputation: 42622

CREATE PROCEDURE create_data_users (IN users_list TEXT, IN data_user_team_id INT)
BEGIN
    DECLARE name VARCHAR(255);
    REPEAT
        SET name = TRIM(SUBSTRING_INDEX(users_list, ',', 1));
        SET users_list = CASE WHEN LOCATE(',', users_list)
                              THEN TRIM(SUBSTRING(users_list FROM 1 + LOCATE(',', users_list)))
                              ELSE '' END;
        INSERT INTO users (users.name, users.type)
        VALUES (name, "team_data");
        INSERT INTO team_members (team_id, user_id, mod_time)
        VALUES (data_user_team_id, LAST_INSERT_ID(), UNIX_TIMESTAMP());
    UNTIL users_list = '' END REPEAT;
END

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=37e397c1066e1e8459df70fc6131e5d4

Upvotes: 0

Related Questions