SQL 全文檢索索引

SQL Server 全文索引查詢T-SQL學習筆記之二(Full-text index)

　　在學習筆記一裡已經掌握了基本的contains文法和freetext文法的用法，但是面對一些複雜的操作，基本的包含文法是不夠用的，如果我們想要查詢含有“世界”或“末日”的所有字串集合，那麼無論是contains([column],'世界末日')或者freetext([column],'世界末日')都不能很好的工作，當然contains本身是可以含有條件的，因此有兩個解決方案。

多條件查詢

　　第一個就是傳統的where多條件查詢，加上兩個contains語句，然後用or串連

select [Column] as [result] from Sample where contains([Column],'世界') or contains([Column],'末日')

　　執行結果如下：

　　現在，介紹如何直接使用一個contains語句實現多條件查詢，文法的結構如下

contains([Column],'"keyword1" and "keyword2" and ......')contains([Column],'"keyword1" or "keyword2" or ......')contains([Column],'("keyword1" or "keyword2") and ......')

　　其實也就是在兩個單引號內實現多條件，and表示交集，or表示並集，我們運行如下T-SQL

select [Column] as [result] from Sample where contains([Column],'"世界" or "末日"')

　　執行結果如下：

　　兩次查詢均返回了1820條記錄，可以看到，排在前面的記錄兩者是不一樣的，因此這兩種方式的查詢在底層的執行方式是不一樣的，往往把條件都放在一個contains裡會有更高的效率。

　　要注意的是，freetext本身就是模糊查詢了，它不能再帶有條件，如果想嘗試在freetext裡加入條件陳述式是沒有意義的，不會返回任何結果。

　　現在總結一下contains和freetext，可以看到，使用這兩個查詢方法很簡單，效率較高，但是它們一個很大的不足：

contains和freetext不會限定返回結果的數量，而是將滿足條件的全部返回

　　這會帶來兩個比較重要的問題，

１、我們會得到很多無用的結果，同時如果返回結果過多，也會極大影響查詢效率

２、返回的結果是無序的，並沒有按照預想的如“相似程度”進行排序，導致最好的查詢結果往往不再最前面

　　為了克服這樣的問題，就可以使用containstable和freetexttable文法，這兩個查詢方法可以限定返回結果的數量，同時能賦予一個rank函數（相似性函數）返回rank最大的n個結果，這就是著名的top_n_by_rank argument

containstable、freetexttable

　　使用containstable和top_n_by_rank需要使用表的內串連操作，內串連也稱為等同串連，返回的結果集是兩個表中所有相匹配的資料，用on進行串連。我們讓containstable返回的結果集作為一個表k，該表擁有兩個欄位，一個是key欄位，一個是rank欄位，在查詢時需要將key欄位與查詢欄位中的一個主鍵（唯一欄位）相進行串連，如下

SELECT [Column] as [result] From wiki --查詢Column欄位的記錄，該欄位是唯一欄位，並且已建立全文索引inner join --內串連containstable(Sample,[Column],'"世界" or "末日"',500) as k --含有“世界”和“末日”的前500條記錄作為表kon wiki.fs_wiki_title = k.[key] --串連條件ORDER BY k.RANK DESC --按照k的rank降序排列，即相似性越高的越靠前

　　執行結果如下：

 

　　可以看到，現在的查詢時間已經大大減少，因為我們只返回500條記錄，並且這些記錄都是與給定的“世界”或“末日”非常接近的。

　　下面，我們可以進行一些更加進階的查詢，比如在containstable文法裡限定條件，這個時候我們要在庫裡查含有“世界”並且含有“末日”的所有字串，執行如下T-SQL語句

SELECT [Column] as [result] From Sampleinner join containstable(Sample,[Column],'"世界" and "末日"',500) as k on Sample.Column = k.[key]ORDER BY k.RANK DESC

　　執行結果如下：

繼續，我們再限定尋找字串長度小於等於6，執行如下T-SQL語句

SELECT [Column] as [result] From Sampleinner join containstable(Sample,[Column],'"世界" and "末日"',500) as k on Sample.Column = k.[key]where len([Column])<=6ORDER BY k.RANK DESC

　　執行結果如下：

　　可以看到，這個時候返回結果就很精確了，因此我們通過containstable能夠非常靈活的進行查詢設計並且對返回結果按相似性排序。freetexttable的用法和containstable用法類似，因此這裡不再講解。