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

时间：2012-10-18 16:22:32 点击：377

　　核心提示：前言： 算法的基本特性在前几篇博客中已经做了详细的说明，经过不断的改进优化，到归仓的时候了，也就是说，该算法告一段落，不再更新。 作为最终的解决方案，简要的总结一下算法特性，以方便读者参阅。l 目的：...

前言：

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

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

        

         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?

1. GO  
2. CREATE function Get_StrArrayLength  
3. (  
4.  @str varchar(1024),  --要分割的字符串   
5.  @split varchar(10)  --分隔符号   
6. )  
7. returns int  
8. as  
9.  begin  
10.   declare @location int  
11.   declare @start int  
12.   declare @length int  
13.   set @str=ltrim(rtrim(@str))  
14.   set @location=charindex(@split,@str)  
15.   set @length=1  
16.    while @location<>0  
17.      begin  
18.       set @start=@location 1  
19.       set @location=charindex(@split,@str,@start)  
20.       set @length=@length 1  
21.      end  
22.    return @length  
23.  end  
24.  GO  
25.  CREATE function Get_StrArrayStrOfIndex  
26. (  
27.  @str varchar(1024),  --要分割的字符串   
28.  @split varchar(10),  --分隔符号   
29.  @index int --取第几个元素   
30. )  
31. returns varchar(1024)  
32. as  
33. begin  
34.  declare @location int  
35.  declare @start int  
36.  declare @next int  
37.  declare @seed int  
38.  set @str=ltrim(rtrim(@str))  
39.  set @start=1  
40.  set @next=1  
41.  set @seed=len(@split)  
42.  set @location=charindex(@split,@str)  
43.  while @location<>0 and @index>@next  
44.    begin  
45.     set @start=@location @seed  
46.     set @location=charindex(@split,@str,@start)  
47.     set @next=@next 1  
48.    end  
49.  if @location =0 select @location =len(@str) 1  
50.    
51. --这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。   
52.  return substring(@str,@start,@location-@start)  
53. end  
54. GO  
55. CREATE PROCEDURE proc_Common_SuperLike  
56.     --要查询的表的主键字段名称   
57.     @primaryKeyName varchar(999),  
58.     --要查询的表名   
59.     @talbeName varchar(999),  
60.     --要查询的表的字段名称，即内容所在的字段   
61.     @contentFieldName varchar(999),  
62.     --查询记录的个数(TOP *),匹配的个数越多,排名越靠前   
63.     @selectNumber varchar(999),  
64.     --匹配字符分隔标记   
65.     @splitString varchar(999),  
66.     --匹配字符组合字符串   
67.     @words varchar(999)  
68.       
69. AS  
70.     declare @sqlFirst varchar(999)  
71.     declare @sqlCenter varchar(999)  
72.     declare @sqlLast varchar(999)  
73.     declare @next int    
74.     declare @arrayLength int  
75.     declare @newWords varchar(999)  
76.     declare @newTable varchar(999)  
77. BEGIN  
78.     set @newTable=@talbeName  
79.     set @newWords=@words  
80.     set @next=dbo.Get_StrArrayLength(@words,'[')  
81.     --判断是否有必要词   
82.     if @next>1  
83.     begin  
84.         set @newTable=''  
85.         --构造必要表sql语句   
86.         while @next>1  
87.         begin  
88.             set @newTable=@newTable @contentFieldName ' like ''%' dbo.Get_StrArrayStrOfIndex(dbo.Get_StrArrayStrOfIndex(@words,'[',@next),']',1) '%'' AND '  
89.             set @next=@next-1  
90.         end  
91.         set @newTable=left(@newTable,(len(@newTable)-4))  
92.         --构造临时表   
93.         set @newTable='SELECT * into ##tempTable FROM '  @talbeName   ' WHERE '   @newTable  
94.         execute(@newTable)  
95.         --指定临时表   
96.         set @newTable='##tempTable'  
97.         --去掉关键词组中的必要词标记   
98.         set @newWords=REPLACE(REPLACE(@words,'[',''),']','')  
99.     end  
100.     set @sqlCenter=''  
101.     set @next=1  
102.     set @arrayLength=dbo.Get_StrArrayLength(@newWords,@splitString)  
103.   
104.     while @next<=@arrayLength  
105.     begin  
106.         --构造sql查询条件(中间部分)   
107.         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 '  
108.         set @next=@next 1  
109.     end  
110.     --处理sql语句中间部分,去除最后无用语句   
111.     set @sqlCenter=left(@sqlCenter,(len(@sqlCenter)-10))  
112.     --构造sql语句开头部分   
113.     set @sqlFirst='SELECT TOP ' @selectNumber ' ' @primaryKeyName ',COUNT(*) SUM(wordPower) AS finalPower FROM ('  
114.     --构造sql语句结尾部分   
115.     set @sqlLast=') AS t_Temp GROUP BY ' @primaryKeyName ' ORDER BY finalPower DESC'  
116.     --拼接出完整sql语句,并执行   
117.     Execute(@sqlFirst @sqlCenter @sqlLast)  
118.     --判断临时表是否存在，存在则删除，一定要删除！   
119.     if OBJECT_ID('tempDb..##tempTable') is not null  
120.     begin  
121.         drop table ##tempTable  
122.     end  
123. END  

大数据量版本：

 

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

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

         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?

1. GO  
2. CREATE function Get_StrArrayLength  
3. (  
4.  @str varchar(1024),  --要分割的字符串   
5.  @split varchar(10)  --分隔符号   
6. )  
7. returns int  
8. as  
9.  begin  
10.   declare @location int  
11.   declare @start int  
12.   declare @length int  
13.   set @str=ltrim(rtrim(@str))  
14.   set @location=charindex(@split,@str)  
15.   set @length=1  
16.    while @location<>0  
17.      begin  
18.       set @start=@location 1  
19.       set @location=charindex(@split,@str,@start)  
20.       set @length=@length 1  
21.      end  
22.    return @length  
23.  end  
24.  GO  
25.  CREATE function Get_StrArrayStrOfIndex  
26. (  
27.  @str varchar(1024),  --要分割的字符串   
28.  @split varchar(10),  --分隔符号   
29.  @index int --取第几个元素   
30. )  
31. returns varchar(1024)  
32. as  
33. begin  
34.  declare @location int  
35.  declare @start int  
36.  declare @next int  
37.  declare @seed int  
38.  set @str=ltrim(rtrim(@str))  
39.  set @start=1  
40.  set @next=1  
41.  set @seed=len(@split)  
42.  set @location=charindex(@split,@str)  
43.  while @location<>0 and @index>@next  
44.    begin  
45.     set @start=@location @seed  
46.     set @location=charindex(@split,@str,@start)  
47.     set @next=@next 1  
48.    end  
49.  if @location =0 select @location =len(@str) 1  
50.    
51. --这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。   
52.  return substring(@str,@start,@location-@start)  
53. end  
54. GO  
55. CREATE PROCEDURE proc_Common_SuperLike  
56.     --要查询的表的主键字段名称   
57.     @primaryKeyName varchar(999),  
58.     --要查询的表名   
59.     @talbeName varchar(999),  
60.     --要查询的表的字段名称，即内容所在的字段   
61.     @contentFieldName varchar(999),  
62.     --查询记录的个数(TOP *),匹配的个数越多,排名越靠前   
63.     @selectNumber varchar(999),  
64.     --匹配字符分隔标记   
65.     @splitString varchar(999),  
66.     --匹配字符组合字符串   
67.     @words varchar(999)  
68.       
69. AS  
70.     declare @sqlFirst varchar(999)  
71.     declare @sqlCenter varchar(999)  
72.     declare @sqlLast varchar(999)  
73.     declare @next int    
74.     declare @arrayLength int  
75.     declare @newTable varchar(999)  
76. BEGIN  
77.     set @newTable=''  
78.     set @sqlCenter=''  
79.     set @next=1  
80.     set @arrayLength=dbo.Get_StrArrayLength(@words,@splitString)  
81.   
82.     while @next<=@arrayLength  
83.     begin  
84.         --构造sql查询条件(中间部分)   
85.         --判断是否是必要词   
86.         if CHARINDEX('[',dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next))>0  
87.         begin  
88.             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 '  
89.             --构造必要词   
90.             set @newTable=@newTable 'CONTAINS('   @contentFieldName   ',''"*' REPLACE(REPLACE(dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next),'[',''),']','') '*"'') AND '  
91.         end  
92.         else  
93.         begin  
94.             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 '  
95.         end  
96.           
97.         set @next=@next 1  
98.     end  
99.     --判断是否有必要词   
100.     if CHARINDEX('[',@words)>0  
101.     begin  
102.         ---处理必要词部分,去除最后无用语句   
103.         set @newTable=left(@newTable,(len(@newTable)-4))  
104.         set @newTable='AS t_Temp WHERE '  @primaryKeyName  ' IN (SELECT ' @primaryKeyName ' FROM '   @talbeName ' WHERE '   @newTable   ')'  
105.     end  
106.     else  
107.     begin  
108.         set @newTable='AS t_Temp'  
109.     end  
110.   
111.     --处理sql语句中间部分,去除最后无用语句   
112.     set @sqlCenter=left(@sqlCenter,(len(@sqlCenter)-10))  
113.     --构造sql语句开头部分   
114.     set @sqlFirst='SELECT TOP ' @selectNumber ' ' @primaryKeyName ',COUNT(*) SUM(wordPower) AS finalPower FROM ('  
115.     --构造sql语句结尾部分   
116.     set @sqlLast=') '   @newTable   ' GROUP BY ' @primaryKeyName ' ORDER BY finalPower DESC'  
117.     --拼接出完整sql语句,并执行   
118.     Execute(@sqlFirst @sqlCenter @sqlLast)  
119. 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

 

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

作者：蜀都互联　来源：www.zcool8.com