mysql 內建全文檢索索引

標籤：拼音   rac   insert   val   自然語言   fulltext   tle   compact   sel   

對於一些簡單的檢索可以通過mysql內建的全文索引及 MATCH AGAINST 查詢語句實現。實驗步驟如下。
1、建表
DROP table IF exists con_video_file_des_test;
CREATE TABLE `con_video_file_des_test` (
`content_id`  bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘視頻ID‘ ,
`video_title`  varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘視頻標題‘ ,
`video_title_phonetic`  text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT ‘視頻標題拼音‘ ,
`video_title_en`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘視頻標題英文‘ ,
PRIMARY KEY (`content_id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT=‘con_視頻類檔案描述資訊‘
AUTO_INCREMENT=7
ROW_FORMAT=COMPACT;
2、插入測試資料
INSERT INTO `test`.`con_video_file_des_test` (`content_id`, `video_title`, `video_title_phonetic`, `video_title_en`) VALUES (‘1‘, ‘建國大業‘, ‘jian guo da ye‘, ‘blockbuster Founding of the Republic‘);
INSERT INTO `test`.`con_video_file_des_test` (`content_id`, `video_title`, `video_title_phonetic`, `video_title_en`) VALUES (‘2‘, ‘堅果遊記‘, ‘jian guo you ji‘, ‘jian guo travel‘);
3、索引如下
alter table con_video_file_des_test add fulltext search_index(video_title,video_title_phonetic,video_title_en);
4、查詢測試
a: select * from con_video_file_des_test where match(video_title,video_title_phonetic,video_title_en) against(‘建國‘);
b: select * from con_video_file_des_test where match(video_title,video_title_phonetic,video_title_en) against(‘建國大業‘);
c: select * from con_video_file_des_test where match(video_title,video_title_phonetic,video_title_en) against(‘jian guo‘);
d: select * from con_video_file_des_test where match(video_title,video_title_phonetic,video_title_en) against(‘jian guo da ye‘);
a查詢無資料；b查詢返回第一條記錄，c,d查詢都返回兩條記錄，並第一條記錄為堅果遊記。
解釋如下因為mysql是以空格分割進行分詞建立索引的，所以a查詢沒有查到資料,b查詢能查到資料；c，d查詢均查詢到語句第一條記錄為堅果遊記是因為MATCH...AGAINST是以相關性排序，由高到低；
其他注意事項：
1、預設搜尋是不分大小寫，若要分大小寫，columne 的 character set要從utf8改成utf8_bin。
2、預設 MATCH...AGAINST 是以相關性排序，由高到低。
3、MATCH(title, content)裡的欄位必須和FULLTEXT(title, content)裡的欄位一模一樣。否則提示 Can‘t find FULLTEXT index matching the column list。
4、ft_min_word_len = 全文檢索索引的最小許可字元(預設4，通過 SHOW VARIABLES LIKE ‘ft_min_word_len‘ 可查看)，中文通常是兩個字就是一個詞。
5、檢索方式有自然語言檢索(IN NATURAL LANGUAGE MODE)和布爾檢索(IN BOOLEAN MODE)兩種，兩種檢索就不深入研究了。
6、由於不能對中文進行單個字詞的檢索，一解決方案是添加拼音列，儲存中文轉成的拼音，將輸入的中文轉為拼音進行檢索並返回結果，但這樣會出現輸入“建國”找到了兩條記錄“建國大業”、“堅果遊記”。
mysql的全文檢索索引可以應對一些簡單的應用需求，對於一些要求更高的應用可以通過其他全文檢索索引方式實現，比如Lucene、Nutch、solr等。

mysql 內建全文檢索索引