[精讚] [會員登入]
1729

[Mysql/Mariadb] 全文檢索 fulltext index

關於 MySQL 的全文檢索

此文完整連結 http://n.sfs.tw/11465

複製連結 [Mysql/Mariadb] 全文檢索 fulltext index@新精讚
(文章歡迎轉載,務必尊重版權註明連結來源)
2017-07-24 19:08:33 最後編修
2017-07-18 17:36:31 By 瘦河馬
 

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

中文字怎麼辦?

fulltext 對中文字「視而不見」, 要對中文字作全檢要修改的東西不少,因為這部分後來我就改用sphinx,而不再深入研究,如果要用mysql全檢+中文只能請先參考[1]

延伸閱讀

安裝SPHINX支援中文@新精讚

PHP for sphinx 函式庫安裝@新精讚

[Centos7] 安裝sphinx+php7@新精讚

參考資料

[1] http://alvin-spaces.blogspot.tw/2013/01/mysqlfulltext-searchmatch-against.html

 

你可能感興趣的文章

[MySQL/Mariadb] 正規表達式(regular express) mysql的正規表達式很詭異,很不能接受

[mysqldump] Mysqldump時出現記憶體錯誤Out of memory 當進行 mysqldump 時出現 Out of memory (Needed xxxxx bytes) 的錯誤,該如何處理?

[Mysql/MariaDB] 查看資料庫所占空間 查看資料庫在磁碟中所占空間

[MYSQL] 把SELECT的結果UPDATE到表中 Mysql update select 出來的結果,一次完成

[Mysql/Mariadb] 忘記root密碼 常常會忘記root 密碼,簡單的幾步驟回復

[Mysql/Mariadb] 全文檢索 fulltext index 關於 MySQL 的全文檢索

[Mysql/Mariadb] 利用phpMyAdmin 建立關聯式資料表 利用phpMyAdmin 建立關聯式資料表

[MYSQL] 查看資料表狀態 要看資料表的狀態,不是結構喔,以前我都會用 procedure analyse(): SELECT `colname`

[Mysql/Mariadb] Left Join 和 Right Join 合併表單 SQL語法中的 Left Join 和 Right Join 很常用,但是我常會忘記,所以寫下來備忘。

[Mysql] 將記錄由0變1、1變0;或是判斷後再改值的寫法 MySQL 將記錄由0變1,1變0的SQL寫法 假如欄位 `active` 為0 是失效;1 為有效,他的型態是 tin

我有話要說


限制:留言最高字數1000字,超過部分會被截掉。請注意:留言不可帶有網址,會被濾掉。 限制:未登入訪客,每則留言間隔需超過10分鐘,每日最多5則留言。

訪客留言

[無留言]

隨機好文

[jQuery] textarea 的取值和給值 HTML 的 TEXTAREA 標籤若要用 jquery 取值,不能使用 .text() 或 .html() ,使用 .

[札記] 2016.7~12月札記 札記,只是札記

最值得學的電腦技能 什麼是最值得學的電腦技能?程式設計?美工繪圖?系統網路?還是…? 都不是!個人覺得一個最值得學的電腦技能就

分享還是炫耀? 是分享還是炫耀,取決於對方的的心態和你的心態...

[Mysql/MariaDB] 查看資料庫所占空間 查看資料庫在磁碟中所占空間