[精讚] [會員登入]
956

[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] Mysqldump 的預設編碼 --default-character-set 有時Mysqldump 也得要指定編碼才不會造成匯出的亂碼

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

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

Mysql INSERT ... ON DUPLICATE KEY UPDATE 寫法 Mysql有則修改,無則新增的sql寫法

[MariaDB] Centos 7安裝mariadb + apache + php + phpmyadmin Centos 7安裝mariadb+apache+php+phpmyadmin

[MySQL] console mode 的亂碼處理 使用MySQL的console mode如果出現亂碼 在,要怎麼處理?

[MySQL/Mariadb] console 將查詢結果輸出到檔案 在mysql 的console中該如何將查詢結果輸出到檔案?

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

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

[phpmyadmin] 登錄超時 (1440 秒未操作),請重新登錄 phpmyadmin登錄超時 (1440 秒未操作),請重新登錄的問題,該如何解決?

我有話要說


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

訪客留言

[無留言]

隨機好文

談借錢 人借錢時手心向上頭向下,人還錢時手心向下頭向上

[Freebsd] 定時測試 ADSL 是否斷線並重連 中華電信 ADSL 雖有固定 ip,可是他卻會不定時「斷線」, 使用以下的 方法可以定時測試是否斷線,以及重新撥接。

Linux shell 的date表示法 linux下SHELL中的date表示法

NETCRAFT發現你的網站及作業系統 NETCRAFT可以發現你的網站及作業系統

Smarty安裝 smarty 是著名的樣版引擎,非常的好用,用多了突然發現拿掉smarty反而不會寫php了,以下是安裝過程..