[精讚] [會員登入]
1188

[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

 

你可能感興趣的文章

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

[Mysql/MariaDB] 修改資料庫、表格或欄位名稱 Mysql/MariaDB修改資料庫、表格或欄位名稱

兩次使用InnoDB的慘痛經驗 Mysql 的Innodb引擎雖然好用,但是我得說說我兩次的慘痛經驗,這讓我考慮以後可能不會再使用innodb了

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

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

[Mysql] 修改資料庫預設校對或編碼 修改資料庫預設校對或編碼的方法

[Mysql/MariaDB] 加解密函數 Mysql/MariaDB上的加解密函數

[Mysql] Trigger 觸發使用方法 trigger 在資料庫的使用上,具有極大的方便性,該如何使用?

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

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

我有話要說


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

訪客留言

[無留言]

隨機好文

[jQuery] select 元件的取值及給值 html中的元件select,在jquery中要如何使用?

PHP for sphinx 函式庫安裝 PECL/sphinx PHP>= 5.2.2 已經能原生支援 sphinx,可是預設的沒有裝,我們得自己裝才能用

TFTP Server 安裝及使用 讓設備的網路設定檔或是韌體經由TFTP拷備出來,操作的方法

維修海棉拖把頭 這類型的海棉拖把很好用,可是這近發現海棉頭越來越不耐用,也許是錯覺,以往都能用個三四個月,現在二個月就差不多掉下來。 這

此一時彼一時 我是不是易怒的人,其實我也不知道,總之我常會失控,不知道自己在幹嘛。這近生活過得浮浮的,不是很踏實,總會想太多,我甚至會