建站
咨詢
售后
建站咨詢
新聞中心
簡單介紹
MySQL全文索引是在基于文本的列(char、varchar或text列)上創(chuàng)建的，以提升對這些列中包含的數(shù)據(jù)的查詢和DML操作效率。全文索引被定義為create table語句的一部分，或者使用alter table或create index追加到現(xiàn)有表中。搜索語法為：match(field1,field2,…) against(’keyword‘)，更多使用方法及詳細信息參考MySQL全文索引官方文檔
創(chuàng)新互聯(lián)公司是專業(yè)的花都網(wǎng)站建設(shè)公司，花都接單;提供成都做網(wǎng)站、網(wǎng)站設(shè)計、外貿(mào)營銷網(wǎng)站建設(shè),網(wǎng)頁設(shè)計,網(wǎng)站設(shè)計,建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務;采用PHP框架,可快速的進行花都網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團隊,希望更多企業(yè)前來合作!
版本說明
MySQL 5.6 以前的版本，只有 MyISAM 存儲引擎支持全文索引。
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存儲引擎均支持全文索引。
只有字段的數(shù)據(jù)類型為 char、varchar、text 及其系列才可以建全文索引。
創(chuàng)建全文索引
說明：搜索時，默認對中文類分詞支持不友好，如果有中日韓文分詞需求，在創(chuàng)建索引時，需要聲明該分詞插件ngram，語法可選【with parser ngram】。
DROP TABLE IF EXISTS `t_stu`;CREATE TABLE `t_stu` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`kecheng` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`fenshu` int(11) NULL DEFAULT NULL,
`keyword` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE,
FULLTEXT INDEX `ft_index`(`keyword`) WITH PARSER `ngram`
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ------------------------------ Records of t_stu-- ----------------------------
INSERT INTO `t_stu` VALUES (1, '張三', '語文', 81, '張三語文');
INSERT INTO `t_stu` VALUES (2, '張三', '數(shù)學', 75, '張三數(shù)學');
INSERT INTO `t_stu` VALUES (3, '李四', '語文', 76, '李四語文');
INSERT INTO `t_stu` VALUES (4, '李四', '數(shù)學', 90, '李四數(shù)學');
INSERT INTO `t_stu` VALUES (5, '王五', '語文', 81, '王五語文');
INSERT INTO `t_stu` VALUES (6, '王五', '數(shù)學', 100, '王五數(shù)學');
INSERT INTO `t_stu` VALUES (7, '王五', '英語', 90, '王五英語');
create index方式創(chuàng)建。
DROP TABLE IF EXISTS `t_stu`;
CREATE TABLE `t_stu` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`kecheng` varchar(255) DEFAULT NULL,
`fenshu` int(11) DEFAULT NULL,
`keyword` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
INSERT INTO `t_stu` VALUES (1, '張三', '語文', 81, '張三語文');
INSERT INTO `t_stu` VALUES (2, '張三', '數(shù)學', 75, '張三數(shù)學');
INSERT INTO `t_stu` VALUES (3, '李四', '語文', 76, '李四語文');
INSERT INTO `t_stu` VALUES (4, '李四', '數(shù)學', 90, '李四數(shù)學');
INSERT INTO `t_stu` VALUES (5, '王五', '語文', 81, '王五語文');
INSERT INTO `t_stu` VALUES (6, '王五', '數(shù)學', 100, '王五數(shù)學');
INSERT INTO `t_stu` VALUES (7, '王五', '英語', 90, '王五英語');
-- create index方式創(chuàng)建
CREATE FULLTEXT INDEX ft_index ON t_stu (keyword) WITH PARSER ngram;
- alter table方式創(chuàng)建。
alter table t_stu add fulltext index ft_index(keyword) with parser ngram;
- alter table方式創(chuàng)建。
alter table t_stu add fulltext index ft_index(keyword) with parser ngram;
刪除全文索引
- drop index方式刪除。
drop index ft_index on t_stu ;
- alter table方式刪除。
alter table t_stu drop index ft_index ;
完整使用語法
select * from t_stu where match(keyword) against('張三') ;提示：match() 函數(shù)中指定的列務必與索引中指定的列完全一致，否則將無法使用全文索引，全文索引不會記錄關(guān)鍵字來自于哪一列。
發(fā)現(xiàn)問題
select * from t_stu where match(keyword) against('張') ;找不到數(shù)據(jù)，或者數(shù)據(jù)不全。
從結(jié)果看出，只有輸入“張三”才匹配到了一條記錄，有了解過ElasticSearch、Lucene、Solr、MeiliSearch等搜索中間件的大佬，對這樣的結(jié)果屬實是不滿意的，預期的結(jié)果應該查出3條記錄才正常，導致這種情況其中非常關(guān)鍵的一個原因是MySQL對應執(zhí)行引擎下全文索引的**【最小搜索長度】**，MySQL 中的全文索引，有兩個關(guān)鍵的變量，分別是最小搜索長度和最大搜索長度，對于長度小于最小搜索長度和大于最大搜索長度的詞，都不會被索引。換言之，這個詞的長度必須在以上兩個變量的區(qū)間內(nèi)。
- 通過命令可以查看
show variables like '%ft%';
----------------------------
// InnoDB
innodb_ft_min_token_size = 3;
innodb_ft_max_token_size = 84;
// MyISAM
ft_min_word_len = 4;
ft_max_word_len = 84;
可以看到最小搜索長度InnoDB 引擎下默認是3，MyISAM引擎下默認是4，因此MySQL 的全文索引只會對長度大于等于3或者4的詞建立索引，而前面基于InnoDB，搜索的只有“jav”和“java”的長度大于等于 3，而基于MyISAM，搜索也只有“java”的長度大于等于4。
解決問題
既然最小搜索長度不滿足要求，就需要修改配置，在/etc/my.cnf下的mysqlId中追加內(nèi)容如下，當然也可以修改最大搜索長度，但沒必要。
innodb_ft_min_token_size = 1ft_min_word_len = 1
完事兒后重啟 MySQL 服務器，并修復全文索引，修復全文索語句如下，不過一般建議刪除索引，并重建索引。
repair table t_stu quick;
到這里為止，也依然沒有達到最終的效果，搜索關(guān)鍵字“張”時，并沒有出現(xiàn)結(jié)果集中，因為MySQL的全文索引有自然語言的全文索引和布爾全文索引兩種選擇：
1、自然語言全文索引
默認情況下，或者使用 in natural language mode修飾符時，match() 函數(shù)對文本集合執(zhí)行自然語言搜索，
上面的例子都是基于自然語言的全文索引。自然語言搜索引擎將計算每一個文檔對象和查詢的相關(guān)度。
這里，相關(guān)度是基于匹配的關(guān)鍵詞的個數(shù)，以及關(guān)鍵詞在文檔中出現(xiàn)的次數(shù)。
在整個索引中出現(xiàn)次數(shù)越少的詞語，匹配時的相關(guān)度就越高。
相反，非常常見的單詞將不會被搜索，如果一個詞語的在超過 50% 的
記錄中都出現(xiàn)了，那么自然語言的搜索將不會搜索這類詞語。
這個機制也比較好理解，比如，一個數(shù)據(jù)表存儲的是一篇篇的文章，
文章中的常見詞、語氣詞等等，出現(xiàn)的頻率非常多，搜索這些詞語是沒有意義的，
搜索具有文章特性的詞，這樣才能把文章區(qū)分開來。
2、布爾全文索引
在布爾搜索中，可以在查詢中自定義某個被搜索的詞語的相關(guān)性，
當編寫一個布爾搜索查詢時，可以通過一些前綴修飾符來定制搜索。
MySQL內(nèi)置的修飾符，上面查詢最小搜索長度時，
搜索結(jié)果ft_boolean_syntax變量的值就是內(nèi)置的修飾符，
相關(guān)修飾符具體的作用可以看看手冊。
上面的情況，可以通過布爾滿足。
select * from t_stu where match(keyword) against('*張*' IN BOOLEAN MODE);
select * from t_stu where match(keyword) against('張*' IN BOOLEAN MODE);數(shù)據(jù)是可以查出來的。完全實現(xiàn)like效果。
按自然語言搜索模式查詢
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('關(guān)鍵詞' IN NATURAL LANGUAGE MODE);
按布爾全文搜索模式查詢
2.1 匹配既有管理又有數(shù)據(jù)庫的記錄
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+數(shù)據(jù)庫 +管理' IN BOOLEAN MODE);
2.2匹配有數(shù)據(jù)庫，但是沒有管理的記錄
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+數(shù)據(jù)庫 -管理' IN BOOLEAN MODE);
2.3匹配MySQL，但是把數(shù)據(jù)庫的相關(guān)性降低
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('>數(shù)據(jù)庫 +MySQL' INBOOLEAN MODE);
select * from t_stu where match(keyword) against('*王五*愛學習*英語*' IN BOOLEAN MODE);匹配度越高的排在最前面。
總結(jié)
MySQL全文索引起初僅支持英文，因為英文的詞與詞之間有空格，使用空格作為分詞的分隔符是很方便的。亞洲文字，比如漢語、日語、漢語等，是沒有空格的，這就造成了一定的限制。不過 MySQL 5.7.6 開始，引入了一個 ngram 全文分析器來解決這個問題，并且對 MyISAM 和 InnoDB 引擎都有效。
事實上，MyISAM 存儲引擎對全文索引的支持有很多的限制，例如表級別鎖對性能的影響、數(shù)據(jù)文件的崩潰、崩潰后的恢復等，這使得 MyISAM 的全文索引對于很多的應用場景并不適合。所以，多數(shù)情況下的建議是使用別的解決方案，例如MeiliSearch、ElasticSearch等等第三方的插件，亦或是使用InnoDB存儲引擎的全文索引。
注意
- 使用全文索引前，明確版本支持情況。
- 全文索引比like + %快N倍，但是可能存在精度問題。
- 如果需要全文索引的是大量數(shù)據(jù)，建議先添加數(shù)據(jù)，再創(chuàng)建索引。
- 對于中文，可以使用 MySQL 5.7.6之后的版本，或者第三方插件。
網(wǎng)站題目：MySQL全文索引、支持中文分詞
標題鏈接：http://www.fisionsoft.com.cn/article/ccojeoc.html
