[Mysql/Mariadb] 全文檢索 fulltext index @新精讚

MySQL 的全文檢索一直不得其解，今日豁然開朗。

普通文字欄位的索引只能對欄位開頭的字串作搜尋(索引只存放開頭的字串)，如果文章很長，索引就沒用了，只能用SQL中的 like '%word%' 來搜尋，這很沒效率。所以我們改用全文檢索，簡稱全檢。

把欄位加入全檢

語法

ALTER TABLE 表名 ADD FULLTEXT(欄位1, 欄位2...)

ex:
ALTER TABLE `content` ADD FULLTEXT( `title`, `keyword`, `text`);

查看全檢

全檢是一個索引，名稱叫作fulltext，範例：

show index FROM `content` where index_type='fulltext'

上面的範例中我一次加入三個欄位，他的鍵名由第一個代表：'title'

使用全檢的SQL

在SELECT時用以下的SQL就可以了，match 會傳回浮點數，代表關聯程度

語法

SELECT * FROM 表名 WHERE MATCH (欄位1, 欄位2,...) AGAINST('word1 word2 ...' ) > 0.001

全檢的限制

1. 資料表中至少要有三筆以上的記錄，否則會出現非預期結果
2. 以字為單位，最少要有四個字元。若想要突破這個限制，得修改 my.ini 的屬性 ft_min_word_len
3. 不分大小寫
4. MyISAM engine才能全檢
5. MATCH 括號中的欄位一定要和建立的一樣，否則會出現#1191錯誤

#1191 - Can't find FULLTEXT index matching the column list

舉例來說，上面建立的全檢鍵值title有三個欄位`title`, `keyword`, `text`，他的語法就得這樣子寫：

SELECT * FROM content WHERE MATCH (`title`,`keyword`,`text`) AGAINST('mysql 亂碼')

不能少一個或多一個，取回來的資料會以權重由大到小排序，如果要看權重值，並確實由大到小排序，可修改為：

SELECT *, MATCH (`title`,`keyword`,`text`) AGAINST ('mysql 亂碼') score FROM content WHERE MATCH (`title`,`keyword`,`text`) AGAINST('mysql 亂碼') order by score desc

使用別名

在SQL1中如果要取回比對的相關度(match 值)又不想寫得很長，我們常會用別名，但where 是不能用別名的，所以改用HAVING代替

SELECT *, MATCH (欄位1, 欄位2...) AGAINST('word1 word2 ...' ) AS MATCHVALUE FROM 表名 HAVING MATCHVALUE > 0.001

使用 Boolean Mode 來全檢

使用boolean mode 可以讓搜尋的方式更聰明，多個字的全檢就可以使用此模式

+word 記錄中一定要有這個字，例如 AGAINST('+word1 +word2' IN BOOLEAN MODE)

-word 記錄中一定不能有這個字

?word 記錄中一定不應有這個字，但他又不是完全排除這個字，我猜可能是可有可無的意思，其字的權重較低

<降低該字權重

>word增加該字權重

word* 列出所有以word開頭的字，這個星號不能改變位置只能放最後

"word1 word2"指定比對的順序

()進行群組，例如　AGAINST('+word1 +(word2 word2)' IN BOOLEAN MODE) 找出一定有 word1及(word2 或 word3) 的記錄

ex:搜尋內容關鍵字'mysql'中一定要有'console'這個字，一定不能有'perl'這個字

SELECT *, MATCH (`title`,`keyword`,`text`) AGAINST ('mysql +console -perl' IN BOOLEAN MODE) score FROM content having score>0