一种模糊查询,应用搜索引擎的分词机制

说明工作原理:

例子:

对于查询

              "我可以理解你吗"

拆分为"我可","可以","以理","理解","解你","你吗"

查询到两个符,则权值加上相应的值:"我可"+1,"可以"+2,"以理"+3,"理解"+4,"解你"+5,"你吗"+6.

 

以下为mysql的存储过程:

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_getFeeArea`(paddress varchar(800))

BEGIN
/*通过函数间接取得存储过程的值*/
declare addresslen,i int default 1;        /*申明变量*/

set addresslen=CHAR_LENGTH(trim(paddress));
set @casestr='';/*通过临时字符串方式得到数据的where条件*/
set @wherestr='';/*查询条件过滤*/
if(addresslen>1) then

while i<=addresslen-1 do
        /*通过select的权重条件*/
        set @casestr=concat(@casestr,'case when INSTR(a.addressname,',char(39),substring(paddress,i,2),char(39),')>0 then ',11-i,' else 0 end+');
        /*通过where的过滤条件*/
        set @wherestr=concat(@wherestr,'  INSTR(a.addressname,',char(39),substring(paddress,i,2),char(39),')>0  or');
 set i=i+1;
        if(i>10) then
           set i=addresslen+1;/*跳出循环*/
        end if;
end while;
/*去除最后的+号*/
set @casestr=substring(@casestr,1,CHAR_LENGTH(trim(@casestr))-1);

/*去除最后的or*/
set @wherestr=substring(@wherestr,1,CHAR_LENGTH(trim(@wherestr))-2);

/*组合查询的sql语句*/
 set @sql=concat('select a.mail_feearea,concat(a.send_office,',char(39),'(',char(39),',a.city_name,',char(39),',',char(39),',a.county_name,',char(39),')',char(39),') send_office,(',@casestr,') seqn  from td_mailfeearea a where ',@wherestr,' order by seqn desc ');

else
 set @sql=concat('select  ',char(39),char(39),'  mail_feearea,',char(39),char(39),'  send_office,',char(39),char(39),'  seqn ');
end if;
                    PREPARE stmt FROM @sql;
                    EXECUTE stmt ;
/*select @sql;*/
END