56,677
社区成员
发帖
与我相关
我的任务
分享
-- 这个您可以自定义插入语句 针对事务表
CREATE PROCEDURE `insert_many_rows_v5`(In loops int,In sqlstring varchar(255))
BEGIN
DECLARE v1 int;
set v1 = loops;
set @stmt_sql = sqlstring;
start transaction;
prepare stmt from @stmt_sql;
while v1>0 do
execute stmt;
set v1 =v1-1;
end while;
DEALLOCATE prepare stmt;
commit;
END;
CREATE DEFINER=`coolwind`@`%` PROCEDURE `insert_many_rows_v1`(In loops int)
BEGIN
DECLARE v1 int;
set v1 = loops;
while v1>0 do
insert into t(firstname,lastname,age) values('qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt',0);
set v1 =v1-1;
end while;
END;
-- 如果是innodb 可以加入事务 这样速度很快
CREATE DEFINER=`coolwind`@`%` PROCEDURE `insert_many_rows_v3`(In loops int)
BEGIN
DECLARE v1 int;
set v1 = loops;
start transaction;
while v1>0 do
insert into t(firstname,lastname,age) values('qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt',0);
set v1 =v1-1;
end while;
commit;
END;
-- 这个您可以自定义插入语句
CREATE DEFINER=`coolwind`@`%` PROCEDURE `insert_many_rows_v4`(In loops int,In sqlstring varchar(255))
BEGIN
DECLARE v1 int;
set v1 = loops;
set @stmt_sql = sqlstring;
prepare stmt from @stmt_sql;
while v1>0 do
execute stmt;
set v1 =v1-1;
end while;
DEALLOCATE prepare stmt;
END;
DELIMITER $$
DROP PROCEDURE IF EXISTS `dsdvrdbm`.`test` $$
CREATE PROCEDURE `test` ()
BEGIN
set @v_n=1;
while @v_n<1000
do
insert into no_test(id,name,sex) values (@v_n,'MM','f');
set @v_n=@v_n+1;
END while ;
END $$
DELIMITER ;
call test();