不用写存储过程,MYSQL中直接有函数。REPEAT(str,count) Returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL. mysql> SELECT REPEAT('MySQL', 3); -> 'MySQLMySQLMySQL'
SET @a='1,'; SET @b=''; SET @i=1; WHILE @i<=2 DO SET @b=CONCAT(@b,@a,'1,'); SET @i=@i+1; END WHILE; SELECT @b;
这个不太正确,需要改一下 WHILE @i <=3 DO SET @b=CONCAT(@b,@a);
begin declare a int; declare b varchar(5000); set a=1; while a<10 do set b=REPEAT(a,'+,'); set b = concat(b,''); set a=a+1; end while; select b; end 想输出:1,2,3,4...
begin declare a int; declare b varchar(5000); set a=1; while a<10 do set b=concat(a,'+,'); set a=a+1; end while; select b; end 这样写什么得不到想得的结果??
mysql> DELIMITER $$ mysql> drop PROCEDURE sp_starnight_cbj$$ Query OK, 0 rows affected (0.01 sec)mysql> CREATE PROCEDURE sp_starnight_cbj() -> BEGIN -> declare a int; -> declare b varchar(5000); -> set a=1; -> set b=''; -> while a<10 do -> set b = concat(b,',',a); -> set a=a+1; -> end while; -> select b; -> END -> $$ Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ; mysql> mysql> call sp_starnight_cbj(); +--------------------+ | b | +--------------------+ | ,1,2,3,4,5,6,7,8,9 | +--------------------+ 1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>
可以用字符SUBSTR把第一个,去掉。
变量B没有累加 set b=''; ... while a<10 do set b=concat(b,a,'+,');
不用自己写循环,直接用函数即可:set b= repeat('1,',5); select b;
OK明白,看到那个@和declare声明的变量有什么区别?
mysql> select repeat('1,',5) as a; +------------+ | a | +------------+ | 1,1,1,1,1, | +------------+ 1 row in set (0.01 sec)mysql>
-> 'MySQLMySQLMySQL'
SET @b='';
SET @i=1;
WHILE @i<=2 DO
SET @b=CONCAT(@b,@a,'1,');
SET @i=@i+1;
END WHILE;
SELECT @b;
WHILE @i <=3 DO
SET @b=CONCAT(@b,@a);
declare a int;
declare b varchar(5000);
set a=1;
while a<10 do
set b=REPEAT(a,'+,');
set b = concat(b,'');
set a=a+1;
end while;
select b;
end
想输出:1,2,3,4...
begin
declare a int;
declare b varchar(5000);
set a=1;
while a<10 do
set b=concat(a,'+,');
set a=a+1;
end while;
select b;
end
这样写什么得不到想得的结果??
mysql> drop PROCEDURE sp_starnight_cbj$$
Query OK, 0 rows affected (0.01 sec)mysql> CREATE PROCEDURE sp_starnight_cbj()
-> BEGIN
-> declare a int;
-> declare b varchar(5000);
-> set a=1;
-> set b='';
-> while a<10 do
-> set b = concat(b,',',a);
-> set a=a+1;
-> end while;
-> select b;
-> END
-> $$
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;
mysql>
mysql> call sp_starnight_cbj();
+--------------------+
| b |
+--------------------+
| ,1,2,3,4,5,6,7,8,9 |
+--------------------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>
set b='';
...
while a<10 do
set b=concat(b,a,'+,');
select b;
+------------+
| a |
+------------+
| 1,1,1,1,1, |
+------------+
1 row in set (0.01 sec)mysql>
B只是你存储过程的内部一个变量,是不同的变量
@为用户变量,只可以当前连接访问,当前连接断掉则释放了
declare声明的变量为局部变量,作用域是在declare 的begin ...end中
mysql> select substring(repeat("1,",5),1,length(repeat("1,",5))-1);
+------------------------------------------------------+
| substring(repeat("1,",5),1,length(repeat("1,",5))-1) |
+------------------------------------------------------+
| 1,1,1,1,1 |
+------------------------------------------------------+
1 row in set (0.00 sec)