mysql> delimiter //
mysql> create function fn_str(@str varchar(1000))
-> returns varchar(5000)
-> begin
-> declare @r varchar(8000);
-> set @r='%';
-> while len(@str)>0
-> select @r=@+left(@str,1)+'%',@str=stuff(@str,1,1,'');
-> return @r;
-> end;
-> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '@str
varchar(1000))
returns varchar(5000)
begin
declare @r varchar(8000);
set @r' at line 1请问这样写是格式上问题还是什么,在sql中不要 ; 可以运行,可在mysql中不要分号,要了分号都报这错,不知道到底该改哪,请教了~~~~~
mysql> create function fn_str(@str varchar(1000))
-> returns varchar(5000)
-> begin
-> declare @r varchar(8000);
-> set @r='%';
-> while len(@str)>0
-> select @r=@+left(@str,1)+'%',@str=stuff(@str,1,1,'');
-> return @r;
-> end;
-> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '@str
varchar(1000))
returns varchar(5000)
begin
declare @r varchar(8000);
set @r' at line 1请问这样写是格式上问题还是什么,在sql中不要 ; 可以运行,可在mysql中不要分号,要了分号都报这错,不知道到底该改哪,请教了~~~~~
-> returns varchar(5000)
-> begin
-> declare @r varchar(8000);
-> set @r='%';
-> while len(@str)>0
-> select @r=@+left(@str,1)+'%',@str=stuff(@str,1,1,'');
-> return @r;
-> end;
-> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '@r va
rchar(8000);
set @r='%';
while len(@str)>0
select @r=@+left(@str,1)+'%',' at line 4还是报错~~~~~我查手册@r是变量~~~~~
declare r varchar(8000);
字符串连接用concat
delimiter //
create function fn_str(str varchar(1000))
returns varchar(5000)
begin
declare r varchar(8000);
set r='%';
while char_length(str)>0 do
set r=concat(r,left(str,1),'%');
set str=substring(str,2);
end while;
return r;
end;
//
Query OK, 0 rows affected (0.00 sec)mysql> select @aa;
+------+
| @aa |
+------+
| 2 |
+------+
1 row in set (0.01 sec)
这个是mysql的定义变量而存储过程中定义变量不用@
直接 declare a int(1);
mysql> create function fn_str(str varchar(1000))
-> returns varchar(5000)
-> begin
-> declare r varchar(8000);
-> set r='%';
-> while len(str)>0 do
-> set r= concat(left(str,1),'%');
-> set str=mid(str,2);
-> return r;
-> END WHILE;
->
-> end;
-> //
Query OK, 0 rows affected (0.08 sec)mysql>
mysql> declare s varchar(200);
-> set s='sql手册';
-> select * from ta where title like dbo.fn_str(s);
-> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'decla
re s varchar(200);
set s='sql手册';
select * from ta where title like dbo.f' at line 1
set @s = 'sql手册';
select * from ta where title like dbo.fn_str(@s);
//
delimiter ;
是的,MYSQL中 @代码是 session 变量
-> //
+------+-----------------------------------------+
| id | title |
+------+-----------------------------------------+
| 001 | 深入浅出SQLServer2005开发管理与应用实例 |
| 006 | SQLServer2000高级管理与开发 |
| 007 | SQLServer2005数据库开发详解 |
+------+-----------------------------------------+
3 rows in set (0.01 sec)mysql> set @s = 'sql手册';
-> select * from ta where title like dbo.fn_str(@s);
-> //
Query OK, 0 rows affected (0.00 sec)ERROR 1305 (42000): FUNCTION dbo.fn_str does not exist---------------------------------------
ACMAIN_CHM 我用你的创建方法是可以成功的但使用就总会报错,而用zhoupuyue 的方法创建和使用都可以,使用我没有声明ok。我总结出sql和mysql在创建函数使用上很有区别~