我是参考sqlserver的sql块的写法的:set @v_n=0;
while(@v_n<1000)
begin
insert into no_test(id,name,sex) values(@v_n,'MM','f'); commit;
set @v_n=@v_n+1;
end ;但是不行啊,执行失败,我的mysql版本是5.1.41。大家指点下,如何写这样一个sql块,在命令行里面执行成功。存储过程我会写,但是这样的小功能,不需要单独写存储过程吧,sql块就可以了。
while(@v_n<1000)
begin
insert into no_test(id,name,sex) values(@v_n,'MM','f'); commit;
set @v_n=@v_n+1;
end ;但是不行啊,执行失败,我的mysql版本是5.1.41。大家指点下,如何写这样一个sql块,在命令行里面执行成功。存储过程我会写,但是这样的小功能,不需要单独写存储过程吧,sql块就可以了。
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();
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 ;
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;
-- 这个您可以自定义插入语句 针对事务表
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;
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
WHILE语句内的语句或语句群被重复,直至search_condition 为真。WHILE语句可以被标注。 除非begin_label也存在,end_label才能被用,如果两者都存在,它们必须是一样的。例如:CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
...
SET v1 = v1 - 1;
END WHILE;
END