SQL多条件模糊查询解决方案(类似百度搜索)

来源：互联网发布：手机怎么多注册淘宝号编辑：程序博客网时间：2024/04/29 00:36

参考博文：

SQL LIKE语句多条件贪婪匹配算法

SQL LIKE语句多条件贪婪加权匹配算法(改进版)

SQL LIKE语句多条件贪婪加权匹配(新增必要词指定)

前言：

算法的基本特性在前几篇博客中已经做了详细的说明，经过不断的改进优化，到归仓的时候了，也就是说，该算法告一段落，不再更新。

作为最终的解决方案，简要的总结一下算法特性，以方便读者参阅。

l 目的：主要用于多条件模糊匹配。

l 贪婪特性：返回满足条件尽可能多的记录。

l 权重特性：为关键词分配权重，代表关键词的重要性，在不破坏贪婪特性的前提下，返回权重高的记录。

l 必要关键词指定特性：在不破坏贪婪特性和权重特性的前提下，返回的结果中必须包含指定的关键词。

l 典型应用：问-答系统，例如百度提问、京东商品咨询。

经过分析，在最终的解决方案中，提供两个版本的算法，已经封装成存储过程和函数，直接导入数据库即可。

普通版本：

l 描述：基于SQL的LIKE语句实现，使用简单，但受限于LIKE语句，不适合超大数据量处理。指定必要词会加快处理速度。

l 使用范围：万级别的数据量，数据量超过1万条，将导致运行缓慢。

l 使用方法：直接在查询分析器中运行脚本导入数据库即可。

l 调用示例：execute proc_Common_SuperLike'id','t_test','content','20','|','[i]|o|c'

l 参数说明：id表的主键字段名称。t_test表名。content匹配内容字段名称。20选出20个记录（从顶至下匹配度越来越低）。|关键字的分隔符号。[i]|o|c一共有i，o，c三个关键字，通过|分隔，其中i是必要词。

[sql] view plaincopyprint?
GO  
CREATE function Get_StrArrayLength  
(  
 @str varchar(1024),  --要分割的字符串  
 @split varchar(10)  --分隔符号  
)  
returns int  
as  
 begin  
  declare @location int  
  declare @start int  
  declare @length int  
  set @str=ltrim(rtrim(@str))  
  set @location=charindex(@split,@str)  
  set @length=1  
   while @location<>0  
     begin  
      set @start=@location+1  
      set @location=charindex(@split,@str,@start)  
      set @length=@length+1  
     end  
   return @length  
 end  
 GO  
 CREATE function Get_StrArrayStrOfIndex  
(  
 @str varchar(1024),  --要分割的字符串  
 @split varchar(10),  --分隔符号  
 @index int --取第几个元素  
)  
returns varchar(1024)  
as  
begin  
 declare @location int  
 declare @start int  
 declare @next int  
 declare @seed int  
 set @str=ltrim(rtrim(@str))  
 set @start=1  
 set @next=1  
 set @seed=len(@split)  
 set @location=charindex(@split,@str)  
 while @location<>0 and @index>@next  
   begin  
    set @start=@location+@seed  
    set @location=charindex(@split,@str,@start)  
    set @next=@next+1  
   end  
 if @location =0 select @location =len(@str)+1  
   
--这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。  
 return substring(@str,@start,@location-@start)  
end  
GO  
CREATE PROCEDURE proc_Common_SuperLike  
    --要查询的表的主键字段名称  
    @primaryKeyName varchar(999),  
    --要查询的表名  
    @talbeName varchar(999),  
    --要查询的表的字段名称，即内容所在的字段  
    @contentFieldName varchar(999),  
    --查询记录的个数(TOP *),匹配的个数越多,排名越靠前  
    @selectNumber varchar(999),  
    --匹配字符分隔标记  
    @splitString varchar(999),  
    --匹配字符组合字符串  
    @words varchar(999)  
      
AS  
    declare @sqlFirst varchar(999)  
    declare @sqlCenter varchar(999)  
    declare @sqlLast varchar(999)  
    declare @next int    
    declare @arrayLength int  
    declare @newWords varchar(999)  
    declare @newTable varchar(999)  
BEGIN  
    set @newTable=@talbeName  
    set @newWords=@words  
    set @next=dbo.Get_StrArrayLength(@words,'[')  
    --判断是否有必要词  
    if @next>1  
    begin  
        set @newTable=''  
        --构造必要表sql语句  
        while @next>1  
        begin  
            set @newTable=@newTable+@contentFieldName+' like ''%'+dbo.Get_StrArrayStrOfIndex(dbo.Get_StrArrayStrOfIndex(@words,'[',@next),']',1)+'%'' AND '  
            set @next=@next-1  
        end  
        set @newTable=left(@newTable,(len(@newTable)-4))  
        --构造临时表  
        set @newTable='SELECT * into ##tempTable FROM '+ @talbeName + ' WHERE ' + @newTable  
        execute(@newTable)  
        --指定临时表  
        set @newTable='##tempTable'  
        --去掉关键词组中的必要词标记  
        set @newWords=REPLACE(REPLACE(@words,'[',''),']','')  
    end  
    set @sqlCenter=''  
    set @next=1  
    set @arrayLength=dbo.Get_StrArrayLength(@newWords,@splitString)  
  
    while @next<=@arrayLength  
    begin  
        --构造sql查询条件(中间部分)  
        set @sqlCenter = @sqlCenter+'SELECT '+@primaryKeyName+','+CONVERT(varchar(999),@arrayLength-@next+1)+' AS wordPower FROM '+@newTable+' WHERE '+@contentFieldName+' like ''%'+dbo.Get_StrArrayStrOfIndex(@newWords,@splitString,@next)+'%'' UNION ALL '  
        set @next=@next+1  
    end  
    --处理sql语句中间部分,去除最后无用语句  
    set @sqlCenter=left(@sqlCenter,(len(@sqlCenter)-10))  
    --构造sql语句开头部分  
    set @sqlFirst='SELECT TOP '+@selectNumber+' '+@primaryKeyName+',COUNT(*)+SUM(wordPower) AS finalPower FROM ('  
    --构造sql语句结尾部分  
    set @sqlLast=') AS t_Temp GROUP BY '+@primaryKeyName+' ORDER BY finalPower DESC'  
    --拼接出完整sql语句,并执行  
    Execute(@sqlFirst+@sqlCenter+@sqlLast)  
    --判断临时表是否存在，存在则删除，一定要删除！  
    if OBJECT_ID('tempDb..##tempTable') is not null  
    begin  
        drop table ##tempTable  
    end  
END  

大数据量版本：

l 描述：基于SQL的全文索引实现，使用较为复杂，但执行速度极快，适合处理大数据量。指定必要词会降低处理速度。

l 使用范围：千万级别的数据量，i3一代笔记本处理器，查询1千万条记录仅需2秒。

l 使用方法：在查询分析器中运行脚本导入数据库，再为要查询的表创建全文索引，索引字段设置为要查询的字段。

l 调用示例：execute proc_Common_SuperLike'id','t_test','content','20','|','[i]|o|c'

[sql] view plaincopyprint?
GO  
CREATE function Get_StrArrayLength  
(  
 @str varchar(1024),  --要分割的字符串  
 @split varchar(10)  --分隔符号  
)  
returns int  
as  
 begin  
  declare @location int  
  declare @start int  
  declare @length int  
  set @str=ltrim(rtrim(@str))  
  set @location=charindex(@split,@str)  
  set @length=1  
   while @location<>0  
     begin  
      set @start=@location+1  
      set @location=charindex(@split,@str,@start)  
      set @length=@length+1  
     end  
   return @length  
 end  
 GO  
 CREATE function Get_StrArrayStrOfIndex  
(  
 @str varchar(1024),  --要分割的字符串  
 @split varchar(10),  --分隔符号  
 @index int --取第几个元素  
)  
returns varchar(1024)  
as  
begin  
 declare @location int  
 declare @start int  
 declare @next int  
 declare @seed int  
 set @str=ltrim(rtrim(@str))  
 set @start=1  
 set @next=1  
 set @seed=len(@split)  
 set @location=charindex(@split,@str)  
 while @location<>0 and @index>@next  
   begin  
    set @start=@location+@seed  
    set @location=charindex(@split,@str,@start)  
    set @next=@next+1  
   end  
 if @location =0 select @location =len(@str)+1  
   
--这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。  
 return substring(@str,@start,@location-@start)  
end  
GO  
CREATE PROCEDURE proc_Common_SuperLike  
    --要查询的表的主键字段名称  
    @primaryKeyName varchar(999),  
    --要查询的表名  
    @talbeName varchar(999),  
    --要查询的表的字段名称，即内容所在的字段  
    @contentFieldName varchar(999),  
    --查询记录的个数(TOP *),匹配的个数越多,排名越靠前  
    @selectNumber varchar(999),  
    --匹配字符分隔标记  
    @splitString varchar(999),  
    --匹配字符组合字符串  
    @words varchar(999)  
      
AS  
    declare @sqlFirst varchar(999)  
    declare @sqlCenter varchar(999)  
    declare @sqlLast varchar(999)  
    declare @next int    
    declare @arrayLength int  
    declare @newTable varchar(999)  
BEGIN  
    set @newTable=''  
    set @sqlCenter=''  
    set @next=1  
    set @arrayLength=dbo.Get_StrArrayLength(@words,@splitString)  
  
    while @next<=@arrayLength  
    begin  
        --构造sql查询条件(中间部分)  
        --判断是否是必要词  
        if CHARINDEX('[',dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next))>0  
        begin  
            set @sqlCenter = @sqlCenter+'SELECT '+@primaryKeyName+','+CONVERT(varchar(999),@arrayLength-@next+1)+' AS wordPower FROM '+@talbeName+' WHERE CONTAINS(' + @contentFieldName + ',''"*'+REPLACE(REPLACE(dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next),'[',''),']','')+'*"'') UNION ALL '  
            --构造必要词  
            set @newTable=@newTable+'CONTAINS(' + @contentFieldName + ',''"*'+REPLACE(REPLACE(dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next),'[',''),']','')+'*"'') AND '  
        end  
        else  
        begin  
            set @sqlCenter = @sqlCenter+'SELECT '+@primaryKeyName+','+CONVERT(varchar(999),@arrayLength-@next+1)+' AS wordPower FROM '+@talbeName+' WHERE CONTAINS(' + @contentFieldName + ',''"*'+dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next)+'*"'') UNION ALL '  
        end  
          
        set @next=@next+1  
    end  
    --判断是否有必要词  
    if CHARINDEX('[',@words)>0  
    begin  
        ---处理必要词部分,去除最后无用语句  
        set @newTable=left(@newTable,(len(@newTable)-4))  
        set @newTable='AS t_Temp WHERE '+ @primaryKeyName +' IN (SELECT '+@primaryKeyName+' FROM ' + @talbeName+' WHERE ' + @newTable + ')'  
    end  
    else  
    begin  
        set @newTable='AS t_Temp'  
    end  
  
    --处理sql语句中间部分,去除最后无用语句  
    set @sqlCenter=left(@sqlCenter,(len(@sqlCenter)-10))  
    --构造sql语句开头部分  
    set @sqlFirst='SELECT TOP '+@selectNumber+' '+@primaryKeyName+',COUNT(*)+SUM(wordPower) AS finalPower FROM ('  
    --构造sql语句结尾部分  
    set @sqlLast=') ' + @newTable + ' GROUP BY '+@primaryKeyName+' ORDER BY finalPower DESC'  
    --拼接出完整sql语句,并执行  
    Execute(@sqlFirst+@sqlCenter+@sqlLast)  
END  

附-SQL数据库表全文索引创建指南：

--开启全文索引

sp_fulltext_database enable

--创建索引目录(创建出来是一个目录，用来放索引文件)

CREATE FULLTEXT CATALOG 索引目录名称 --例如myFullText

--创建全文索引

CREATE FULLTEXT INDEX ON 表名(字段名) --为哪个表的哪个字段创建全文索引，例如t_test(content)

KEY INDEX 主键索引名称 ON 索引目录名称 --注意是主键索引名称，而不是主键字段名称！例如，PK__t_test__3213E83F0EA330E9；指定全文索引目录，即放在哪个目录下，例如myFullText

注意：如果在创建数据库表全文索引之前，数据库表中已经有大量记录，那么创建全文索引是需要时间的，因此创建完全文索引后马上使用可能查不到数据。

如果遇到SQL 08 在与全文筛选器后台程序宿主(FDHost)进程通信时遇到错误

1.请开启SQL Full-text Filter服务

2.将SQL Full-text Filter服务的登录类型更改为本地账户