函数原型
set global log_bin_trust_function_creators = 1;
drop function if exists ld;
drop function if exists sim;
delimiter @
create function ld(s1 varchar(25),s2 varchar(25)) returns varchar(256)
begin
declare s1_len int default 0;
declare s2_len int default 0;
declare i int default 0;
declare j int default 0;
declare _i int default 0;
declare _j int default 0;
declare pos int default 0;
declare temp int default 0;
declare ss varchar(625);
declare _ss varchar(625);
declare _t1 varchar(1);
declare _t2 varchar(1);
declare _t3 varchar(1);
set s1_len=length(s1);
set s2_len=length(s2);
If s1_len=0 then return s2_len;
end if;
If s2_len=0 then return s1_len;
end if;
Set ss=repeat('0',(s1_len+1)*(s2_len+1));
Set i=0;
myloop1:loop
set i=i+1;
set _ss=concat(substring(ss,1,i-1),i-1,substring(ss,i+1));
set ss=_ss;
if i>s1_len then leave myloop1;
end if;
end loop myloop1;
set i=0;
myloop2:loop
set _i=i*(s1_len+1);
set i=i+1;
set _ss=concat(substring(ss,1, _i),i-1,substring(ss,_i+2));
set ss=_ss;
if i>s2_len then leave myloop2;
end if;
end loop myloop2;
set i=1;
myloop3:loop
set i=i+1;
set _t1=substring(s1,i-1,1);
set j=1;
myloop4:loop
set _j=j*(s1_len+1);
set j=j+1;
set _t2=substring(s2,j-1,1);
if _t1=_t2 then set temp=0;
else set temp=1;
end if;
set pos=(i-1)*(s2_len+1)+j;
set _ss=concat(substring(ss,1,pos-1),least(substring(ss,pos-1,1) + 1,substring(ss,pos-1-s2_len,1) + 1, substring(ss,pos-1-s2_len-1,1) + temp), substring(ss,pos+1));
set ss=_ss;
if j>s2_len then leave myloop4;
end if;
end loop myloop4;
If i>s1_len then leave myloop3;
end if;
end loop myloop3;
Return substring(ss,-1,1);
end @
Create function sim(s1 varchar(25),s2 varchar(25)) returns float
begin
declare i int default 0;
declare ldval float default 0;
set i=ld(s1,s2);
set ldval = 1 - i / greatest(length(s1),length(s2));
return ldval;
end @
此函数返回的是字符串的相似度
这样使用select sim('12','123')一点问题都没有
为什么这样不行 select user_like from user where sim(user_like,'123')>0.6 。。。。。。。
求高手解答.....在线等
------解决方案--------------------
我来领分的