begin @tname = @tname + @a; insert into testTable(username,userpwd) values (@tname,'a'); @tname ='test'; @a = @a +1; end; 这之间的变量赋值,难道不用set @var = ....?用的是SQL Server TSQL的语法?
在mysql运行 不懂mysql 的语法是这样吗?
给你一个mysql的: create table t (name varchar(32), no int);mysql> delimiter // mysql> create procedure test_insert (a int) -> begin -> set @i = 1; -> while @i < a do -> insert into t values('abc', @i); -> set @i = @i + 1; -> end while; -> end -> // Query OK, 0 rows affected (0.00 sec)mysql> call test_insert(100000);// Query OK, 0 rows affected (2 min 10.13 sec)
要存储过程:delimiter $$ create procedure add_date() begin declare i int default 0; while i<=10 do insert into cdr(id,userfield) values(i,'a'); set i=i+1; end while; end;$$ delimiter ;
mysql> select * from cdr; Empty set (0.00 sec)mysql> drop procedure add_date; Query OK, 0 rows affected (0.00 sec)mysql> delimiter $$ mysql> create procedure add_date() -> begin -> declare i int default 0; -> while i<=10 do -> insert into cdr(id,userfield) values(i,'a'); -> set i=i+1; -> end while; -> end;$$ Query OK, 0 rows affected (0.00 sec)mysql> delimiter ; mysql> call add_date(); Query OK, 1 row affected (0.39 sec)mysql> select * from cdr; +------+-----------+ | id | userfield | +------+-----------+ | 0 | a | | 1 | a | | 2 | a | | 3 | a | | 4 | a | | 5 | a | | 6 | a | | 7 | a | | 8 | a | | 9 | a | | 10 | a | +------+-----------+ 11 rows in set (0.00 sec)
@tname = @tname + @a;
insert into testTable(username,userpwd) values (@tname,'a');
@tname ='test';
@a = @a +1;
end;
这之间的变量赋值,难道不用set @var = ....?用的是SQL Server TSQL的语法?
create table t (name varchar(32), no int);mysql> delimiter //
mysql> create procedure test_insert (a int)
-> begin
-> set @i = 1;
-> while @i < a do
-> insert into t values('abc', @i);
-> set @i = @i + 1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)mysql> call test_insert(100000);//
Query OK, 0 rows affected (2 min 10.13 sec)
create procedure add_date()
begin
declare i int default 0;
while i<=10 do
insert into cdr(id,userfield) values(i,'a');
set i=i+1;
end while;
end;$$
delimiter ;
Empty set (0.00 sec)mysql> drop procedure add_date;
Query OK, 0 rows affected (0.00 sec)mysql> delimiter $$
mysql> create procedure add_date()
-> begin
-> declare i int default 0;
-> while i<=10 do
-> insert into cdr(id,userfield) values(i,'a');
-> set i=i+1;
-> end while;
-> end;$$
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> call add_date();
Query OK, 1 row affected (0.39 sec)mysql> select * from cdr;
+------+-----------+
| id | userfield |
+------+-----------+
| 0 | a |
| 1 | a |
| 2 | a |
| 3 | a |
| 4 | a |
| 5 | a |
| 6 | a |
| 7 | a |
| 8 | a |
| 9 | a |
| 10 | a |
+------+-----------+
11 rows in set (0.00 sec)