MySQL 全文檢索

MySQL 從 5.7.6 版開始有內建 ngram，可以支援中文檢索。

一、事前準備

1. 調整儲存引擎

要使用全文檢索的 table 必須是 InnoDB 或 MyISAM。

2. 設定分詞詞語長度

ngram 能對字串進行分詞，每個詞連續 N 個字，N 為 1~10 的數字 (預設為 2)，可以在 MySQL 設定檔中進行設定調整。 
ngram_token_size=2
ngram 範例 (全文檢索)： 
N = 1：全，文，檢，索
N = 2：全文，文檢，檢索
N = 3：全文檢，文檢索
N = 4：全文檢索

二、建立 FULLTEXT 索引

像一般加 index 的方式一樣，建立 FULLTEXT 類型的索引，要注意的是只能在 CHAR、VARCHAR 或 TEXT 的欄位上建立。 
ALTER TABLE `news` ADD FULLTEXT INDEX ft_index (`title`) WITH PARSER ngram;
WITH PARSER ngram 是重點，一定要有。

三、SQL

全文檢索有三種類型，分別是 Natural Language、Boolean、Query Expansion，MySQL 預設是 Natural Language 也就是我這次用的，以後如果有機會用到其他的再補上來。

1. Natural Language

修飾符是 IN NATURAL LANGUAGE MODE，不能使用運算符，由於他是預設類型，所以修飾符寫不寫都沒差。
SQL 在滿足以下條件時，回傳結果會自動以相關性由高至低排序
  • 沒有明確的 ORDER BY
  • 如果 join 其他 table，全文檢索必須是最左邊的 table
範例： 
SELECT *, MATCH (`title`) AGAINST ('關鍵字' IN NATURAL LANGUAGE MODE) AS score
FROM `news`
WHERE MATCH (`title`) AGAINST ('關鍵字' IN NATURAL LANGUAGE MODE);
範例中，score 是相關性的評分，搜尋結果就是依據這個評分做排序。

2. Boolean

修飾符是 IN BOOLEAN MODE，可以使用運算符，例如指定關鍵字必須存在或不存在，或它的權重應該比平常高或低。搜尋結果不會按照相關性遞減的順序自動對行進行排序。
以下列出比較有機會用到的運算符：
  • (無運算符)：預設，該關鍵字是可選的，但包含它的結果權重更高。
  • +：搜尋結果必須包含有關鍵字。
  • -：搜尋結果不包含關鍵字。
  • ><：用於更改關鍵字對搜尋結果的權重。>增加權重，<減少權重。
  • ()：將關鍵字分組為子表達式。
  • ~：否定運算符，導致關鍵字對搜尋結果的權重為負。
  • *：萬用字元。
範例： 
SELECT * FROM `news` WHERE MATCH (`title`) AGAINST ('+關鍵字*' IN BOOLEAN MODE);

四、Stopwords

最後還有一個要注意的地方是 stopwords，MySQL 一般在 stopwords 的處理是排除一模一樣的字詞，在 ngram 則是排除包含的字詞。
例如：a 是 stopword
我們要查 'a,b'，他會被解析為 'a,'、',b'，'a,' 會從索引中排除。
又如果我們查 'ai'，則會查無結果。
參考文件

留言

張貼留言