【轉載】MySQL全文檢索索引筆記MySQL全文檢索索引筆記

MySQL全文檢索索引筆記

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即可。

   所以上面不能返回結果。但是我用上面的方法改設定檔並重啟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' IN BOOLEAN MODE );

 

 

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索引列按照一定的規則轉化成英文索引表中的每一條記錄，比如全部進行base64編碼，內容表和英文索引表的id相同），檢索時先將檢索詞也用相同規則轉換成英文，然後再使用。如果還要支援按拼音全文檢索索引，那麼還需要在索引表中增加對應的拼音內容（就需要中文轉拼音演算法了）。當然如果還需要支援中英文互動搜尋，比如搜尋William時也需要返回威廉，反之亦然，那麼還需要將威廉對應的英文翻譯也存到索引表中去。

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

 

7. 核對條目

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

   B. MySQL全文檢索索引預設不支援中文，且對英文檢索時忽略大小寫

   C. MySQL全文檢索索引時，預設檢索長度為4，即關鍵詞的長度必須大於5才能被捕獲

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

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

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

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

 

參考連結：

   http://viralpatel.net/blogs/2009/04/full-text-search-using-mysql-full-text-search-capabilities.html

   http://hi.baidu.com/gogogo/blog/item/28b16c81b3bc87d6bc3e1eb7.html

   http://dev.mysql.com/doc/refman/5.1/zh/functions.html#fulltext-query-expansion

   http://www.chinahtml.com/0702/mysql-117187149111362.html

   http://www.phpx.com/happy/viewthread.php?tid=124691

 

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即可。

   所以上面不能返回結果。但是我用上面的方法改設定檔並重啟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' IN BOOLEAN MODE );

 

 

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索引列按照一定的規則轉化成英文索引表中的每一條記錄，比如全部進行base64編碼，內容表和英文索引表的id相同），檢索時先將檢索詞也用相同規則轉換成英文，然後再使用。如果還要支援按拼音全文檢索索引，那麼還需要在索引表中增加對應的拼音內容（就需要中文轉拼音演算法了）。當然如果還需要支援中英文互動搜尋，比如搜尋William時也需要返回威廉，反之亦然，那麼還需要將威廉對應的英文翻譯也存到索引表中去。

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

 

7. 核對條目

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

   B. MySQL全文檢索索引預設不支援中文，且對英文檢索時忽略大小寫

   C. MySQL全文檢索索引時，預設檢索長度為4，即關鍵詞的長度必須大於5才能被捕獲

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

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

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

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

 

參考連結：

   http://viralpatel.net/blogs/2009/04/full-text-search-using-mysql-full-text-search-capabilities.html

   http://hi.baidu.com/gogogo/blog/item/28b16c81b3bc87d6bc3e1eb7.html

   http://dev.mysql.com/doc/refman/5.1/zh/functions.html#fulltext-query-expansion

   http://www.chinahtml.com/0702/mysql-117187149111362.html

   http://www.phpx.com/happy/viewthread.php?tid=124691