mysql使用全文索引實現大欄位的模糊查詢
0.場景說明
centos7 mysql5.7 InnoDB引擎
0.1創建表
DROP TABLE IF EXISTS tbl_article_content;
CREATE TABLE tbl_article_content (
id bigint(40) NOT NULL AUTO_INCREMENT,
content text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
article_id bigint(40) NOT NULL COMMENT ‘對應文章ID’,
create_by datetime(0) NOT NULL COMMENT ‘創建時間’,
modifield_by datetime(0) NOT NULL COMMENT ‘更新時間’,
PRIMARY KEY (id) USING BTREE,
INDEX artid(article_id) USING BTREE,
FULLTEXT INDEX content_word(content) WITH PARSER ngram
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
0.2為什麼要使用索引
因為有一個全局搜索的的需求，用戶輸入關鍵字對部落格系統內所有含有該欄位的文章(標題&簡介&內容)進行展示,(上表只是文章內容表)，由於要對文章內容進行搜素，而文章內容content在資料庫中是以text存儲的，所以為了查詢效率，這裡就需要使用索引，由於是查大文本，這裡選擇使用全文索引(fulltext).
1.創建索引（使用ngram解析器）
create fulltext index content_word on tbl_article_content(content) WITH PARSER ngram;
ngram是一個支援中文索引的分詞引擎，
在這裡我們對tbl_article_content表的content欄位建一個叫content_word的全文索引.
2.mysql配置（my.cnf）
mysql默認是沒有開啟與配置ngram的，所以在建立索引後對mysql進行配置
打開mysql配置文件 vim /etc/my.cnf
在[mysqld]下加入
ft_min_word_len=1 //全文索引的最小搜索長度。默認是4
ngram_token_size=1 //分詞的大小設置，這裡設置越小，索引越大
重啟mysql
systemctl restart mysqld
進入mysql，修復一下之前創的索引
repair table tbl_article_content
3.查詢語句
select article_id FROM tbl_article_content WHERE MATCH(content) AGAINST(‘沒有了’);
4.總結問題
從MySQL 5.7開始，MySQL內置了ngram全文檢索插件，用來支援中文分詞，並且對MyISAM和InnoDB引擎有效，使用的時候注意自己版本號和配置
5.相關命令
show create table tbl_article_content; //查詢當前表資訊
SHOW VARIABLES LIKE '%ngram%'; //查詢ngram引擎配置資訊
6.歡迎follow
github: //github.com/DianeDii
wx： dianedii
知乎： 愛睡覺的狄大人 （帶你睡大覺）
