解讀MySQL全文檢索

2014年辛星mysql教程秋季版之夯實基礎

• 類型：電子教程大�。�8.5M語言：中文 評分：8.3
• 標簽：

立即下載

1. MySQL 4.x版本及以上版本提供了全文檢索支持，但是表的存儲引擎類型必須為MyISAM，以下是建表SQL，注意其中顯式設置了存儲引擎類型

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 其中FULLTEXT(title, body) 給title和body這兩列建立全文索引，之后檢索的時候注意必須同時指定這兩列。


2. 插入測試數據

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 ...');

3. 全文檢索測試

SELECT * FROM articles
WHERE MATCH (title,body) AGAINST ('database'); 檢索結果如下：

5 MySQL vs. YourSQL In the following database comparison ...1 MySQL Tutorial DBMS stands for DataBase ... 說明全文匹配時忽略大小寫。


4. 可能遇到的困擾

到目前為止都很順利，但是如果檢索SQL改為下面會怎樣呢？

SELECT * FROM articles
WHERE MATCH (title,body) AGAINST ('well'); 結果讓人大跌眼鏡，開始我也困惑了許久，后來去網上查了下才知道原來是這么回事：

mysql指定了最小字符長度，默認是4，必須要匹配大于4的才會有返回結果，可以用SHOW VARIABLES LIKE 'ft_min_word_len' 來查看指定的字符長度，也可以在mysql配置文件my.ini 更改最小字符長度，方法是在my.ini 增加一行 比如：ft_min_word_len = 2，改完后重啟mysql即可。 所以上面不能返回結果。但是我用上面的方法改配置文件后，再用show命令查看，并沒有改變。

另外，MySQL還會計算一個詞的權值，以決定是否出現在結果集中，具體如下：

mysql在集和查詢中的對每個合適的詞都會先計算它們的權重，一個出現在多個文檔中的詞將有較低的權重(可能甚至有一個零權重)，因為在這個特定的集中，它有較低的語義值。否則，如果詞是較少的，它將得到一個較高的權重，mysql默認的閥值是50%，上面‘you’在每個文檔都出現，因此是100%，只有低于50%的才會出現在結果集中。 但是如果不考慮權重，那么該怎么辦呢？MySQL提供了布爾全文檢索（BOOLEAN FULLTEXT SEARCH）

假設well在所有記錄中都出現，并且ft_min_word_len已經改為2，那么下面的SQL檢索語句得到的結果集將包含所有記錄：

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

5. 布爾全文檢索語法

上面通過IN BOOLEAN MODE指定全文檢索模式為布爾全文檢索。MySQL還提供了一些類似我們平時使用搜索引擎時用到的的語法：邏輯與、邏輯或、邏輯非等。具體通過幾個SQL語句例子來說明

SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('+apple -banana' IN BOOLEAN MODE); + 表示AND，即必須包含。- 表示NOT，即不包含。

SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('apple banana' IN BOOLEAN MODE); apple和banana之間是空格，空格表示OR，即至少包含apple、banana中的一個。

SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('+apple banana' IN BOOLEAN MODE); 必須包含apple，但是如果同時也包含banana則會獲得更高的權重。

SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('+apple ~banana' IN BOOLEAN MODE); ~ 是我們熟悉的異或運算符。返回的記錄必須包含apple，但是如果同時也包含banana會降低權重。但是它沒有 +apple -banana 嚴格，因為后者如果包含banana壓根就不返回。

SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('+apple +(>banana <orange)' IN BOOLEAN MODE); 返回同時包含apple和banana或者同時包含apple和orange的記錄。但是同時包含apple和banana的記錄的權重高于同時包含apple和orange的記錄。

6. MySQL不支持中文的全文檢索

默認MySQL不支持中文全文檢索，怎么辦？大致方法有下面幾個：

A. 擴展MySQL，添加中文全文檢索支持，難度較大

B. 為中文內容表提供一個對應的英文索引表（即將FULLTEXT索引列按照一定的規(guī)則轉化成英文索引表中的每一條記錄，比如全部進行base64編碼，內容表和英文索引表的id相同），檢索時先將檢索詞也用相同規(guī)則轉換成英文，然后再使用。如果還要支持按拼音全文檢索，那么還需要在索引表中增加對應的拼音內容（就需要中文轉拼音算法了）。當然如果還需要支持中英文交互搜索，比如搜索William時也需要返回威廉，反之亦然，那么還需要將威廉對應的英文翻譯也存到索引表中去。

參考網上的鏈接，具體做法包括先對中文內容進行分詞，然后中文轉換為四位區(qū)位碼存到索引表中。檢索時，包含中文的檢索詞也要先分詞，再轉換為四位區(qū)位碼，然后在索引表中進行全文檢索。

7. 核對條目

A. 只有存儲引擎類型為MyISAM類型的表，并且MySQL版本為4.X或者以上才能使用MySQL內置的全文檢索支持

B. MySQL全文檢索默認不支持中文，且對英文檢索時忽略大小寫

C. MySQL全文檢索時，默認檢索長度為4，即關鍵詞的長度必須大于5才能被捕獲

D. MySQL全文檢索時，所有FULLTEXT索引列必須使用相同的字符集

E. MySQL全文檢索返回結果集時還會考慮權重

F. MySQL全文檢索還支持靈活的布爾全文檢索模式

G. 更多內容參考MySQL5官方手冊