我是参考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块就可以了。
解决方案 »
- 问一下mysql的循环中有没中止函数啊,谢谢
- 一条MySQL查询语句,卡死机器,不知道为什么,求高手指点!
- 查询出来的两条记录,如何拼接相同的字段的内容
- SQL 查询语句?
- 怎么将mysql数据库文件(.sql)直接完整导入到数据库中?
- 请高手帮翻译一下以下语句
- 数据库中有两个字段,date1和date2,数据库类型同为datetime,现在有第三个字段days,怎样才能将它的默认值设为date2-date1?
- 谁会写mysql的sql,保留前十个记录,删除以后的
- update 的连立修改问题
- ubunt mysql 主从复制 二进制日志起不了
- 关于大量数据更新的性能
- 求在MYSQL中栏位里面的数据再加入多一个字符
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