[精讚] [會員登入]
9363

[Mysql/Mariadb] 全文檢索 fulltext index

關於 MySQL 的全文檢索

分享此文連結 //n.sfs.tw/11465

分享連結 [Mysql/Mariadb] 全文檢索 fulltext index@新精讚
(文章歡迎轉載,務必尊重版權註明連結來源)
2019-10-25 08:57:28 最後編修
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

 

END

你可能感興趣的文章

[Mysql] 建立叢集式資料庫2/4 -- DB2、DB3設定 使用galera建立Mysql的叢集式資料庫 ,繼續建立第2,第3台DBs

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

[Mysql/MariaDB] 使用 LOCATE, POSITION, INSTR來取代 like [Mysql/MariaDB中使用LOCATE, POSITION, INSTR來取代 like的方法

[Mysql/Mariadb] 密碼設定強度修改 新的mysql對於密碼預設也把原來的規則改得更嚴格,此篇教你修改密碼的設定政策

Mysql 安裝完畢要做的事 Mysql 安裝完畢後,該處理的流程

[Mysql/MariaDB] 清除及設定 MySQL 的死連接 mysql 伺服器若有太多的連接,無疑的會耗用非常多的資源,若資源耗盡會影響新的連線,勢必要做一些調整

我有話要說

>>

限制:留言最高字數1000字。 限制:未登入訪客,每則留言間隔需超過10分鐘,每日最多5則留言。

訪客留言

[無留言]

隨機好文

使用Google尋找你的手機 這近發現google竟然可以用來找android的手機,而且不需要經過什麼設定或安裝軟體。

[Win7] 燒錄 iso 檔 在Windows7 中內建燒錄程式,可以直接把檔案拉到光碟機裡,再執行燒錄。

[CodeIgniter3] 解決無法上傳特定檔案(.sb2)的問題 上傳時出現The filetype you are attempting to upload is not allowed,要怎麼解決?

外匯課程經驗 有天遇到某個朋友的正妹朋友,說她有個很棒的外匯投資課程,要我們去聽,只收場地費200大洋。她又說續效最差一個月也有1%,誰還在苦哈哈上班?

正則表達式:Email名稱防止連續輸入點(.)及下底線(_) Email的名稱中要允許特殊符號,但又不允許連續出現的正則表達式