MySQL全文檢索搜尋

全文索引在 MySQL 中是一個FULLTEXT類型索引。FULLTEXT索引用於MyISAM表，可以在CREATE
TABLE時或之後使用ALTER TABLE或CREATE
INDEX在CHAR、VARCHAR或TEXT列上建立。對於大的資料庫，將資料裝載到一個沒有FULLTEXT索引的表中，然後再使用ALTER
TABLE(或CREATE INDEX) 建立索引，這將是非常快的。將資料裝載到一個已經有FULLTEXT索引的表中，將是非常慢的。
全文檢索搜尋通過MATCH()函數完成。

?View Code SQL

1 2 3 4 5 6

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)

?View Code SQL

1 2 3 4 5 6 7

INSERT INTO articles VALUES (NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'), (NULL,'How To Use MySQL Efficiently', 'After you went through a ...'), (NULL,'Optimising MySQL','In this tutorial we will show ...'), (NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), (NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'), (NULL,'MySQL Security', 'When configured properly, MySQL ...');

Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

?View Code SQL

1 2	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)
函數MATCH()對照一個文本集(包含在一個FULLTEXT索引中的一個或多個列的列集)執行一個自然語言搜尋一個字串。搜尋字串做為
AGAINST()的參數被給定。搜尋以忽略字母大小寫方式執行。對於表中的每個記錄行，MATCH()返回一個相關性值。即，在搜尋字串與記錄行在
MATCH()列表中指定的列的文本之間的相似性尺度。
當MATCH()被使用在一個WHERE子句中時
(參看上面的例子)，返回的記錄行被自動地以相關性從高到底的次序排序。相關性值是非負的浮點數字。零相關性意味著不相似。相關性的計算是基於：詞在記錄
行中的數目、在行中唯一詞的數目、在集中詞的全部數目和包含一個特殊詞的文檔(記錄行)的數目。
它也可以執行一個邏輯模式的搜尋。這在下面的章節中被描述。
前面的例子是函數MATCH()使用上的一些基本說明。記錄行以相似性遞減的順序返回。
下一個樣本顯示如何檢索一個明確的相似性值。如果即沒有WHERE也沒有ORDER BY子句，返回行是不排序的。

?View Code SQL

1	SELECT id,MATCH (title,body) AGAINST ('Tutorial') FROM articles;

+—-+—————————————–+
| id | MATCH (title,body) AGAINST (’Tutorial’) |
+—-+—————————————–+
| 1 | 0.64840710366884 |
| 2 | 0 |
| 3 | 0.66266459031789 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
+—-+—————————————–+
6 rows in set (0.00 sec)
下面的樣本更複雜一點。查詢返回相似性並依然以相似性遞減的次序返回記錄行。為了完成這個結果，你應該指定MATCH()兩次。這不會引起附加的開銷，因為 MySQL 最佳化器會注意到兩次同樣的MATCH()調用，並只調用一次全文檢索搜尋代碼。

?View Code SQL

1 2 3 4

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.5055546709332 |
| 6 | When configured properly, MySQL … | 1.31140957288 |
+—-+————————————-+—————–+
2 rows in set (0.00 sec)
MySQL 使用一個非常簡單的剖析器來將文本分隔成詞。一個“詞”是由文字、資料、“’”和“_”組成的任何字元序列。任何在 stopword 列表上出現的，或太短的(3 個字元或更少的)的 “word” 將被忽略。
在集和查詢中的每個合適的詞根據其在集與查詢中的重要性衡量。這樣，一個出現在多個文檔中的詞將有較低的權重(可能甚至有一個零權重)，因為在這個特定的
集中，它有較低的語義值。否則，如果詞是較少的，它將得到一個較高的權重。然後，詞的權重將被結合用於計算記錄行的相似性。
這樣一個技術工作可很好地工作與大的集(實際上，它會小心地與之諧調)。 對於非常小的表，詞分類不足以充份地反應它們的語義值，有時這個模式可能產生奇怪的結果。

?View Code SQL

1	SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL');

Empty set (0.00 sec)
在上面的例子中，搜尋字詞MySQL卻沒有得到任何結果，因為這個詞在超過一半的記錄行中出現。同樣的，它被有效地處理為一個 stopword
(即，一個零語義值的詞)。這是最理想的行為 — 一個自然語言的查詢不應該從一個 1GB 的表中返回每個次行(second row)。
匹配表中一半記錄行的詞很少可能找到相關文檔。實際上，它可能會發現許多不相關的文檔。我們都知道，當我們在互連網上通過搜尋引擎試圖搜尋某些東西時，這會經常發生。因為這個原因，在這個特殊的資料集中，這樣的行被設定一個低的語義值。
到 4.0.1 時，MySQL 也可以使用IN BOOLEAN MODE修飾語來執行一個邏輯全文檢索搜尋。

?View Code SQL

1 2	SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

+—-+——————————+————————————-+
| id | title | body |
+—-+——————————+————————————-+
| 1 | MySQL Tutorial | DBMS stands for DataBase … |
| 2 | How To Use MySQL Efficiently | After you went through a … |
| 3 | Optimising MySQL | In this tutorial we will show … |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. … |
| 6 | MySQL Security | When configured properly, MySQL … |
+—-+——————————+————————————-+
這個查詢返回所有包含詞MySQL的記錄行(注意： 50%
的閾值沒有使用)，但是它沒有包含詞YourSQL。注意，一個邏輯模式的搜尋不會自動地以相似值的降序排序記錄行。你可以從上面的結果出看得出來，最高
的相似值(包含MySQL兩次的那個)
最列在最後，而不是第一位。一個邏輯全文檢索搜尋即使在沒有一個FULLTEXT索引的情況下也可以工作，然而它慢些。
邏輯全文檢索搜尋支援下面的操作符：
+
一個領頭的加號表示，該詞必須出現在每個返回的記錄行中。
-
一個領頭的減號表示，該詞必須不出現在每個返回的記錄行中。
預設的 (當既沒有加號也沒有負號被指定時)詞是隨意的，但是包含它的記錄行將被排列地更高一點。這個模仿沒有IN BOOLEAN MODE修飾詞的MATCH() … AGAINST()的行為。
< >
這兩個操作符用於改變一個詞的相似性值的基值。<操作符減少基值，>操作符則增加它。參看下面的樣本。
( )
圓括弧用於對子運算式中的詞分組。
~
一個領頭的否定號的作用象一個否定操作符，引起行相似性的詞的基值為負的。它對標記一個雜訊詞很有用。一個包含這樣的詞的記錄將被排列得低一點，但是不會被完全的排除，因為這樣可以使用-操作符。
*
一個星號是截斷操作符。不想其它的操作符，它應該被追加到一個詞後，不加在前面。
”
短語，被包圍在雙引號”中，只匹配包含這個短語(字面上的，就好像被鍵入的)的記錄行。
這裡是一些樣本：
apple banana
找至少包含上面詞中的一個的記錄行
+apple +juice
… 兩個詞均在被包含
+apple macintosh
… 包含詞 “apple”，但是如果同時包含 “macintosh”，它的排列將更高一些
+apple -macintosh
… 包含 “apple” 但不包含 “macintosh”
+apple +(>pie
... 包含 “apple” 和 “pie”，或者包含的是 “apple” 和 “strudel” (以任何次序)，但是 “apple pie” 排列得比 “apple strudel” 要高一點
apple*
... 包含 “apple”，“apples”，“applesauce” 和 “applet”
"some words"
... 可以包含 “some words of wisdom”，但不是 “some noise words”
全文的限制
MATCH()函數的所有參數必須是從來自於同一張表的列，同時必須是同一個FULLTEXT索引中的一部分，除非MATCH()是IN BOOLEAN MODE的。
MATCH()列列表必須確切地匹配表的某一FULLTEXT索引中定義的列列表，除非MATCH()是IN BOOLEAN MODE的。
AGAINST()的參數必須是一個常量字串。
微調 MySQL 全文檢索搜尋
不幸地，全文檢索搜尋仍然只有很少的使用者可調參數，雖然增加一些在 TODO 上排列很高。如果你有一個 MySQL 源碼發行，你可以發揮對全文檢索搜尋的更多控制。
注意，全文檢索搜尋為最佳的搜尋效果，被仔細地調整了。修改預設值的行為，在大多數情況下，只會使搜尋結果更糟。不要修改 MySQL 的原始碼，除非你知道你在做什麼！
被索引的詞的最小長度由 MySQL 變數ft_min_word_len指定。
stopword 列表可以從ft_stopword_file變數指定的檔案中讀取。
50% 閾值選擇由所選擇的特殊的衡量模式確定。為了禁止它，修改`myisam/ftdefs.h'檔案中下面的一行：
#define GWS_IN_USE GWS_PROB
改為：
#define GWS_IN_USE GWS_FREQ
然後重新編譯 MySQL。在這種情況下，不需要重建索引。注意：使用了這個，將嚴重地減少 MySQL 為MATCH()提供足夠的相似性值的能力。如果你確實需要搜尋這樣的公用詞，最好使用IN BOOLEAN MODE的搜尋代替，它不遵守 50% 的閾值。
有時，搜尋引擎維護員希望更改使用於邏輯全文檢索搜尋的操作符。這些由變數ft_boolean_syntax定義。對於這些更改，要求你重建你的FULLTEXT索引，對於一個 MyISAM 表，最容易的重建索引檔案的方式如下面的語句：
mysql> REPAIR TABLE tbl_name QUICK;
全文檢索搜尋 TODO
使所有對FULLTEXT索引的操作更快
鄰近(Proximity)操作符
對 “always-index words” 的支援。他們可以是使用者希望視為一個詞處理的任一字元串，例如 “C++”、”AS/400″、”TCP/IP”，等等
支援在MERGE表中的全文檢索搜尋
對多位元組字元的支援
依照資料的語言建立 stopword 列表
Stemming (當然，依賴於資料的語言)
Generic user-suppliable UDF preparser.
使模式更加靈活 (通過為CREATE/ALTER TABLE中的FULLTEXT增加某些可調整參數)