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

CodePudding user response：

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

CodePudding user response：

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.)