MySQL 技術參考手冊: 全文搜尋函數

MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION])

MATCH()函數對一個字串進行資料庫內的自然語言搜索。一個資料集就是1個在FULLTEXT內包含有1個或2個資料列的集合。搜索字串由AGAINST()的參數指定。對於資料表中的每一行, MATCH() 回傳一個相關值，搜索字串和 MATCH()表中指定列中該行文字之間的一個相似性度量。

MySQL支援全文索引和搜索功能。MySQL中的全文索引是FULLTEXT index。 FULLTEXT 索引只能用在 MyISAM 資料表；建立資料表的時候，可以在 CREATE TABLE 命令中建立CHAR、 VARCHAR或TEXT列作為全文搜尋索引，或是隨後使用ALTER TABLE 或 CREATE INDEX加入全文搜尋索引。對於較大的數據集，將你的資料輸入一個沒有FULLTEXT索引的表中，然後建立索引，其速度比把資料輸入已經設定好FULLTEXT索引的速度更快。

全文搜索必須與MATCH()函數一起使用。

mysql> CREATE TABLE articles (
   ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
   ->   title VARCHAR(200),
   ->   body TEXT,
   ->   FULLTEXT (title,body)
   -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles (title,body) VALUES
   -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
   -> ('How To Use MySQL Well','After you went through a ...'),
   -> ('Optimizing MySQL','In this tutorial we will show ...'),
   -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
   -> ('MySQL vs. YourSQL','In the following database comparison ...'),
   -> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles
   -> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

在默認狀態下, 搜索的執行方式為不區分大小寫方式。然而,可以透過對編入索引的列使用二進制排序方式執行區分大小寫的全文搜索。例如，可以向一個使用latin1字元集的列給定latin1_bin 的排序方式，對全文搜索區分大小寫。

如上述所舉例子，當MATCH()被用在一個 WHERE 語句中時，相關值是非負浮點數。零相關的意思是沒有相似性。相關性的計算是基於該行中單詞的數目、該行中單獨字的數目，資料庫中單詞的總數，以及包含特殊詞的文件(行)數目。
要簡單的知道搜尋結果數量可以用下面的查詢：

mysql> SELECT COUNT(*) FROM articles
   -> WHERE MATCH (title,body)
   -> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

你可以將查詢語句改成下面：

mysql> SELECT
   -> COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
   -> AS count
   -> FROM articles;
+-------+
| count |
+-------+
|     2 |
+-------+
1 row in set (0.03 sec)

第一個查詢會因為集合函數會對比對結果關連性進行排序，第二個查詢則不會。但是第二個查詢會對整個資料表進行比對，第一個則不會。在資料列比較少的情況下，使用第一個查詢也許會比較快一些，但是第二個查詢在資料列比較多的狀況下會比較快，因為資料列多的時候，不管什麼樣的查詢都會讀取許多列。

對於自然語言全文搜索，要求MATCH()函數中指定的列和你的表中的FULLTEXT索引包含的列相同。對於前述的查詢，注意，MATCH()函數(標題及全文)中所命名的列和文章表的FULLTEXT索引中的列相同。若要分別搜索標題和全文，應該對每個列建立FULLTEXT索引。
或者也可以運行Boolean搜索或使用查詢擴展進行搜索。

全文搜尋時MATCH()函數使用的索引只能在同一個資料表裡，因為建立索引不能跨資料表。Boolean搜尋能用在未索引的欄位(雖然比較慢)，也就是說它能夠用在多個資料表中的欄位進行查詢。

前面的示範是一個關於MATCH()使用方式的基本描述，與資料列的關連性比較沒有關係。接下來的示範是展示如何查詢明確的關連性值。因為SELECT語句未包含WHERE和ORDER BY語句，因此回傳未排序的資料列。

mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial')
   -> FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
|  1 |                        0.65545833110809 |
|  2 |                                       0 |
|  3 |                        0.66266459226608 |
|  4 |                                       0 |
|  5 |                                       0 |
|  6 |                                       0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)

下面的範例更複雜，這個查詢回傳關連性值並且使用關連性值遞減排序資料列。為了這個結果，必須使用兩次MACTH()：一次是在SELECT列出關連性，第二次是在WHERE做為條件。這樣不會造成重覆運算，MySQL最佳化時已經考慮到兩個相同參數的MATCH()同時呼叫的情況，只會進行一次檢索。

mysql> SELECT id, body, MATCH (title,body) AGAINST
   -> ('Security implications of running MySQL as root') AS score
   -> FROM articles WHERE MATCH (title,body) AGAINST
   -> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+

MySQL FULLTEXT 執行將任何單字字元原形 (字母、數字和下劃線部分)的序列視為一個單詞。這個序列或許也包含單引號 ('),但在一行中不會超過一個。這意味著 aaa'bbb 會被視為一個單詞，而 aaa''bbb則被視為2個單詞。位於單詞之前或其後的單引號會被FULLTEXT分析程序去掉； 'aaa'bbb' 會變成 aaa'bbb。

FULLTEXT分析程序會通過尋找某些分隔符來確定單詞的起始位置和結束位置，例如' ' (間隔符號)、 , (逗號)以及 . (句號 )。假如單詞沒有被分隔符分開，(例如在中文裡 ), 則 FULLTEXT 分析程序不能確定一個詞的起始位置和結束位置。為了能夠在這樣的語言中向FULLTEXT 索引添加單詞或其它編入索引的術語，你必須對它們進行預處理，使其被一些諸如"之類的任意分隔符分隔開。
一些詞在全文搜索中會被忽略：

任何過於短的詞都會被忽略。全文搜索所能找到的詞的默認最小長度為 4個字元。
停止字中的詞會被忽略。禁用詞就是一個像「the」或「some」這樣過於平常而被認為是不具語義的詞。存在一個內置的停止字，但它可以通過用戶自定義列表被改寫。

詞庫和詢問中每一個正確的單詞根據其在詞庫和詢問中的重要性而被衡量。通過這種方式，一個出現在許多文件中的單詞具有較低的重要性(而且甚至很多單詞的重要性為零),原因是在這個特別詞庫中其語義價值較低。反之，假如這個單詞比較少見，那麼它會得到一個較高的重要性。然後單詞的重要性被組合，從而用來計算該行的相關性。

這項技術最適合同大型詞庫一起使用 (事實上, 此時它經過仔細的調整 )。對於很小的表，單詞分佈並不能充分反映它們的語義價值，而這個模式有時可能會產生奇特的結果。例如, 雖然單詞「MySQL」出現在文章表中的每一行，但對這個詞的搜索可能得不到任何結果：

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)

這個搜索的結果為空，原因是單詞「MySQL」出現在至少全文的50%的行中。因此, 它被列入停止字。對於大型數據集，使用這個操作最合適不過了----一個自然語言問詢不會從一個1GB 的表每隔一行回傳一次。對於小型數據集，它的用處可能比較小。

一個符合表中所有行的內容的一半的單詞查找相關文檔的可能性較小。事實上, 它更容易找到很多不相關的內容。我們都知道，當我們在因特網上試圖使用搜索引擎尋找資料的時候，這種情況發生的頻率頗高。可以推論，包含該單詞的行因其所在特別數據集而被賦予較低的語義價值。一個給定的詞有可能在一個數據集中擁有超過其50%的域值，而在另一個數據集卻不然。

全文搜尋函數

沒有留言: