利用MySQL內(nèi)置函數(shù)實(shí)現(xiàn)全文搜索功能

作者：佚名 2009-02-25 13:41:49

本文主要介紹應(yīng)用MySQL中的內(nèi)置函數(shù)來實(shí)現(xiàn)全文搜索功能，希望本文可以給您帶來幫助。

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

MySQL支持全文索引和搜索功能。MySQL中的全文索引類型FULLTEXT的索引。 FULLTEXT 索引僅可用于 MyISAM 表；他們可以從CHAR、VARCHAR或TEXT列中作為CREATE TABLE語句的一部分被創(chuàng)建，或是隨后使用ALTER TABLE 或 CREATE INDEX被添加。對(duì)于較大的數(shù)據(jù)集，將你的資料輸入一個(gè)沒有FULLTEXT索引的表中，然后創(chuàng)建索引，其速度比把資料輸入現(xiàn)有FULLTEXT索引的速度更為快。

全文搜索同MATCH()函數(shù)一起執(zhí)行。

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)

MATCH()函數(shù)對(duì)于一個(gè)字符串執(zhí)行資料庫內(nèi)的自然語言搜索。一個(gè)資料庫就是1套1個(gè)或2個(gè)包含在FULLTEXT內(nèi)的列。搜索字符串作為對(duì)AGAINST()的參數(shù)而被給定。對(duì)于表中的每一行, MATCH() 返回一個(gè)相關(guān)值，即, 搜索字符串和 MATCH()表中指定列中該行文字之間的一個(gè)相似性度量。

在默認(rèn)狀態(tài)下, 搜索的執(zhí)行方式為不區(qū)分大小寫方式。然而,你可以通過對(duì)編入索引的列使用二進(jìn)制排序方式執(zhí)行區(qū)分大小寫的全文搜索。例如，可以向一個(gè)使用latin1字符集的列給定latin1_bin 的排序方式，對(duì)于全文搜索區(qū)分大小寫。

如上述所舉例子，當(dāng)MATCH()被用在一個(gè) WHERE 語句中時(shí)，相關(guān)值是非負(fù)浮點(diǎn)數(shù)。零相關(guān)的意思是沒有相似性。相關(guān)性的計(jì)算是基于該行中單詞的數(shù)目, 該行中獨(dú)特子的數(shù)目，資料庫中單詞的總數(shù)，以及包含特殊詞的文件(行)數(shù)目。

對(duì)于自然語言全文搜索，要求MATCH() 函數(shù)中命名的列和你的表中一些FULLTEXT索引中包含的列相同。對(duì)于前述問訊，注意，MATCH()函數(shù)(題目及全文)中所命名的列和文章表的FULLTEXT索引中的列相同。若要分別搜索題目和全文，應(yīng)該對(duì)每個(gè)列創(chuàng)建FULLTEXT索引。

或者也可以運(yùn)行布爾搜索或使用查詢擴(kuò)展進(jìn)行搜索。

上面的例子基本上展示了怎樣使用返回行的相關(guān)性順序漸弱的MATCH()函數(shù)。而下面的例子則展示了怎樣明確地檢索相關(guān)值。返回行的順序是不定的，原因是 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)

下面的例子則更加復(fù)雜。詢問返回相關(guān)值，同時(shí)對(duì)行按照相關(guān)性漸弱的順序進(jìn)行排序。為實(shí)現(xiàn)這個(gè)結(jié)果，你應(yīng)該兩次指定 MATCH()：一次在 SELECT 列表中而另一次在 WHERE子句中。這不會(huì)引起額外的內(nèi)務(wù)操作，原因是MySQL 優(yōu)化程序注意到兩個(gè)MATCH()調(diào)用是相同的，從而只會(huì)激活一次全文搜索代碼。

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 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

表中有2行(0.00 秒)

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

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

一些詞在全文搜索中會(huì)被忽略：

◆ 任何過于短的詞都會(huì)被忽略。全文搜索所能找到的詞的默認(rèn)最小長(zhǎng)度為 4個(gè)字符。

◆ 停止字中的詞會(huì)被忽略。禁用詞就是一個(gè)像“the” 或“some” 這樣過于平常而被認(rèn)為是不具語義的詞。存在一個(gè)內(nèi)置的停止字，但它可以通過用戶自定義列表被改寫。

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

這項(xiàng)技術(shù)最適合同大型詞庫一起使用 (事實(shí)上, 此時(shí)它經(jīng)過仔細(xì)的調(diào)整 )。對(duì)于很小的表，單詞分布并不能充分反映它們的語義價(jià)值，而這個(gè)模式有時(shí)可能會(huì)產(chǎn)生奇特的結(jié)果。例如, 雖然單詞 “MySQL” 出現(xiàn)在文章表中的每一行，但對(duì)這個(gè)詞的搜索可能得不到任何結(jié)果：

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

找不到搜索的詞(0.00 秒)

這個(gè)搜索的結(jié)果為空，原因是單詞 “MySQL” 出現(xiàn)在至少全文的50%的行中。因此, 它被列入停止字。對(duì)于大型數(shù)據(jù)集，使用這個(gè)操作最合適不過了----一個(gè)自然語言問詢不會(huì)從一個(gè)1GB 的表每隔一行返回一次。對(duì)于小型數(shù)據(jù)集，它的用處可能比較小。

一個(gè)符合表中所有行的內(nèi)容的一半的單詞查找相關(guān)文檔的可能性較小。事實(shí)上, 它更容易找到很多不相關(guān)的內(nèi)容。我們都知道，當(dāng)我們?cè)谝蛱鼐W(wǎng)上試圖使用搜索引擎尋找資料的時(shí)候，這種情況發(fā)生的頻率頗高。可以推論，包含該單詞的行因其所在特別數(shù)據(jù)集而被賦予較低的語義價(jià)值。一個(gè)給定的詞有可能在一個(gè)數(shù)據(jù)集中擁有超過其50%的域值，而在另一個(gè)數(shù)據(jù)集卻不然。

當(dāng)你***次嘗試使用全文搜索以了解其工作過程時(shí)，這個(gè)50% 的域值提供重要的蘊(yùn)涵操作：若你創(chuàng)建了一個(gè)表，并且只將文章的1、2行插入其中，而文中的每個(gè)單詞在所有行中出現(xiàn)的機(jī)率至少為 50% 。那么結(jié)果是你什么也不會(huì)搜索到。一定要插入至少3行，并且多多益善。需要繞過該50% 限制的用戶可使用布爾搜索代碼。

【編輯推薦】