1. Create user table

CREATE TABLE user100w(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(10) NOT NULL,
  last_name VARCHAR(10) NOT NULL,
  sex VARCHAR(5) NOT NULL,
  score INT NOT NULL,
  copy_id INT NOT NULL,
  PRIMARY KEY (`id`)
);

2. Create stored procedure

DROP PROCEDURE IF EXISTS add_user;  
DELIMITER //
    create PROCEDURE add_user(in num INT)
    BEGIN
        DECLARE rowid INT DEFAULT 0;
        DECLARE firstname CHAR(1);
        DECLARE name1 CHAR(1);
        DECLARE name2 CHAR(1);
        DECLARE lastname VARCHAR(3) DEFAULT '';
        DECLARE sex CHAR(1);
        DECLARE score CHAR(2);
        WHILE rowid < num DO
        SET firstname = SUBSTRING('Zhao Qian Sun Li Zhou Wu Zheng Wang Lin Yang Liu Sun Chen Jiang Ruan Hou Zou Gao Peng Xu',FLOOR(1+21*RAND()),1); 
        SET name1 = SUBSTRING('One, two, three, four, six, seven, eight, three, three, three, three, three, three, three, three',ROUND(1+43*RAND()),1); 
        SET name2 = SUBSTRING('One, two, three, four, six, seven, eight, three, three, three, three, three, three, three, three',ROUND(1+43*RAND()),1); 
        SET sex=FLOOR(0 + (RAND() * 2));
        SET score= FLOOR(40 + (RAND() *60));
        SET rowid = rowid + 1;
        IF ROUND(RAND())=0 THEN 
        SET lastname =name1;
        END IF;
        IF ROUND(RAND())=1 THEN
        SET lastname = CONCAT(name1,name2);
        END IF;
        insert INTO user100w (first_name,last_name,sex,score,copy_id) VALUES (firstname,lastname,sex,score,rowid);  
        END WHILE;
    END //
DELIMITER ;

3. Call the stored procedure to add random user data, for example, randomly insert 1000000 pieces of data:

call add_user(1000000);

The results are as follows:

From the above results, it can be seen that using the above stored procedure to add 1 million random user data is very time-consuming, which takes almost an hour. Here is the optimized stored procedure:

DROP PROCEDURE IF EXISTS add_user_optimizition;  
DELIMITER //
    create PROCEDURE add_user_optimizition(in num INT)
    BEGIN
        DECLARE rowid INT DEFAULT 0;
        DECLARE firstname CHAR(1);
        DECLARE name1 CHAR(1);
        DECLARE name2 CHAR(1);
        DECLARE lastname VARCHAR(3) DEFAULT '';
        DECLARE sex CHAR(1);
        DECLARE score CHAR(2);
        SET @exedata = "";
        WHILE rowid < num DO
            SET firstname = SUBSTRING('Zhao Qian Sun Li Zhou Wu Zheng Wang Lin Yang Liu Sun Chen Jiang Ruan Hou Zou Gao Peng Xu',FLOOR(1+21*RAND()),1); 
            SET name1 = SUBSTRING('One, two, three, four, six, seven, eight, three, three, three, three, three, three, three, three',ROUND(1+43*RAND()),1); 
            SET name2 = SUBSTRING('One, two, three, four, six, seven, eight, three, three, three, three, three, three, three, three, three',ROUND(1+43*RAND()),1); 
            SET sex=FLOOR(0 + (RAND() * 2));
            SET score= FLOOR(40 + (RAND() *60));
            SET rowid = rowid + 1;
            IF ROUND(RAND())=0 THEN 
            SET lastname =name1;
            END IF;
            IF ROUND(RAND())=1 THEN
            SET lastname = CONCAT(name1,name2);
            END IF;
            IF length(@exedata)>0 THEN
            SET @exedata = CONCAT(@exedata,',');
            END IF;
            SET @exedata=concat(@exedata,"('",firstname,"','",lastname,"','",sex,"','",score,"','",rowid,"')");
            IF rowid%1000=0
            THEN 
                SET @exesql =concat("insert into user100w_optimizition(first_name,last_name,sex,score,copy_id) values ", @exedata);
                prepare stmt from @exesql;
                execute stmt;
                DEALLOCATE prepare stmt;
                SET @exedata = "";
            END IF;
        END WHILE;
        IF length(@exedata)>0 
        THEN
            SET @exesql =concat("insert into user100w_optimizition(first_name,last_name,sex,score,copy_id) values ", @exedata);
            prepare stmt from @exesql;
            execute stmt;
            DEALLOCATE prepare stmt;
        END IF; 
    END //
DELIMITER ;

Create a table structure similar to the above user table as follows:

CREATE TABLE user100w_optimizition(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(10) NOT NULL,
  last_name VARCHAR(10) NOT NULL,
  sex VARCHAR(5) NOT NULL,
  score INT NOT NULL,
  copy_id INT NOT NULL,
  PRIMARY KEY (`id`)
);

Execute optimized stored procedures:

 call add_user_optimizition(1000001);

The results are as follows:

After optimization, we can see that millions of data only need 50 seconds of execution time, and the optimization effect is very significant. Compared with the stored procedures before and after optimization, it is not difficult to find that I just modified the structure of sql here to combine multiple sql into one execution. From this case, we conclude that if multiple pieces of data are inserted into the same table at one time, the efficiency of assembling the insert statements into one piece will be higher.