SQL 全文檢索簡介

 工作上用了全文檢索之後慢慢的讀了一些文章

這邊做個簡單的整理

SQL的全文檢索是基於分詞的文本檢索功能，不同於傳統的平衡樹，採用倒排索引（英語：Inverted index）至於是哪一種到排索引可能就要再搜尋一下了。


倒排索引

由上圖所知 倒排索引就是把字詞單獨寫出後 給予陣列回記哪個字詞出現在哪段句子中。
而第二種完全倒排就只是把位置記錄得更詳細 於哪一個句子的哪一個位置。

生成全文索引是把用户表中的文本数据进行分词（Word breaker）和提取词干（Stemmer），并转换同义词（Thesaurus），过滤掉分词中的停用词（Stopword），最后把处理之后的数据存储到全文索引中

關於停用詞的部分，比如說第一個的"個"就是所謂的停用詞，用

SELECT *

FROM sys.dm_fts_index_keywords

(DB_ID('DB_NAME'),OBJECT_ID('TABLE_NAME')

WHERE 找取display_trim的時候無法取得分詞"第一個"，同理Contain()的時候也會把第一個分成第一

停用詞的用途是為了減少全文檢索的size，增加效能。

接著是關於詞幹提取器跟同義詞。

詞幹提取器是用於把同源單字轉換成樹形式，能夠轉換為同跟形式的單字。

如 單字 run 等同於 ran、running、runs 諸如此類。

接著是建立全文檢索的部分

首先要先安裝全文檢索，這不是內建的(至少我用免費版要自己勾選安裝)。

安裝完之後可以對table 右鍵 => 全文檢索索引 定義全文檢索索引




這邊需要注意的是你必須要有pk你才可以建立全文檢索索引。

假如你是對於View建立的話，就要先指定UNIQUE CLUSTERED INDEX

CREATE UNIQUE CLUSTERED INDEX INDEX_NAME

ON  dbo.VIEWNAME(COL_NAME);

GO 

下一步後建立選擇要被搜尋的行。

這邊貌似會有一些限制，比如說Type不能使用Text之類的。

接著選自動之後進入到建立全文檢索目錄的地方。

這邊比較算是在分配哪一個目錄管理哪一個View or Table(?)。

目錄的命名我是依照目標View名稱，然後那個目錄只塞對應名稱的資料行。

之前我是依照使用用途塞，但是後來我是用資料表名稱去區分。

並且，每個表只能創建一個全文檢索索引，因此若你這張表有兩種不同的停用詞或者分詞表的話可能就要建立兩個View去區分了。

理論上應該沒有什麼區別，因為SELECT也不是用全文檢索目錄名稱。

但是會跟你套用的停用詞跟分詞表有關係。

接著按完成就算創立成功，就可以使用contains()了。

大致上速度的差距在這篇有講解

接著用code建立全文索引部分

create fulltext catalog catalog_name --目錄名稱
as default; --語言 預設

GO

CREATE UNIQUE CLUSTERED INDEX INDEX_NAME 

ON  dbo.VIEWNAME(COL_NAME);

GO 

GO

create fulltext index
on [dbo].[VIEWNAME]
(
[tsql] language 1033
)
key index INDEX_NAME
on (catalog_name,filegroup [primary]) --選定文件組，分詞等工具
with(change_tracking=off ,no population ,stoplist=system);

--with裡面分別是(是否自動更新全文檢索數據  , 不清楚  , 是否含有停用詞)。


接著是各種查詢方法

1. IF判斷

CONTAIN(ColName ,  ' "A" OR "B" ')

CONTAIN(ColName ,  'A OR B')

2. 前後綴查詢 類似LIKE 的 %%

CONTAIN(ColName ,  ' "*A*" ')

CONTAIN(ColName ,  ' "*A*" ' OR ' "*B*" ')

3. 同義、源詞查詢

單字型態問題 過去式之類的 如 RUN RAN RUNNING

CONTAIN(ColName ,  ' FORMSOF (INFLECTIONAL , A) ')

同義字查詢

A可能是 作家 跟 作者可能算是同義詞

CONTAIN(ColName ,  ' FORMSOF (THESAURUS, A) ')

不過以上情況是限於英文 假如說是中文可能還是要自己處理才行

4. 距離查詢
NEAR ( ( { <simple_term> | <prefix_term> } [ ,…n ] )  [, <maximum_distance> ] [, <match_order> ] ) 

不管距離與順序
CONTAINS(ColName , 'NEAR(term1,"term3 term4")')

不管順序 但是距離五以內

CONTAINS(column_name, 'NEAR((AA,BB),5)')

要求順序由 1 ~ 2 ~ 3 並且距離MAX以內
CONTAINS(column_name, 'NEAR ((Monday, Tuesday, Wednesday), MAX, TRUE)')


全文檢索用途
通常已經有LIKE可以使用了，而且LIKE的準確度比起全文檢索是更高的
至少原生的全文檢索你打一不一定會找到一相關，因為他的斷詞工具並沒有斷到一或者是把一當成停用詞，要再去人工設定

但是用還是需要用到全文檢索，尤其是在大筆資料搜尋的時候，因為兩者的搜尋結構不同

在這篇有提到關於執行成本的問題

參考:

https://www.cnblogs.com/ljhdo/p/5041605.html

https://medium.com/ricos-note/full-text-search-ae170434907b

https://www.796t.com/content/1496414407.html

https://zh.wikipedia.org/zh-tw/%E5%80%92%E6%8E%92%E7%B4%A2%E5%BC%95

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-fts-index-keywords-by-document-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16

留言

張貼留言

這個網誌中的熱門文章

無法載入檔案或組件 'System.IO.Compression' 或其相依性的其中之一。

圖片
曾經在拿取檔案的時候發生過這個問題 我不清楚究竟是版本的問題還是(應該不是版本的問題，因為我從我的夥伴手中拿它可以跑的程式，修改Config後仍然有問題) 主要的問題可能是因為package的問題，時常因為package跟參考以及nuget和WebConfig有不同而出現版本問題 遇到上述問題的話可能都要檢查以上三個檔案 建議是直接複製查詢 然後把「你認為不該存在的」參考給移除。 我自己本人是只留下參考中的檔案，是會把package和WebConfig的依賴給註解後再使用nuget重新安裝。 題外話抱怨 package.Config貌似就是參考的引入，但是不知道為啥連那邊都會跟參考互相衝突，微軟真的不打算修一下逆? 補充說明: 比如說遇到這個錯誤 上面有寫明說 Newtonsoft.Json culture = neutral publicKeyToken = 30ad4fe6b2a6aeed version = 6.0.0.0 就可以在web.config中寫這段加入  <dependentAssembly>    <assemblyIdentity name="Newtonsoft.Json" culture="neutral" publicKeyToken="30ad4fe6b2a6aeed"/>    <bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0"/>  </dependentAssembly> oldVersion - 那些版號要對應 - 可以用 - 來包含版號區間。 注意，newVersion不代表一定比oldVersion高。換句話說也有可能高版本全部要用低版本的dll（不過這種情景比較少 ，因為一般都是高版本會向下兼容） 不過這種方法不太建議，一律建議使用最上面的方法 參考 http://shaurong.blogspot.com/2020/02/systemiocompression.html
閱讀完整內容

MongoDB 入門

圖片
 MongoDB是一個NoSQL(非關聯性資料庫) 主要的儲存方式是 一個DB => 底下為一個Collection (類似Table) => 接著是文檔(docume) 存的資料型態為 BSON 是一種JSON的變體 如這樣 ID 自動生成 如SN 為唯一值 name sort之類的數值都是自己產生的 常用語法 通常都是以 db.CollectionName 開頭 find() 類似where 不輸入為全搜尋 insertOne({colname : data , colname2 :"data2"}) insert 用逗號分隔 insertMany([{colname : data} , {colname2 :"data2"}]) 如上 只是有中括號用來代表陣列 find().limit(N) 只回傳N筆 類似select() find().skip(N) 略過N筆後 類似skip() find().sort({colname : 1 , colname2 : -1}) 1是升冪 -1降冪 find()進階 => find({colname : 3} , {colname : 1 , colname2 : 0}) 這段的意思是 依照前面大括內的where 搜尋後 依照後面的select 條件選出 1是要輸出 0是不要輸出 假如只有0 則是 除了這些以外都要輸出  通常 1 跟 0 同時出現的時候是 _id這個字樣 因為這個是會自動返回的字樣 find(colname : {$gt : 3})  gt 大於 lt 小於 gte 大於等於 lte 小於等於 eq 等於 in => {$in :[ 1 , 3]} 一或三 nin => {$nin :[ 1 , 3]} 不等於1 或 3 都出 colname : { $exists : 1 } 代表這個colname存在的話則顯示 (nosql 因此型態可能不同) 0則是沒有的顯示 colname : {$regex :{/data/}} 正則表達式 找所有data  countDocuments() 總數 ex 找大於等於3小於等於5的 db.CollName.find(colname : {$gte : 3 , $let : 5}) 參考 : h...
閱讀完整內容

javascript 更改屬性及創建標籤

雖然應該是一些很基本的東西 但是我的前端其實蠻破的，所以筆記一下關於這個東西 //-----------------------------------1 如果要呈現某種屬性的話 就用 document.querySelector('id').setAttribute('readonly', true); 如果要刪除(隱藏)某種屬性的話就用 document.querySelector('id').removeAttribute('readonly'); //-----------------------------------2 $(document).ready(function () {         //取目標Tag為img者          //getElementsByTagName 不能使用forEach走訪 只好使用for走訪         var varTaglist = document.getElementsByTagName('img');         for (let i = 0; i < varTaglist.length; i++) {             //走訪建立id用於區分點選的目標             varTaglist[i].setAttribute('id', 'imgID' + i)             //走訪建立onclick屬性             varTaglist[i].setAttribute('onclick', 'imgclick(id)');         }     });     //設定屬性值 part2     var imgclick = function ...
閱讀完整內容