【轉貼】MySQL 的全文檢索

轉貼 - 原文 - MySQL 的全文檢索




MySQL 的全文檢索

MySQL 的全文檢索一直不得其解，今日豁然開朗。
普通文字欄位的索引只能對欄位開頭的字串作搜尋(索引只存放開頭的字串)，如果文章很長，索引就沒用了，只能用SQL中的 like '%word%' 來搜尋，這很沒效率。所以我們改用全文檢索，簡稱全檢。
他的語法是
ALTER TABLE 表名 ADD FULLTEXT(欄位1, 欄位2...)
如此在SELECT時用以下的SQL就可以了，match 會傳回浮點數，代表關聯程度
SQL1. SELECT * FROM 表名 WHERE MATCH (欄位1, 欄位2...) AGAINST('word1 word2 ...' ) > 0.001

全檢的限制

1. 至少要有三筆以上的記錄，否則會出現非預期結果
2. 以字為單位，最少要有四個字元。若想要突破這個限制，得修改 my.ini 的屬性 ft_min_word_len
3. 不分大小寫
4. MyISAM 的type 才能全檢
使用別名

在SQL1中如果要取回比對的相關度(match 值)又不想寫得很長，我們常會用別名，但where 是不能用別名的，所以改用HAVING代替
SQL2. SELECT *,  MATCH (欄位1, 欄位2...) AGAINST('word1 word2 ...' ) AS MATCHVALUE FROM 表名 HAVING MATCHVALUE > 0.001
使用 Boolean Mode 來全檢

使用boolean mode 可以讓搜尋的方式更聰明，例如 SQL1 中只會列出結果有 word1 word2 的結果，並不會列出 word10 word20 的結果，所以多個字的全檢就可以使用此模式。

+word	記錄中一定要有這個字，例如 AGAINST('+word1 +word2' IN BOOLEAN MODE)
-word	記錄中一定不能有這個字
?word	記錄中一定不應有這個字，但他又不是完全排除這個字，我猜可能是可有可無的意思，其字的權重較低
	降低該字權重
>word	增加該字權重
word*	列出所有以word開頭的字，這個星號不能改變位置只能放最後
"word1 word2"	指定比對的順序
()	進行群組，例如　AGAINST('+word1 +(word2 word2)' IN BOOLEAN MODE) 找出一定有 word1及(word2 或 word3) 的記錄