一種模糊查詢,應用搜尋引擎的分詞機制

說明工作原理:

例子:

對於查詢

              "我可以理解你嗎"

拆分為"我可","可以","以理","理解","解你","你嗎"

查詢到兩個符,則權值加上相應的值:"我可"+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