Mysql stored procedures insert large amounts of data in batches

delimiter ;;
CREATE PROCEDURE insertData(in sum INT) 
BEGIN 
DECLARE count INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
set count=0;
set i = rand() * 10000;
set @exesql = concat("insert into t(a,b) values ");
set @exedata = "";
while count<sum do 
    set @exedata = concat(@exedata, "(, '", i, "', '", i+1."')");
    set count=count+1;
    set i=i+1;
    if i%1000=0
    then 
        set @exedata = SUBSTRING(@exedata, 2);
        set @exesql = concat("insert into t(a,b) values ", @exedata);
        prepare stmt from @exesql;
        execute stmt;
        DEALLOCATE prepare stmt;
        set @exedata = "";
    end if;
end while;

if length(@exedata)>0 
then 
    set @exedata = SUBSTRING(@exedata, 2);
    set @exesql = concat("insert into t(a,b) values ", @exedata);
    prepare stmt from @exesql;
    execute stmt;
    DEALLOCATE prepare stmt;
end if;

end;


delimiter ;

// Insert 100,000 in just over 20 seconds
Copy the code

`